group by 1/2 hour
-
Good day
I have a request to query against some fax DB (rightfax) for the following:
A query to indicate how many faxes are sent per half hour, per fax line, from 17:00 - 17:00 of current day.
I am still trying to locate the tables I need, but the "group by 1/2 hour" has my wheels spinning - I thought I would see if anyone has run across this and could point me in the right direction quicker.
Cory
-- Cory
-
You can play with the format of the minutes to be anything you like.
Try something like this:
declare @sample table (id int, fax_date datetime)
insert @sample values(1, '2006-02-25 00:28:00.000')
insert @sample values(2, '2006-02-25 00:31:00.000')
insert @sample values(3, '2006-02-25 00:33:00.000')
insert @sample values(4, '2006-02-25 02:28:00.000')
insert @sample values(5, '2006-02-25 02:29:00.000')
insert @sample values(6, '2006-02-25 02:59:00.000')
select sample_date, count(*)
from (select cast(DATEADD(DD, 0, DATEDIFF(DD, 0, fax_date)) as varchar(11)) + ' ' + right('0' + cast(DATEPART(hh,fax_date) as varchar), 2) + case when DATEPART(mi,fax_date) < 30 then ':00' else ':30' end sample_date
from @sample) s
group by s.sample_date
-
-
Using the previous two posts as inspiration, here's another way:
declare @sample table (id int, fax_date datetime)
SET NOCOUNT ON
insert @sample values(1, '2006-02-25 00:28:00.000')
insert @sample values(2, '2006-02-25 00:31:00.000')
insert @sample values(3, '2006-02-25 00:33:00.000')
insert @sample values(4, '2006-02-25 02:28:00.000')
insert @sample values(5, '2006-02-25 02:29:00.000')
insert @sample values(6, '2006-02-25 02:59:00.000')
SET NOCOUNT OFF
SELECT DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , fax_Date) AS AdjustedDate
, Count(*) As CountPerHalfHour
FROM @sample
GROUP BY DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , fax_Date)
-
Okay - this may seem like a no brainer question...but it has me stumped at the moment...
I have seconds in my time, and this causes the sql to not quite work right...what might I have to do?
declare
@sample table (id int, fax_date datetime)
SET
NOCOUNT ON
insert @sample values(1, '2006-06-19 09:04:20.000')
insert
@sample values(2, '2006-06-19 09:04:06.000')
insert
@sample values(3, '2006-06-19 09:04:08.000')
insert
@sample values(4, '2006-06-19 09:04:14.000')
insert
@sample values(5, '2006-06-19 09:03:54.000')
insert
@sample values(6, '2006-06-20 14:02:43.000')
SET
NOCOUNT OFF
SELECT DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , fax_Date) AS AdjustedDate
, Count(*) As CountPerHalfHour
FROM @sample
GROUP BY DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , fax_Date)
-- Cory
-
Try casting fax_date as a smalldatetime to remove the seconds:
SELECT DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , cast(fax_Date as smalldatetime)) AS AdjustedDate
, Count(*) As CountPerHalfHour
FROM @sample
GROUP BY DateAdd(mi, 0 - DatePart(mi, fax_Date) + (DatePart(mi, fax_Date) / 30) * 30 , cast(fax_Date as smalldatetime))
-
- select dateadd(mi,datediff(mi,0,fax_date)/30*30,0) half_hour_start, count(*) cnt
- from @sample
- group by dateadd(mi,datediff(mi,0,fax_date)/30*30,0)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Why you need "*30" in "Group by" ?
_____________
Code for TallyGenerator -
Just a couple of thoughts, folks...
I'm thinkin' that no-one considered the fact that it is possible to have a day with no faxes. I'm also not seeing anything that considers that the reporting period requested was for 17:00 the previous day 'til 17:00 of the current day.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Cory,
Using the data that JeffB originally posted, here's a couple of solutions that observe your 17:00 start and end times for reporting purposes... the first solution is similar to Tim's posting... the second solution is 'cause it was fun and it ticks people off who think it needs to be done in an application that may not exist
SET NOCOUNT ON DECLARE @Sample TABLE (id int, fax_date datetime)
insert @Sample values(1, '2006-06-21 00:28:00.000') insert @Sample values(2, '2006-06-21 00:31:00.000') insert @Sample values(3, '2006-06-21 00:33:00.000') insert @Sample values(4, '2006-06-21 02:28:00.000') insert @Sample values(5, '2006-06-21 02:29:00.000') insert @Sample values(6, '2006-06-21 02:59:00.000')
PRINT '===== The "simple" form =====' SELECT DATEADD(mi,DATEDIFF(mi,0,Fax_Date)/30*30,0) AS TimeBin, COUNT(*) FaxCount FROM @Sample WHERE Fax_Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)+'17:00' AND Fax_Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)+'17:00' GROUP BY DATEADD(mi,DATEDIFF(mi,0,Fax_Date)/30*30,0) WITH ROLLUP
PRINT '===== The "pretty" form =====' SELECT CASE WHEN GROUPING(TimeBin) = 0 THEN CONVERT(CHAR(10),d.TimeBin,101) ELSE CHAR(13)+'Total' END AS [Date], CASE WHEN GROUPING(TimeBin) = 0 THEN CONVERT(CHAR(5),d.TimeBin,108) + ' - ' + CONVERT(CHAR(5),DATEADD(mi,29,d.TimeBin),108) ELSE '' END AS [Time Slot], CASE WHEN GROUPING(TimeBin) = 0 THEN STR(SUM(FaxCount),8) ELSE STR(SUM(FaxCount),9) END AS FaxCount FROM ( SELECT DATEADD(mi,DATEDIFF(mi,0,Fax_Date)/30*30,0) AS TimeBin, 1 AS FaxCount FROM @Sample WHERE Fax_Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)+'17:00' AND Fax_Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)+'17:00' ) d GROUP BY d.TimeBin WITH ROLLUP
... and here's the output(s)...
===== The "simple" form ===== TimeBin FaxCount ------------------------------------------------------ ----------- 2006-06-21 00:00:00.000 1 2006-06-21 00:30:00.000 2 2006-06-21 02:00:00.000 2 2006-06-21 02:30:00.000 1 NULL 6
===== The "pretty" form ===== Date Time Slot FaxCount ---------- ------------- --------- 06/21/2006 00:00 - 00:29 1 06/21/2006 00:30 - 00:59 2 06/21/2006 02:00 - 02:29 2 06/21/2006 02:30 - 02:59 1
Total 6
... if you need it to show missing time slots with a "0", write back and we'll give it a whirl with a Tally table...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>Why you need "*30" in "Group by" ?
You don't; you need it in the SELECT, and in the absence of countervailing reasons, I always try and ask SQL to evaluate as few expressions as possible. Would it be better to have SQL calculate dateadd(mi,datediff(mi,0,fax_date)/30,0) for the group by, and dateadd(mi,datediff(mi,0,fax_date)/30*30,0) for the select?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Jeff,
I thought I could do it, but yes, I need your help - I need the the 0 counts to show up as well.
TIA!
Cory
-- Cory
-
- declare
@querylboundinc datetime, @queryuboundex datetime, @halfhourstart datetime
- declare @halfhour table(halfhourstart datetime)
- select
@querylboundinc = dateadd(hour, -7, dateadd(day,datediff(day,0,getdate()),0))
- select
@queryuboundex = @querylboundinc + 1, @halfhourstart = @querylboundinc
- while
@halfhourstart < @queryuboundex
- begin
- insert @halfhour(halfhourstart) values(@halfhourstart)
- select @halfhourstart = dateadd(minute,30,@halfhourstart)
- end
- select
h.halfhourstart, coalesce(v.cnt,0) cnt
- from
@halfhour h
- left
join (select dateadd(mi,datediff(mi,0,fax_date)/30*30,0) halfhourstart, count(*) cnt
- from '<table goes here>'
- where fax_date >= @querylboundinc
- and fax_date < @queryuboundex
- group by dateadd(mi,datediff(mi,0,fax_date)/30*30,0)
- ) v on v.halfhourstart = h.halfhourstart
[Edit: print size increased in response to following post]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
If you can read it (dang, that's small print
), Tim's WHILE loop works just fine on the straight output... so I'll concentrate on the "pretty" ouput... Also, I'll use a Tally table just to be different. I will say that, in many cases, a properly constructed WHILE loop will be faster than a Tally table solution simply because WHILE loops occur in memory while the Tally table solution relies on a JOIN. In this case, I believe the Tally table would be faster but it's not real important here unless you have a couple of hundred thousand fax entries in your table.That being said, what's a Tally table? It's nothing more that what some folks call a "Numbers" table and it's a permanent table that contains a single column of sequential numbers with a clustered index and a FILL FACTOR of 100 because it'll likely never change once you build it. It has many uses but, like I said, WHILE loops have the performance advantage in many cases (usually in scalar functions). The Tally table method does have the advantage of making code much simpler to build/read IF you know what a Tally table is.
But, I digress... here's one way to build a Tally table...
--===== Create and populate the Tally table on the fly
SELECT TOP 9999
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Yep... the cross join above is intentional. That's another story...
Anyway, here's some updated data and the previous "pretty" code with an outer join to the Tally table converted to time bins...
SET NOCOUNT ON
--===== Make a sample table with some sample data
DECLARE @Sample TABLE (id int, fax_date datetime)
INSERT @Sample VALUES(1, '2006-06-28 00:28:00.000')
INSERT @Sample VALUES(2, '2006-06-28 00:31:00.000')
INSERT @Sample VALUES(3, '2006-06-28 00:33:00.000')
INSERT @Sample VALUES(4, '2006-06-28 02:28:00.000')
INSERT @Sample VALUES(5, '2006-06-28 02:29:00.000')
INSERT @Sample VALUES(6, '2006-06-28 02:59:00.000')
PRINT '===== The "pretty" form ====='
SET ANSI_WARNINGS OFF --prevents the warning about nulls being eliminated by aggragate
SELECT
CASE
WHEN GROUPING(t.TimeBin) = 0
THEN CONVERT(CHAR(10),t.TimeBin,101)
ELSE CHAR(13)+'Total'
END AS [Date],
CASE
WHEN GROUPING(t.TimeBin) = 0
THEN CONVERT(CHAR(5),t.TimeBin,108)
+ ' - '
+ CONVERT(CHAR(5),DATEADD(mi,29,t.TimeBin),108)
ELSE ''
END AS [Time Slot],
CASE
WHEN GROUPING(t.TimeBin) = 0
THEN STR(ISNULL(SUM(FaxCount),0),8)
ELSE STR(ISNULL(SUM(FaxCount),0),9)
END AS FaxCount
FROM
(--Derived table converts desired fax times into time bins
SELECT DATEADD(mi,DATEDIFF(mi,0,Fax_Date)/30*30,0) AS TimeBin,
1 AS FaxCount
FROM @Sample
WHERE Fax_Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)+'17:00'
AND Fax_Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)+'17:00'
) d
RIGHT OUTER JOIN
(--Derived table returns all time bins from 17:00 to 17:00
SELECT DATEADD(mi,(N-1)*30,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))+'17:00' AS TimeBin
FROM dbo.TALLY
WHERE N<=48
) t
ON d.TimeBin = t.TimeBin
GROUP BY t.TimeBin
WITH ROLLUP
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The method below creates and loads a table data type to hold the desired datetime ranges. The defaults are as you specified -- from 17:00 the previous day to 17:00 of the current day, in 30 minute intervals -- but it is written flexibly to handle different values.
The final SELECT is then a straight-forward, standard (left) join from that table to the rightFax table.
SET NOCOUNT ON
DECLARE @counts TABLE (
startTime DATETIME NOT NULL,
endTime DATETIME NOT NULL
 
DECLARE @startTime DATETIME
DECLARE @endTime DATETIME
DECLARE @interval SMALLINT --interval in minutes, e.g. 30
SET @startTime = DATEADD(DAY, -1, DATEADD(HOUR, 17, CONVERT(CHAR(8), GETDATE(), 112)))
SET @endTime = DATEADD(MS, -3, DATEADD(DAY, 1, @startTime))
SET @interval = 30
WHILE @startTime <= @endTime
BEGIN
INSERT INTO @counts VALUES(@startTime, DATEADD(MS, -3, DATEADD(MINUTE, @interval, @startTime)))
SET @startTime = DATEADD(MINUTE, @interval, @startTime)
END
SELECT CONVERT(CHAR(5), c.startTime, 8) + ' - ' + CONVERT(CHAR(9), c.endTime, 8) AS [Time Range],
COUNT(rf.time) AS [Count of Faxes]
FROM @counts c
LEFT OUTER JOIN rightFax rf ON rf.time BETWEEN c.startTime AND c.endTime
GROUP BY c.startTime, c.endTime
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply