question

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.