help needed for sql query

  • 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.

  • 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]

  • 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]

  • @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.

  • 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]

  • @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/

  • 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

  • This should work for you....

    select Customer, status, sum(datediff(dd, createddate, lastupdatedate))

    from #TempTable

    group by Customer, status

    order by customer

  • 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.

    ----------------------------------------------------

  • 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