help needed for sql query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » help needed for sql query

help needed for sql query

Rate Topic

Display Mode

Topic Options

Author

Message

pjrpjr7

Posted Sunday, February 03, 2013 5:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:55 AM
Points: 27, Visits: 61

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

Steven Willis

Posted Sunday, February 03, 2013 7:01 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:16 PM
Points: 298, Visits: 1,319

pjrpjr7 (2/3/2013)


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

dgowrij

Posted Sunday, February 03, 2013 8:55 PM

Valued 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

pjrpjr7

Posted Monday, February 04, 2013 6:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:55 AM
Points: 27, Visits: 61

@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

agavrila

Posted Monday, February 04, 2013 7:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:51 AM
Points: 126, Visits: 112

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

Sean Lange

Posted Monday, February 04, 2013 8:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523

[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

Post #1415361

Steven Willis

Posted Monday, February 04, 2013 9:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:16 PM
Points: 298, Visits: 1,319

pjrpjr7 (2/4/2013)

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

sk-458789

Posted Tuesday, February 05, 2013 2:13 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:54 PM
Points: 90, Visits: 39

This should work for you....

select Customer, status, sum(datediff(dd, createddate, lastupdatedate))
from #TempTable
group by Customer, status
order by customer

Post #1416097

mmartin1

Posted Friday, February 15, 2013 2:59 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:02 PM
Points: 198, Visits: 656

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

Lynn Pettis

Posted Friday, February 15, 2013 3:19 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 21,832, Visits: 27,850

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 »

Permissions