Aggregate Adjacent Only Records with T-Sql

GROUP BY adjacent records

If I understand you correctly: What you want is a result that is dependent on the value in the next record. If the value of value is different, it starts a new subgroup. We can find the first row from the next group for every row. For that we can use an auto join, based on unequality. Of course the last row(s) of your data will go missing as they have no next row with a different value. (Maybe you could fix this by adding a fake row with future date and nonexisting value by using UNION.)

Then from this list of data where we know the starting date of the next group for each member, we can use that Nextdate for grouping so we can find the first and last date within that group as well:

SELECT Min(Somedate) AS timest_first, Max(Somedate) AS timest_last, value FROM
(SELECT  t2.Value, t2.timest AS Somedate, Min(t1.timest) AS Nextdate, t1.value as n
 FROM entity_log t1 JOIN entity_log t2
 ON t1.timest > t2.timest
 WHERE t1.value <> t2.value
 GROUP BY t2.timest) s1
GROUP BY value, Nextdate
ORDER BY 2 desc 

How to get aggregate data from a dynamic number of related rows in adjacent table

I think this does what you want.

The key idea is to assign a "streak group" to each winning streak, so you can aggregate them. You can do this by observing:

1. A match in a winning streak is obviously a "win".
2. A winning streak can be identified by counting the number of losses before it -- this is constant for the streak.

Postgres introduced the filter clause in 9.4, which makes the syntax a little easier:

select player_id, count(*) as streak_length,
       avg(opponent_rank) as avg_opponent_rank
from (select m.*,
             count(*) filter (where result = 'l') over (partition by player_id order by date) as streak_grp
      from matches_m m
     ) m
where result = 'w'
group by player_id, streak_grp;

Merge adjacent rows in SQL?

This article provides quite a few possible solutions to your question

http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851

This one seems like the most straight forward:

WITH StartTimes AS
(
  SELECT DISTINCT username, starttime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.starttime < S1.starttime
       AND S2.endtime >= S1.starttime)
),
EndTimes AS
(
  SELECT DISTINCT username, endtime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.endtime > S1.endtime
       AND S2.starttime <= S1.endtime)
)
SELECT username, starttime,
  (SELECT MIN(endtime) FROM EndTimes AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM StartTimes AS S;

Group consecutive rows of same value using time spans

The query determines each rows EndTime by using NOT EXISTS to make sure no other class or course of a different type is scheduled between a course range's StartTime and EndTime and then uses MIN and GROUP BY to find the StartTime.

The NOT EXISTS part ensures that there aren't "breaks" between the StartTime and EndTime ranges by searching for any rows that have an EndTime between StartTime and EndTime but belong to a different CourseName or CourseRoom.

SELECT    
    t0.ClassRoom,
    t0.CourseName,
    MIN(t0.StartTime),
    t0.EndTime
FROM (
    SELECT 
    t1.ClassRoom,
    t1.CourseName,
    t1.StartTime,
    (
        SELECT MAX(t2.EndTime)
        FROM tableA t2
        WHERE t2.CourseName = t1.CourseName
        AND t2.ClassRoom = t1.ClassRoom
        AND NOT EXISTS (SELECT 1 FROM tableA t3
            WHERE t3.EndTime < t2.EndTime 
            AND t3.EndTime > t1.EndTime
            AND (t3.CourseName <> t2.CourseName 
            OR t3.ClassRoom <> t2.ClassRoom)
        )
    ) EndTime
    FROM tableA t1
) t0 GROUP BY t0.ClassRoom, t0.CourseName, t0.EndTime

http://www.sqlfiddle.com/#!6/39d4b/9

Group rows if certain column value directly after another in SQL

If ids are one after one try do it this way:

select * into #tab from(
    select 0 as id, 'bob' as name, 'note' as event, '14:20' as time union
    select 1, 'bob', 'time', '14:22' union
    select 2, 'bob', 'time', '14:40' union
    select 3, 'bob', 'time', '14:45' union
    select 4, 'bob', 'send', '14:48' union
    select 5, 'bob', 'time', '15:30' union
    select 6, 'bob', 'note', '15:35' union
    select 7, 'bob', 'note', '18:00'
) t

select t.*
from #tab t
left join #tab t1 on t.id = t1.id + 1 and t1.event = t.event 
    -- and t1.name = t.name -- if there are more names you are going to need this one as well
where t1.id is null

result:

id  name    event   time
0   bob     note    14:20
1   bob     time    14:22
4   bob     send    14:48
5   bob     time    15:30
6   bob     note    15:35

Added:

If ids aren't one after one, you can make them to be:

select identity(int, 1, 1) as id, name, event, time 
into #tab_ordered_ids
from #tab order by name, id, time

Creating Columns in a View That Use an Aggregate Method in a Case...When Expression

You need to put your case inside your sum:

SUM(CASE WHEN kth.[Portfolio Report Category] = '705' THEN TRY_CONVERT(money, kth.[Market Value]) ELSE '0.00' END) AS [Crypto Total Value], 

Here is a working example:

declare @Adr table ([Account Number] varchar(6), [Account_ Display Name] varchar(64), [Account Category Code] varchar, [Division Code] varchar, [Division Name] varchar, [Market Value Amount] money);
declare @Kth table ([Account Number] varchar(6), [Portfolio Report Category] varchar(3), [Market Value] money);
declare @Ktc table ([Account Number] varchar(6), [Available Cash Amount] money);

insert into @Adr ([Account Number], [Account_ Display Name])
values ('001000', 'Carl S Sykes Roth IRA');

insert into @Kth ([Account Number], [Market Value], [Portfolio Report Category])
values ('001000', 9998.4792, '600'),
('001000', 43467.09, '600'),
('001000', 84524.71, '600');

insert into @Ktc ([Account Number])
values ('001000');

SELECT adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount]
  , SUM(CASE WHEN kth.[Portfolio Report Category] = '705' THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Crypto Total Value] 
  , SUM(CASE WHEN kth.[Portfolio Report Category] = '691' THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Precious Metal Total Value]
  , SUM(CASE WHEN kth.[Portfolio Report Category] IN ('010', '011', '020', '025', '030') THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Stock Total Value]
  , SUM(CASE WHEN kth.[Portfolio Report Category] NOT IN ('010', '011', '020', '025', '030', '691', '705') THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Other Total Value]
  , ktc.[Available Cash Amount] AS [CASH TOTAL]
FROM @Adr adr
INNER JOIN @Kth kth ON adr.[Account Number] = kth.[Account Number]
INNER JOIN @Ktc ktc  ON adr.[Account Number] = ktc.[Account Number]
GROUP BY adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount], ktc.[Available Cash Amount];

Which returns (un-necessary columns removed):

Account Number  Crypto Total Value  Precious Metal Total Value  Stock Total Value   Other Total Value   CASH TOTAL
001000          0.00                0.00                        0.00                137990.2792         NULL

Edit: As you have now added sample data, the reason you were getting incorrect values is because you had duplicate rows in your KTAccountsADR which then multiple the rows in the KTHoldings table. Resolve the duplicates and you will get the correct values when using case inside sum.

Note, its best practice to ensure you are returning the same datatype from all branches of a case expression.

---

Related Topics