September 20, 2008 at 2:33 am
Hi All,
I am having some problem with hourly breakup of a datetime column.Let me explain the problem with sample data.
I have a Table named AgentSession with the following structre
AgentSession(AgentID[integer],SessionStart[datetime],TalkTime[decimal],BreakTime[decimal])
Here I am also giving some sample data
AgentID SessionStart TalkTime BreakTime
1 20 Sep 08 11:15:00 2700 900
1 20 Sep 08 12:15:00 2900 700
1 20 Sep 08 13:15:00 2900 700
2 20 Sep 08 11:15:00 2700 900
2 20 Sep 08 12:15:00 2700 900
Now what I need out of this data is a 24 hours break up of sum of TalkTime and BreakTime for a specific day like the following
OutPut
Hours TotalTalkTime TotalBreakTime
0 0 0
1 0 0
1 0 0
1 0 0
.
.
.
11 5400 1800
12 5600 1600
13 2900 700
14 0 0
.
.
.
23 0 0
Please help me.Thanks in advance
September 20, 2008 at 4:02 am
How about:
declare @AgentSession table (AgentID [integer],SessionStart [datetime],TalkTime [decimal],BreakTime [decimal])
set nocount on
Insert into @AgentSession values (1, '20080920 11:15:00', 2700, 900)
Insert into @AgentSession values (1, '20080920 12:15:00', 2700, 900)
Insert into @AgentSession values (1, '20080920 13:15:00', 2700, 900)
Insert into @AgentSession values (2, '20080920 11:15:00', 2700, 900)
Insert into @AgentSession values (2, '20080920 12:15:00', 2700, 900)
select * from @AgentSession
Set nocount off
Select
T.N as TheHour
, sum (isnull(S.TalkTime,0)) as sum_TalkTime
, sum (isnull(S.BreakTime,0)) as sum_BreakTime
from TempDB.dbo.Tally T
left join @AgentSession S
on T.N = datepart(hh, S.SessionStart)
Where T.N < 24
group by T.N
order by TheHour
/*
*
* http://www.sqlservercentral.com/articles/TSQL/62867/#
*
* The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
* By Jeff Moden, 2008/05/07
*
*/
/*
USE TempDB
--DB that everyone has where we can cause no harm
SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME
--Timer to measure total duration
SET @StartTime = GETDATE()
--Start the timer --=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 10000 --equates to more than 30 years of dates
IDENTITY( INT,0,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
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
*/
Keep in mind to start your Tally table with 0 (zero) !
There final result
TheHour sum_TalkTime sum_BreakTime
----------- --------------------------------------- ---------------------------------------
0 0 0
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 5400 1800
12 5400 1800
13 2700 900
14 0 0
15 0 0
16 0 0
17 0 0
18 0 0
19 0 0
20 0 0
21 0 0
22 0 0
23 0 0
(24 row(s) affected)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 28, 2008 at 10:43 am
Thanks a lot ALZDBA.I really appreciate this.This is exactly what I needed.
Thank you again
September 28, 2008 at 1:20 pm
HTH
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply