pjrpjr7 (2/4/2013)
@Steve - thanks for replying. But your query gives me a different result. Your query gives me the number of TIMES custA's status was changed (Ex: custA status1, numdays = 2 - custA's status was updated to 1 twice. But, what I want is CustA has status 1 for ((01/01/2013 - 01/05/2013 = 4 days) + (01/20/2013 - 02/01/2013 = 11 days)) = 15 days.
I guess I didn't understand your question. So you want the total number of days in the DATE RANGE a customer was in a certain status? Like: Status 1 from 1/1/2013 to 1/2/2013 and from 1/15/201 to 1/18/2013 would be 2 days + 3 days = 5 days total for Status 1?
Like this?
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable(
[ID] INT IDENTITY(1,1) NOT NULL,
[Customer] VARCHAR(50) NULL,
[Status] INT NULL,
[CreatedDate] DATETIME NULL,
[LastUpdateDate] DATETIME NULL,
PRIMARY KEY (ID))
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/05/2013','01/20/2013'
UNION
SELECT 'CustA',3,'01/20/2013','02/01/2013'
UNION
SELECT 'CustB',1,'01/01/2013','01/07/2013'
UNION
SELECT 'CustC',1,'01/01/2013','01/15/2013'
UNION
SELECT 'CustB',2,'01/07/2013','01/20/2013'
UNION
SELECT 'CustB',3,'01/20/2013','01/20/2013'
UNION
SELECT 'CustC',2,'01/15/2013','02/01/2013'
SELECT
Customer
,Status
,MIN(CreatedDate) AS EarliestDate
,MAX(LastUpdateDate) AS LatestDate
,SUM(Duration) AS TotalDays
FROM
(
SELECT
tt1.Customer
,tt1.[Status]
,tt1.CreatedDate
,tt1.LastUpdateDate
,DATEDIFF(day,CreatedDate,LastUpdateDate)+1 AS Duration
FROM
#TempTable AS tt1
) totals
GROUP BY
Customer
,Status
ORDER BY
Customer
,Status
Output:
CustomerStatusEarliestDateLatestDateTotalDays
CustA12013-01-01 00:00:00.0002013-01-20 00:00:00.00017
CustA22013-01-01 00:00:00.0002013-01-05 00:00:00.0005
CustA32013-01-20 00:00:00.0002013-02-01 00:00:00.00013
CustB12013-01-01 00:00:00.0002013-01-07 00:00:00.0007
CustB22013-01-07 00:00:00.0002013-01-20 00:00:00.00014
CustB32013-01-20 00:00:00.0002013-01-20 00:00:00.0001
CustC12013-01-01 00:00:00.0002013-01-15 00:00:00.00015
CustC22013-01-15 00:00:00.0002013-02-01 00:00:00.00018