Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help needed for sql query Expand / Collapse
Author
Message
Posted Sunday, February 3, 2013 5:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 2:51 PM
Points: 29, 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.
Post #1415079
Posted Sunday, February 3, 2013 7:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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]



Post #1415084
Posted Sunday, February 3, 2013 8:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:32 AM
Points: 50, 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]
Post #1415091
Posted Monday, February 4, 2013 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 2:51 PM
Points: 29, 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.
Post #1415255
Posted Monday, February 4, 2013 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 126, Visits: 116
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]
Post #1415316
Posted Monday, February 4, 2013 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,296, Visits: 12,147
[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 Moden's 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)
Post #1415361
Posted Monday, February 4, 2013 9:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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

Post #1415374
Posted Tuesday, February 5, 2013 2:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 3, 2014 7:55 PM
Points: 90, Visits: 46
This should work for you....

select Customer, status, sum(datediff(dd, createddate, lastupdatedate))
from #TempTable
group by Customer, status
order by customer
Post #1416097
Posted Friday, February 15, 2013 2:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 355, Visits: 870
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.

Post #1420789
Posted Friday, February 15, 2013 3:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 23,277, Visits: 31,997
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





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)
Post #1420803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse