Summarize Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Summarize Query

Author

Topic

hisouka
Starting Member

28 Posts

Posted - 2008-12-15 : 05:49:43

I have a table named Overdue.

Sample data:

Overdue Table
Hours
23
29
60
220
89
40

How can i make a table or report that will count the numbers of hours who is overdue by 24 hours (between 0 and 24), 48 (between 25 and 48), 72, 96, 120, 240.

Output based on table above:

Level------Count
24-----------1
48-----------2
72-----------1
96-----------1
120----------0
240----------1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-15 : 05:56:38

[code]SELECT [Hours] / 24 * 24 + 24 AS [Level],
COUNT(*) AS [Count]
FROM Overdue
GROUP BY [Hours] / 24 * 24 + 24[/code]

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

hisouka
Starting Member

28 Posts

Posted - 2008-12-15 : 06:04:23

quote:
Originally posted by Peso

SELECT [Hours] / 24 * 24 + 24 AS [Level],
COUNT(*) AS [Count]
FROM Overdue
GROUP BY [Hours] / 24 * 24 + 24

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

Hi.. it is fixed as 24, 48, 72, 98, 120 and 240

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-15 : 06:11:39

Try.

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-15 : 06:22:04

quote:
Originally posted by Peso

SELECT [Hours] / 24 * 24 + 24 AS [Level],
COUNT(*) AS [Count]
FROM Overdue
GROUP BY [Hours] / 24 * 24 + 24

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

hai peso once plz check it ur output is not getting the 120 value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 07:25:41

[code]
SELECT [Hours] / 24 * 24 + 24 AS [Level],
COUNT(*) AS [Count] INTO #temp
FROM Overdue
GROUP BY [Hours] / 24 * 24 + 24
[/code]

;With CTE(Level) AS
(SELECT MIN(Level)
FROM #Temp
UNION ALL
SELECT Level + 24
FROM CTE
WHERE Level+24 < (SELECT MAX(Level)
FROM #Temp)
)

SELECT c.Level,
COALESCE(t.Count,0) AS [Count]
FROM CTE c
LEFT JOIN #Temp t
ON t.Level=c.Level

DROP TABLE #Temp
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-15 : 08:30:31

[code]SELECT d.[level] AS [Level],
COUNT(o.[Hours]) AS [Count]
FROM (
SELECT 0 AS lowLevel, 24 AS highLevel, 24 AS [level] UNION ALL
SELECT 25 AS lowLevel, 48 AS highLevel, 48 AS [level] UNION ALL
SELECT 49 AS lowLevel, 72 AS highLevel, 72 AS [level] UNION ALL
SELECT 73 AS lowLevel, 96 AS highLevel, 96 AS [level] UNION ALL
SELECT 97 AS lowLevel, 120 AS highLevel, 120 AS [level] UNION ALL
SELECT 121 AS lowLevel, 240 AS highLevel, 240 AS [level]
) AS d
LEFT JOIN Overdue AS o ON o.[Hours] BETWEEN d.lowLevel AND d.highLevel
GROUP BY d.[level]
ORDER BY d.[level][/code]

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

hisouka
Starting Member

28 Posts

Posted - 2008-12-15 : 21:37:05

quote:

Originally posted by Peso

SELECT		d.[level] AS [Level],
		COUNT(o.[Hours]) AS [Count]
FROM		(
			SELECT	  0 AS lowLevel,  24 AS highLevel,  24 AS [level] UNION ALL
			SELECT	 25 AS lowLevel,  48 AS highLevel,  48 AS [level] UNION ALL
			SELECT	 49 AS lowLevel,  72 AS highLevel,  72 AS [level] UNION ALL
			SELECT	 73 AS lowLevel,  96 AS highLevel,  96 AS [level] UNION ALL
			SELECT	 97 AS lowLevel, 120 AS highLevel, 120 AS [level] UNION ALL
			SELECT	121 AS lowLevel, 240 AS highLevel, 240 AS [level]
		) AS d
LEFT JOIN	Overdue AS o ON o.[Hours] BETWEEN d.lowLevel AND d.highLevel
GROUP BY	d.[level]
ORDER BY	d.[level]

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

Thanks Peso.. it works. but i have follow up Question..

Overdue Table
Hours-------Time
23----------1:32
29----------5:15
60----------10:00
220---------11:00
89----------2:00
40----------5:39

how can i output additional fields like:
Level------Count------Average Time-----Min Time-----Max Time
24-----------1
48-----------2
72-----------1
96-----------1
120----------0
240----------1

Average time is the average time of all count within a Level
Min Time is the minimun time within a Level
Max Time is the maximum time within a Level

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 23:41:09

[code]
SELECT [Hours] / 24 * 24 + 24 AS [Level],
COUNT(*) AS [Count],
AVG(Hours*1.0) AS [Average],
MIN(Hours) AS [MinTime],
MAX(Hours) AS [MaxTime]
INTO #temp
FROM Overdue
GROUP BY [Hours] / 24 * 24 + 24

;With CTE(Level) AS
(SELECT MIN(Level)
FROM #Temp
UNION ALL
SELECT Level + 24
FROM CTE
WHERE Level+24 < (SELECT MAX(Level)
FROM #Temp)
)

SELECT c.Level,
COALESCE(t.Count,0) AS [Count],
COALESCE(t.Average,0) AS [Average],
COALESCE(t.MinTime,0) AS [MinTime],
COALESCE(t.MaxTime,0) AS [MaxTime]
FROM CTE c
LEFT JOIN #Temp t
ON t.Level=c.Level

DROP TABLE #Temp
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 03:25:54

[code]SELECT d.[level] AS [Level],
COUNT(o.[Hours]) AS [Count],
AVG(1.0E * [Hours] AS [Avg],
MIN([Hours] AS [Min],
MAX([Hours] AS [Max]
FROM (
SELECT 0 AS lowLevel, 24 AS highLevel, 24 AS [level] UNION ALL
SELECT 25 AS lowLevel, 48 AS highLevel, 48 AS [level] UNION ALL
SELECT 49 AS lowLevel, 72 AS highLevel, 72 AS [level] UNION ALL
SELECT 73 AS lowLevel, 96 AS highLevel, 96 AS [level] UNION ALL
SELECT 97 AS lowLevel, 120 AS highLevel, 120 AS [level] UNION ALL
SELECT 121 AS lowLevel, 240 AS highLevel, 240 AS [level]
) AS d
LEFT JOIN Overdue AS o ON o.[Hours] BETWEEN d.lowLevel AND d.highLevel
GROUP BY d.[level]
ORDER BY d.[level][/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources