pjrpjr7 (2/3/2013)
I am working on sql 2005. I have a table wherein it maintains the history of statusCustomer Status CreatedDate LastUpdateDate
CustA 1 01/01/2013 01/01/2013
CustA 2 01/01/2013 01/05/2013
CustA 1 01/01/2013 01/20/2013
CustA 3 01/01/2013 02/01/2013
Now what I want it, I need to count the number of days CustA is in status 1, 2 and 3.
Cust Status NumberofDays
CustA 1 15
CustA 2 15
CustA 3 2 (getdate()-02/01/2013)
Can someone please tell me as how do I write a query for this.
DROP TABLE #TempTable
CREATE TABLE #TempTable(
[Customer] VARCHAR(50) NOT NULL,
[Status] INT NULL,
[CreatedDate] DATETIME NULL,
[LastUpdateDate] DATETIME NULL,
)
INSERT INTO #TempTable
SELECT 'CustA',1,'01/01/2013','01/01/2013'
UNION
SELECT 'CustA',2,'01/01/2013','01/05/2013'
UNION
SELECT 'CustA',1,'01/01/2013','01/20/2013'
UNION
SELECT 'CustA',3,'01/01/2013','02/01/2013'
UNION
SELECT 'CustB',1,'01/01/2013','01/01/2013'
UNION
SELECT 'CustC',2,'01/01/2013','01/05/2013'
UNION
SELECT 'CustB',1,'01/01/2013','01/20/2013'
UNION
SELECT 'CustB',3,'01/01/2013','02/01/2013'
SELECT
Customer
,[Status]
,COUNT([Status]) AS NumDays
FROM
#TempTable
GROUP BY
Customer
,[Status]
ORDER BY
Customer
,[Status]