October 31, 2008 at 10:59 am
Hello. I need to pull a count from a 'service call' table.
The table has two useful columns. 'Case_Number' and 'End_Date_Time' (the date/time of phone calls)
Data needed: need to count billable hours.
any calls in the same 60 minute time period from first call = 1 point
after 60 minutes = an additional point etc..
cant just add hours, as calls recieved may be spaced out between days, hours or most likely in the same 60 minute periods
Problem: How can I group these calls in 60 minute clips in order to count them (# of groups)?
To anyone who can help, THANK YOU! :Wow:
Example info: (note: date info is in typical GetDate format. I've just written the bare times for clarity's sake)
Case_Number = 55
Calls came in at:
8:45
8:50
9:10
9:50
2:30
3:00
4:45
so
8:45 + 8:50 + 9:10 = 1 point
9:50 = 1 point
2:30 + 3:00 = 1 Point
4:45 = 1 Point
Total = 4 points
October 31, 2008 at 11:55 am
To be sure, we aren't talking about 60 minute blocks based on the first call for a given case, corret? That is why the 2:30 and 3:00 calls are grouped together, correct?
October 31, 2008 at 11:56 am
This should do it. In place of the bogus data and the table variable I'm using, create a temp table (with the exact same definition, the index is vital) and insert all your data into it. (I couldn't do that, because you didn't supply sample data or table DDL, please read the link in my signature for next time).
A full article of this method can be found here: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
SETUP: (To see this work, you run this and the bottom part at the same time)
[font="Courier New"]DECLARE @Calls TABLE(
Case_Number INT,
End_Date_Time DATETIME,
CallGroup INT,
PRIMARY KEY CLUSTERED(Case_Number, End_Date_Time))
DECLARE @GD DATETIME
SET @GD = GETDATE()
INSERT INTO @Calls(Case_Number, End_Date_Time)
SELECT 1, @GD-2 UNION ALL
SELECT 1, DATEADD(n,15,@GD-2) UNION ALL
SELECT 1, DATEADD(n,45,@GD-2) UNION ALL
SELECT 1, DATEADD(n,120,@GD-2) UNION ALL
SELECT 1, DATEADD(n,135,@GD-2) UNION ALL
SELECT 2, @GD-2 UNION ALL
SELECT 2, DATEADD(n,15,@GD-2) UNION ALL
SELECT 2, DATEADD(n,45,@GD-2) UNION ALL
SELECT 2, DATEADD(n,121,@GD-2) UNION ALL
SELECT 2, DATEADD(n,135,@GD-2) UNION ALL
SELECT 3, @GD-2 UNION ALL
SELECT 3, DATEADD(n,15,@GD-2) UNION ALL
SELECT 3, DATEADD(n,45,@GD-2) UNION ALL
SELECT 3, DATEADD(n,125,@GD-2) UNION ALL
SELECT 3, DATEADD(n,195,@GD-2)[/font]
SOLUTION:
[font="Courier New"]DECLARE @PrevCallGroupStart DATETIME,
@PrevCase_Number INT,
@CallGroup INT
SET @CallGroup = 1
SET @PrevCallGroupStart = (SELECT MIN(End_Date_Time) FROM @Calls)
UPDATE @Calls
SET @CallGroup = CallGroup = CASE WHEN Case_Number <> @PrevCase_Number THEN 1
WHEN DATEDIFF(n,@PrevCallGroupStart,End_Date_Time) > 60 THEN @CallGroup + 1
ELSE @CallGroup
END,
@PrevCallGroupStart = CASE WHEN Case_Number <> @PrevCase_Number OR DATEDIFF(n,@PrevCallGroupStart,End_Date_Time) > 60
THEN End_Date_Time
ELSE @PrevCallGroupStart
END,
@PrevCase_Number = Case_Number
FROM @Calls C
SELECT * FROM @Calls -- Show what you have.
SELECT Case_Number, COUNT(DISTINCT CallGroup) --This gives you number of call hours per Case_Number
FROM @Calls
GROUP BY Case_Number[/font]
ALSO, the update should have WITH INDEX(Clustered Index Name) added to it. I wasn't paying attention and didn't do add it because Table Variables don't like the 'WITH' syntax. What your clustered index is on is very important here, it should order the columns in the manner that they are updated.
October 31, 2008 at 12:08 pm
Seth,
Looks like you went the same direction I was look at as well, you just beat me to the post. I checked out your code, and it seems solid to me.
Great Job!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply