SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help needed for sql query


help needed for sql query

Author
Message
pjrpjr7
pjrpjr7
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 64
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.
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2121 Visits: 1721
pjrpjr7 (2/3/2013)
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.



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]



dgowrij
dgowrij
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 75
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]
pjrpjr7
pjrpjr7
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 64
@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.
agavrila
agavrila
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 217
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]
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64732 Visits: 17979
[b]@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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2121 Visits: 1721
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:


Customer Status EarliestDate LatestDate TotalDays
CustA 1 2013-01-01 00:00:00.000 2013-01-20 00:00:00.000 17
CustA 2 2013-01-01 00:00:00.000 2013-01-05 00:00:00.000 5
CustA 3 2013-01-20 00:00:00.000 2013-02-01 00:00:00.000 13
CustB 1 2013-01-01 00:00:00.000 2013-01-07 00:00:00.000 7
CustB 2 2013-01-07 00:00:00.000 2013-01-20 00:00:00.000 14
CustB 3 2013-01-20 00:00:00.000 2013-01-20 00:00:00.000 1
CustC 1 2013-01-01 00:00:00.000 2013-01-15 00:00:00.000 15
CustC 2 2013-01-15 00:00:00.000 2013-02-01 00:00:00.000 18


sk-458789
sk-458789
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 46
This should work for you....

select Customer, status, sum(datediff(dd, createddate, lastupdatedate))
from #TempTable
group by Customer, status
order by customer
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7405 Visits: 2033
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.

----------------------------------------------------
How to post forum questions to get the best help
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98012 Visits: 38995
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search