question

jhowe avatar image
jhowe asked

query help

Hi all, apologies if this is simple... my brain doesn't seem to be working this friday afternoon. I received this query, *please can we get an SQL report run, which measures customer ID, number of requests before complaint, date of complaint and then number of requests after complaint.* For some reason i can't get my head round this... please see code below for creating tables and sample data. complaints are serviceid 51, so i want to count requests before the first instance of serviceid 51 per customerid and then requests after... any ideas? CREATE TABLE #Customer (CustomerID INT, FirstName NVARCHAR(30), LastName NVARCHAR(30)) INSERT INTO #Customer ( CustomerID, FirstName, LastName ) VALUES ( 1, -- CustomerID - int N'Joe', -- FirstName - nvarchar(30) N'Bloggs' -- LastName - nvarchar(30) ) CREATE TABLE #ServiceRequest (CustomerID INT, ServiceRequestID INT, ServiceID INT, CreatedDateTime DATETIME) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 1 , -- ServiceRequestID - int 1 , -- ServiceID - int '2013-07-01 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 2 , -- ServiceRequestID - int 5 , -- ServiceID - int '2013-07-02 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 3 , -- ServiceRequestID - int 7 , -- ServiceID - int '2013-07-03 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 4 , -- ServiceRequestID - int 6 , -- ServiceID - int '2013-07-04 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 5 , -- ServiceRequestID - int 11 , -- ServiceID - int '2013-07-05 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 6 , -- ServiceRequestID - int 51 , -- ServiceID - int '2013-07-06 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 7 , -- ServiceRequestID - int 31 , -- ServiceID - int '2013-07-07 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 8 , -- ServiceRequestID - int 20 , -- ServiceID - int '2013-07-08 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 9 , -- ServiceRequestID - int 19 , -- ServiceID - int '2013-07-09 14:22:26' -- CreatedDateTime - datetime ) INSERT INTO #ServiceRequest ( CustomerID , ServiceRequestID , ServiceID , CreatedDateTime ) VALUES ( 1 , -- CustomerID - int 10 , -- ServiceRequestID - int 41 , -- ServiceID - int '2013-07-10 14:22:26' -- CreatedDateTime - datetime ) SELECT * FROM #Customer JOIN #ServiceRequest ON #Customer.CustomerID = #ServiceRequest.CustomerID
sql-server-2008sql-server-2008-r2tsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
great sample data setup!
0 Likes 0 ·

1 Answer

·
KenJ avatar image
KenJ answered
This might get you close. It only accounts for a single complaint - the most recent. If you have multiple complaints, all previous complaints are generically lumped into RequestsBeforeComplaint. ;WITH ServiceId51 (ServiceRequestId51, CustomerId) AS(SELECT MAX(sr.ServiceRequestId), sr.CustomerID FROM #Customer AS c JOIN #ServiceRequest AS sr ON c.CustomerID = sr.CustomerID WHERE sr.ServiceID = 51 GROUP BY sr.CustomerID) SELECT c.CustomerId, SUM(CASE WHEN sr.ServiceRequestId < si51.ServiceRequestId51 THEN 1 ELSE 0 END) AS RequestsBeforeComplaint, SUM(CASE WHEN sr.ServiceRequestId > si51.ServiceRequestId51 THEN 1 ELSE 0 END) AS RequestsAfterComplaint, MAX(CASE WHEN sr.ServiceRequestID = si51.ServiceRequestId51 THEN sr.CreatedDateTime ELSE NULL END) AS DateOfComplaint FROM #Customer AS c JOIN #ServiceRequest AS sr ON c.CustomerID = sr.CustomerID JOIN ServiceId51 AS si51 ON sr.CustomerID = si51.CustomerId GROUP BY c.CustomerId
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
I think this will do the trick thanks! I was going along the same lines with doing a CTE, but didn't get the detail right... can you explain the *If you have multiple complaints, all previous complaints are generically lumped into RequestsBeforeComplaint.* in a bit more detail?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
The CTE gets the MAX ServiceRequestId of ALL ServiceId 51s for each CustomerID. If you have more than one complaint, any ServiceId 51 with a ServiceRequestId that's not the MAX ServiceRequestId is simply not recognized as a complaint
0 Likes 0 ·
jhowe avatar image jhowe commented ·
hmm... this would skew the count then? so if there are multiple complaints it would increase the count of requests before complaint? Is there a way to expand this easily so that if there's more than one complaint it does a count before 1st complaint Requestsbefore/after then date of second complaint requests before/after then third etc.?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Yes, it could skew the count. You would have the same problem only counting off of the first complain using MIN() - all subsequent complaints would be lumped together with RequestsAfterComplaint I've seen some queries floating around this site that could be adapted to count the "befores" "betweens" and "afters" for multiple complaints. I'll see if I can track one down
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Thanks so much for your help... to be honest i may have to stick with the original because some of the customers have over 20 complaints, the format of the report will just go crazy if you split it out by each complaint... i don't know another way of doing it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.