Hourly BreakUp of DateTime column

• Dinendra Nath Dolai

  Mr or Mrs. 500

  Points: 594

  More actions

  September 20, 2008 at 2:33 am

  #193139

  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

• Johan Bijnens

  SSC Guru

  Points: 135506

  More actions

  September 20, 2008 at 4:02 am

  #874566

  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

• Dinendra Nath Dolai

  Mr or Mrs. 500

  Points: 594

  More actions

  September 28, 2008 at 10:43 am

  #878188

  Thanks a lot ALZDBA.I really appreciate this.This is exactly what I needed.

  Thank you again

• Johan Bijnens

  SSC Guru

  Points: 135506

  More actions

  September 28, 2008 at 1:20 pm

  #878198

  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