February 3, 2013 at 5:25 pm
I am working on sql 2005. I have a table wherein it maintains the history of status
Customer 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.
February 3, 2013 at 7:02 pm
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]
February 3, 2013 at 8:55 pm
Is this what you are expecting?
SELECT a.customer, a.status, DATEDIFF(dd, a.mlastupdt, getdate()) AS NumDays
FROM (SELECT Customer, max(LastUpdateDate) AS mlastupdt, [Status], createddate
FROM #TempTable
GROUP BY Customer, [Status], createddate) a
ORDER BY a.Customer, a.[Status]
February 4, 2013 at 6:00 am
@steve-2 - 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.
@dgowrij - thanks for replying. But your query gives me a different result. For example,CustA was present in status 2 for 15 days (01/20/2013 - 01/05/2013), but your query gives me 30 days.
February 4, 2013 at 7:56 am
Will this work:
SELECT A.Customer
, A.[Status]
, NumDays = SUM(DATEDIFF(d,A.LastUpdateDate , COALESCE(B.LastUpdateDate, GETDATE())))
FROM #TempTable A
OUTER APPLY (SELECT TOP 1 LastUpdateDate FROM #TempTable
WHERE LastUpdateDate > A.LastUpdateDate
AND A.Customer = Customer
ORDER BY LastUpdateDate) B
Group By A.Customer
, A.[Status]
February 4, 2013 at 8:56 am
@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.
@dgowrij - thanks for replying. But your query gives me a different result. For example,CustA was present in status 2 for 15 days (01/20/2013 - 01/05/2013), but your query gives me 30 days.
The biggest challenge we have here is that we don't know what your table actually looks like. If it is like Steven Willis posted then you need to explain the business rules. According to the data they appear to be in multiple statuses simultaneously.
Please take a few minutes to read the first link in my signature for best practices when posting questions.
--EDIT--
Quoted the wrong post.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2013 at 9:31 am
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
February 5, 2013 at 2:13 pm
This should work for you....
select Customer, status, sum(datediff(dd, createddate, lastupdatedate))
from #TempTable
group by Customer, status
order by customer
February 15, 2013 at 2:59 pm
I think we need to know what the two date columns represent, so that we do not guess. It looks to me the update column is like a record time stamp, and irrelevant. Is this correct? If a customer enters status 1 on a day and leaves that status four hours later yet in the same day... do you want to count that as 0 days? What if the four hours take the status past midnight? Should you maybe be using a time portion to extract day fractions? This is part of your business rules that we may need to know.
----------------------------------------------------
February 15, 2013 at 3:19 pm
Perhaps this?
if exists(select 1 from tempdb.sys.objects where object_id = object_id('tempdb..#TempTable'))
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';
go
with BaseData as (
select
Customer,
Status,
CreatedDate,
LastUpdateDate,
rn = row_number() over (partition by Customer order by LastUpdateDate)
from
#TempTable
)
select
bd1.Customer,
bd1.Status,
NumOfDays = sum(datediff(dd,bd1.LastUpdateDate,isnull(bd2.LastUpdateDate, getdate())))
from
BaseData bd1
left outer join BaseData bd2
on (bd1.Customer = bd2.Customer and
bd1.rn = bd2.rn - 1)
group by
bd1.Customer,
bd1.Status
order by
bd1.Customer,
bd1.Status;
go
if exists(select 1 from tempdb.sys.objects where object_id = object_id('tempdb..#TempTable'))
DROP TABLE #TempTable;
go
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply