question

jhowe avatar image
jhowe asked

report query!

Hi all i have recieved the following request : *I've been working to extract some data and been advised that the second part would be best completed by you. I attach a report provided of the complaint cases in the last 12 months. Having got this data and seen that near on 2000 complaints have been run in the last 12 months, are you able to provide the following : In regard customers that have complained in the last 12 months: 1. How many have had services with us since they complained - and how many services? Many thanks* getting the complaints in the last year is the easy part. Consider the following code : DROP TABLE #Customer CREATE TABLE #Customer ( CustomerID INT ) INSERT #Customer ( CustomerID ) VALUES ( 1 ) -- CustomerID - int INSERT #Customer ( CustomerID ) VALUES ( 2 ) -- CustomerID - int INSERT #Customer ( CustomerID ) VALUES ( 3 ) -- CustomerID - int INSERT #Customer ( CustomerID ) VALUES ( 4 ) -- CustomerID - int INSERT #Customer ( CustomerID ) VALUES ( 5 ) -- CustomerID - int DROP TABLE #Request CREATE TABLE #Request ( RequestID INT , CustomerID INT , CreatedDateTime DATETIME ) INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 1 , -- RequestID - int 1 , -- CustomerID - int '2011-07-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 2 , -- RequestID - int 2 , -- CustomerID - int '2011-08-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 3 , -- RequestID - int 3 , -- CustomerID - int '2011-09-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 4 , -- RequestID - int 4 , -- CustomerID - int '2011-10-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 5 , -- RequestID - int 5 , -- CustomerID - int '2011-11-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 6 , -- RequestID - int 1 , -- CustomerID - int '2012-07-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 7 , -- RequestID - int 2 , -- CustomerID - int '2012-08-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 8 , -- RequestID - int 3 , -- CustomerID - int '2012-09-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 9 , -- RequestID - int 4 , -- CustomerID - int '2012-10-05 12:17:29' ) -- CreatedDateTime - datetime INSERT INTO #Request ( RequestID , CustomerID , CreatedDateTime ) VALUES ( 10 , -- RequestID - int 5 , -- CustomerID - int '2012-11-05 12:17:29' ) -- CreatedDateTime - datetime DROP TABLE #Complaint CREATE TABLE #Complaint ( ComplaintID INT , RequestID INT , ) INSERT INTO #Complaint ( ComplaintID , RequestID ) VALUES ( 1 , -- ComplaintID - int 1 -- RequestID - int ) -- ComplaintDateTime - datetime INSERT INTO #Complaint ( ComplaintID , RequestID , ) VALUES ( 2 , -- ComplaintID - int 2 -- RequestID - int ) -- ComplaintDateTime - datetime INSERT INTO #Complaint ( ComplaintID , RequestID , ) VALUES ( 3 , -- ComplaintID - int 3 -- RequestID - int ) -- ComplaintDateTime - datetime INSERT INTO #Complaint ( ComplaintID , RequestID , ) VALUES ( 4 , -- ComplaintID - int 4 -- RequestID - int ) -- ComplaintDateTime - datetime INSERT INTO #Complaint ( ComplaintID , RequestID ) VALUES ( 5 , -- ComplaintID - int 5 -- RequestID - int ) -- ComplaintDateTime - datetime --Complaints in last 12 months SELECT COUNT(*) FROM #Complaints WHERE CreatedDateTime BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE() How do i do another count to get services since they complained??? Thanks for your help.
sql-server-2008sql-server-2008-r2tsql
7 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.

You might want to edit your code - it doesn't run...
0 Likes 0 ·
I know i realised that a little while ago but i can't see WHY???
0 Likes 0 ·
1.missing braces in VALUES statements 2.#complaints table not defined anywhere 3.CreatedDateTime not a column in complaints table
0 Likes 0 ·
also given the data, there are no requests after the complaint date for any complaint in the last 12 months....
0 Likes 0 ·
There is no information for column mapping...? How you identify the service?????
0 Likes 0 ·
Show more comments

1 Answer

·
Kev Riley avatar image
Kev Riley answered
select complaintsinlast12months.customerid, count(distinct #request.requestid) as NumRequests, count(complaintsinlast12months.requestid) as NumComplaints from #request right join ( select customerid, #Complaint.requestid, min(ComplaintDateTime) as ComplaintDate from #Complaint join #request on #request.requestid = #complaint.RequestID WHERE ComplaintDateTime BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE() group by #Complaint.requestid, customerid ) complaintsinlast12months on complaintsinlast12months.customerid = #request.customerid and #request.CreatedDateTime >= complaintsinlast12months.ComplaintDate group by complaintsinlast12months.customerid
3 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.

complaintsinlast12months isn't a table? i just had some text commented out...
0 Likes 0 ·
complaintsinlast12months is an in-line derived view
0 Likes 0 ·
i've just found an issue with this, i made a mistake in the tables. In the complaint table there is no createddatetime field...
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.