question

anumodhc avatar image
anumodhc asked

Group By with ORDER of records

Hi All, I have a result set as below Name Date Hours A 1-Feb 10:00 A 2-Feb 8:30 B 3-Feb 11:00 A 4-Feb 10:00 A 5-Feb 10:00 i want to group the records (to get total Hours) according to name in the order result is populated. I need total hours for First 2 records, then 3 record and for 4th and 5th records. I need the out put like A 18:30 B 11:00 A 20:00 Please help AnumodH
tsqlgroup-by
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
You need to identify the boundaries of the groups in your data - this is the islands and gaps problem/solution: I had to change the data slightly (the date information needed a year) and the column names are far from ideal. DECLARE @TestTable AS TABLE (Name char(1), [Date] datetime, [Hours] varchar(5)) INSERT INTO @TestTable (Name,[Date],[Hours]) SELECT 'A' ,'1-Feb-2012', '10:00' UNION ALL SELECT 'A', '2-Feb-2012', '8:30' UNION ALL SELECT 'B', '3-Feb-2012', '11:00' UNION ALL SELECT 'A', '4-Feb-2012', '10:00' UNION ALL SELECT 'A', '5-Feb-2012', '10:00' SELECT Name, SUM(CAST(REPLACE(hours, ':', '.') AS decimal(4, 2))) FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Name) - ROW_NUMBER() OVER (ORDER BY [date]) grp FROM @TestTable) a GROUP BY Name, Grp The major work done here is to identify the data that belongs together. I understand your definition to mean "group all occurrences of adjacent [Name] rows together". This is done by assigning a `row_number` to all rows sorted by date and all rows sorted by name, subtracting the date sorted from the name sorted to achieve a grouping key. You then add all [hours] together that are in the group Name+GroupingKey to arrive at what you want. As you supplied character data I had to do some casting to allow aggregation, but it does the trick. The conversion can be spared through appropriate data-type usage or by using an in-line table valued function.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - agree using reserved words for column names will lead to trouble in the future. Use Person_Hours or Test_Hours or Billable_Hours rather than just Hours, etc
1 Like 1 ·
@WilliamD +1 for the nice solution and critical suggestions. (Cannot give more :)) @anumodhc You may need to look into the decimal conversion as it would be good for the sample data only. I guess something like SELECT Name, REPLACE( CAST( SUM(CAST(SUBSTRING(HOURS,1,CHARINDEX(':',hours)-1) AS decimal(4, 2))) + SUM(CAST(RIGHT(HOURS,LEN(HOURS)-CHARINDEX(':',hours)) AS INT))/60 + (SUM(CAST(RIGHT(HOURS,LEN(HOURS)-CHARINDEX(':',hours)) AS INT))%60) * 0.01 AS VARCHAR(50)) ,'.',':') FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Name) - ROW_NUMBER() OVER (ORDER BY [date]) grp FROM @Table) a GROUP BY Name, Grp may be used for the desired format OR do the conversion to datetime and then calculate. Another Important thing to keep in mind that it is assumed, that the name would be unique for a day/datetime as it is in the sample data. OR if not unique then the gouping would be according to the dates not according to the order of the rows.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.