Group By with ORDER of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By with ORDER of records

Author

Topic

anumodhc
Starting Member

13 Posts

Posted - 2012-02-21 : 23:55:32

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.

Please help

AnumodH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 00:12:51

so what should be the output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anumodhc
Starting Member

13 Posts

Posted - 2012-02-22 : 00:16:51

I need the out put like

A 18:30
B 11:00
A 20:00

AnumodH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 00:27:10

[code]
SELECT t.Name,CAST(SUM(DATEDIFF(ss,0,CAST(t.Date AS time))) AS time)
FROM Table t
CROSS APPLY (SELECT TOP 1 Date
FROM table
WHERE Date < t.Date
AND Name <> t.Name
ORDER BY Date DESC) t1
GROUP BY t.Name,t1.Date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anumodhc
Starting Member

13 Posts

Posted - 2012-02-22 : 02:27:35

Hi Visakh

Thanks for the help
But it did not helped me to solve this problem.

Thanks for the help

AnumodH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 09:43:22

quote:
Originally posted by anumodhc

Hi Visakh

Thanks for the help
But it did not helped me to solve this problem.

Thanks for the help

AnumodH

why whats the issue
can you show what your expected format is and what you got by my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-22 : 13:50:26

[code]DECLARE @Foo TABLE (Name CHAR(1), [Date] VARCHAR(5), [Hours] TIME)
INSERT @Foo (Name, [Date], [Hours])
VALUES
('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')

;WITH Cte AS
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY Date, Name)
- ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS GroupNum
FROM @Foo AS A
)
SELECT Name, CAST(DATEADD(MINUTE, SUM(DATEDIFF(MINUTE, '0:00', [Hours])), '0:00') AS TIME)
FROM Cte
GROUP BY GroupNum, Name
[/code]If you go over 24 hours, then I'm not if the results are going to be what you expect.

anumodhc
Starting Member

13 Posts

Posted - 2012-02-22 : 23:02:49

Hey thanks all
i got the solution.

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

thanks a lot

AnumodH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 08:58:28

quote:
Originally posted by anumodhc

Hey thanks all
i got the solution.

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

thanks a lot

AnumodH

this will not work correct always

Add this sample data and check

A 4-Feb 5:40
A 5-Feb 3:50

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 09:36:19

[code]Scenario 1 Scenario 2
--------------------- ---------------------
'A', '1-Feb', '10:00' 'A', '1-Feb', '10:00'
'A', '2-Feb', '8:30' 'B', '2-Feb', '7:00' * These two rows are dated the same day
'B', '2-Feb', '7:00' 'A', '2-Feb', '8:30' * Since nothing tells them apart, there is no solution
'B', '3-Feb', '11:00' 'B', '3-Feb', '11:00'
--------------------- ---------------------
'A', '18:30' 'A', '10:00'
'B', '18:00' 'B', '7:00'
'A', '8:30'
'B', '11:00'[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources