grouping by specific time frames 1

[ter79]

I have a post on Microsoft's Newsgroup - SQL Programming, thought I try here also.

I have the following code that works perfectly, that I got from MS Newsgroup

SELECT U.agent, U.phour,
 COUNT(F.agentid),
 COALESCE(SUM(CAST(F.pamount AS NUMERIC(10,2))),0),
 COALESCE(SUM(CASE DATEDIFF(HOUR,F.pdate,F.vdate) WHEN 0 THEN 1 END),0),
 COALESCE(SUM(CASE DATEDIFF(HOUR,F.pdate,F.vdate) WHEN 0 THEN
  CAST(psum AS NUMERIC(10,2)) END),0),
 MIN(U.shift)
 FROM
  (SELECT agent,
   DATEPART(HOUR, [date]) AS phour,
   CASE WHEN CONVERT(CHAR(5),MIN([date]),8)<'16:20' THEN 'D' ELSE 'N' END
   FROM Userstat
   WHERE [date] >= DATEADD(HOUR,10,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
    AND [date] < DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
    AND LEFT(agent, 1) = 'F'
   GROUP BY agent, DATEPART(HOUR, [date])) AS U(agent, phour, shift)
 LEFT JOIN Fr AS F
  ON U.agent = F.agentid
   AND F.pdate >= DATEADD(HOUR,10,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
   AND F.pdate < DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
   AND U.phour = DATEPART(HOUR, F.[pdate])
 GROUP BY U.agent, U.phour

It returns all the users from the 1st table (Sample data at the end) and then summarizes the data from the second table for each user and does place zeros (0) if the user does not have any records.

However, I was thrown a "curve" today and now I have to put the users and summaries within specific time frames and for whatever date I choose.

I can also get rid of the CASE statement, since I have to put the summary in time frames

CASE WHEN CONVERT(CHAR(5),MIN([date]),8)<'16:20' THEN 'D' ELSE 'N' END

The time frames are:
10 - 11 AM
11 - 12 PM
12 - 12:30 PM
1:15 PM - 2:15 PM
2:15 pm - 3:15 PM
3:15 PM - 4:15 PM
4:30 PM - 5:30 PM
5:30 PM - 6:30 PM
6:30 PM - 7:00 PM
7:30 PM - 8:30 PM
8:30 PM - 9:30 PM
9:30 PM - 10:00 PM

Sample Data

Create Table USERSTAT(
        [Agent]  [nvarchar] (10),
        [Date]    [datetime])

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F1234', '03/25/2004 10:00:00')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F1234', '03/25/2004 10:10:00')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F4321', '03/25/2004 11:15:55')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F4321', '03/25/2004 17:00:00')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F1234', '03/25/2004 21:05:00')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F1234', '03/25/2004 11:05:00')

INSERT INTO USERSTAT(Agent, Date)
VALUES ('F4321', '03/25/2004 11:55:00')


This is the 2nd table, where I get the summary

Create Table FR(
	[AgentID] [nvarchar] (10) NULL,
	[PDate] [datetime] NULL,
	[PAmount] [nvarchar] (10) NULL,
	[VDate] [datetime] NULL,
	[PSum] [nvarchar] (10) Null)

INSERT INTO FR (AgentID, PDATE, PAmount, VDate, PSum)
VALUES ('F1234', '03/25/2004 10:00:00', '500', '03/25/2004 10:05:00', '500')

INSERT INTO FR(AgentID, PDATE, PAmount, VDate, PSum)
VALUES ('F1234', '03/25/2004 17:10:00', '500', '03/25/2004 10:35:00', '500')

INSERT INTO FR(AgentID, PDATE, PAmount, VDate, PSum)
VALUES ('F4321', '03/25/2004 21:29:00', '250', '03/25/2004 10:35:00', '250')

INSERT INTO FR(AgentID, PDATE, PAmount, VDate, PSum)
VALUES ('F4321', '03/25/2004 10:45:00', '250', '03/25/2004 11:05:00', '250')

INSERT INTO FR(AgentID, PDATE, PAmount, VDate, PSum)
VALUES ('F4321', '03/25/2004 12:15:00', '500', '03/25/2004 11:19:00', '500')

Any ideas in the right direction is appreciated

 

[TJRTech]

First, and foremost, please consider using an integer or some other numeric value for your PAmount column.

Other than that, it seems to me that an additional computed columns, called "Period" that given PDate value returns either a label, name or ordinal for the corresponding period, one value for each of the following:

10 - 11 AM
11 - 12 PM
12 - 12:30 PM
1:15 PM - 2:15 PM
2:15 pm - 3:15 PM
3:15 PM - 4:15 PM
4:30 PM - 5:30 PM
5:30 PM - 6:30 PM
6:30 PM - 7:00 PM
7:30 PM - 8:30 PM
8:30 PM - 9:30 PM
9:30 PM - 10:00 PM

Then, in your queries, you can sum PAmount, grouping by "Period".

TR