April 14, 2008 at 8:34 am
Good day - I have the following TSQL -
--==== Create sample table
CREATE TABLE #tbl1
(
EndTime DATETIME,
somethingelse CHAR(2)
)
--==== Sample date
INSERT INTO #tbl1 VALUES ('2007-01-01 09:24:03.077', 'a')
INSERT INTO #tbl1 VALUES ('2008-01-01 09:26:03.077', 'b')
INSERT INTO #tbl1 VALUES ('2007-01-02 09:24:03.077', 'c')
INSERT INTO #tbl1 VALUES ('2007-01-02 09:26:03.077', 'd')
INSERT INTO #tbl1 VALUES ('2007-01-01 09:31:03.077', 'e')
INSERT INTO #tbl1 VALUES ('2008-01-02 09:31:03.077', 'f')
--==== The statement
SELECT 1 AS ID, -- dummy value
CONVERT(VARCHAR(10), s.endtime, 101) AS [Date],
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END AS TimeGroup,
COUNT(*) AS 'CallCount'
FROM#tbl1 s
GROUP BY CONVERT(VARCHAR(10), s.endtime, 101),
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END
ORDER BY CONVERT(VARCHAR(10), s.endtime, 101),
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END
DROP TABLE [#tbl1]
and when I run it, it is returning results that are not quite in the correct order:
101/01/200709:001
101/01/200709:301
101/01/200809:001
101/02/200709:002
101/02/200809:301
How could I do this and get all the 2007 data followed by the 2008 data? I know it is doing this because of the convert, however, the table I will eventually export this to wants the data in MM/DD/YYYY format.
Also - a coworker suggested I use a calender table for both the Date field and the time bucket, as to reduce the converts and such. I have looked, I see something similar (I think) Jeff M suggests, but, not exactly sure how it would help.
Any help would be greatly appreciated!
-- Cory
April 14, 2008 at 8:54 am
A datetime dimensions table would definitely be the way to go.
Create a table with half-hour increments of dates and times. Should have a "begin" column and an "end" column. Then join to that and do your counts, order by, etc., off of those fields. Will perform MUCH better.
Example:
create table dbo.TimeBy30Min (
StartTime datetime primary key,
EndTime as dateadd(minute, 30, starttime))
go
insert into dbo.TimeBy30Min (StartTime)
select dateadd(minute, 30 * number, '1/1/2000')
from dbo.BigNumbers
(I have a BigNumbers table that goes up to 1-million from 0.)
That will give you half-hour increments from 1 Jan 2000 midnight to 15 Jan 2057 8 AM. Add to the number range, and modify the start date, as needed.
Then, either of these:
select 1 as ID,
starttime, count(*) as CallCount
from dbo.TimeBy30Min t30
inner join #tbl1
on #tbl1.endtime >= t30.starttime
and #tbl1.endtime < t30.endtime
group by t30.starttime
order by StartTime
or
;with CTE (ID, StartTime, CallCount) as
(select 1,
starttime, count(*)
from dbo.TimeBy30Min t30
inner join #tbl1
on #tbl1.endtime >= t30.starttime
and #tbl1.endtime < t30.endtime
group by t30.starttime)
select ID, convert(varchar(100), StartTime, 101) as Date,
left(convert(varchar(100), StartTime, 108), 5) as TimeGroup,
CallCount
from CTE
order by StartTime
The first won't format your data, but you should really have the front-end application do that anyway. The second will format it, but will be slightly slower (more expensive).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 9:16 am
Simply ordering by the datetime field itself and not the "display version" would get your data in the right order.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 14, 2008 at 9:24 am
Matt Miller (4/14/2008)
Simply ordering by the datetime field itself and not the "display version" would get your data in the right order.
I had tried that, and when I do, I get
Msg 8127, Level 16, State 1, Line 17
Column "#tbl1.EndTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Is there something wrong?
ORDER BY s.endtime,--CONVERT(VARCHAR(10), s.endtime, 101),
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END
-- Cory
April 14, 2008 at 9:55 am
Cory Ellingson (4/14/2008)
Matt Miller (4/14/2008)
Simply ordering by the datetime field itself and not the "display version" would get your data in the right order.I had tried that, and when I do, I get
Msg 8127, Level 16, State 1, Line 17
Column "#tbl1.EndTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Is there something wrong?
ORDER BY s.endtime,--CONVERT(VARCHAR(10), s.endtime, 101),
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END
Hmm... I must be asleep at the wheel. Yes - the error is appropriate. I didn't read that closely enough. Something different's needed.
This should allow you to do it all in one field:
dateadd(minute,((datediff(minute,0,endtime)/30)*30),0)
Of course - if you run this a lot- I would actually make that a computed persisted column in your table, and call that instead of the calculation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 14, 2008 at 9:58 am
Try using your select statement as derived table like the following:
SELECT ID,
[Date],
TimeGroup,
CallCount
FROM (SELECT 1 AS ID, -- dummy value
CONVERT(VARCHAR(10), s.endtime, 101) AS [Date],
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END AS TimeGroup,
COUNT(*) AS 'CallCount'
FROM #tbl1 s
GROUP BY CONVERT(VARCHAR(10), s.endtime, 101),
CASE WHEN DATEPART(MI, s.endtime) BETWEEN 0 AND 29
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':00'
WHEN DATEPART(MI, s.endtime) BETWEEN 30 AND 59
THEN RIGHT('0' + CAST(DATEPART(HH, s.endtime) AS VARCHAR(2)), 2) + ':30'
END) a
ORDER BY CONVERT(datetime, [Date]), TimeGroup
Dave Novak
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply