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
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.