none
Time difference between states RRS feed

  • Question

  • My sql server table has datetime and state column. There are 4 states Waiting For Preperation, Waiting For Assignment, Assigned To Driver and Delivered. This is the delivery tracking report for POS software. The user can skip the state. Means the user can assign the 4th state (Delivered) from the first status (Waiting For Preperation). I have to make the report showing how much time, each state is taking. For the above scenario, I have to show first, 4th and 1st state with values and 2nd and 3rd state with 0 value. And also for 1st state, I have to show the time taken between 1st and 4th state. May I know how to do it?

    Thanks


    test
    • Moved by Dan Benediktson Friday, June 25, 2010 4:33 PM (From:SQL Server Data Access)
    Friday, June 25, 2010 2:50 PM

Answers

  • Here is the idea:

     

    declare @t table(Id int identity(1,1) primary key, RecId int, [State] int, DateFld date)
    insert into @t values(1,1,'06/01/2010')
    insert into @t values(1,3,'06/03/2010') 
    insert into @t values(1,4,'06/06/2010')
    insert into @t values(2,1,'06/09/2010')
    insert into @t values(2,2,'06/10/2010')
    insert into @t values(2,4,'06/12/2010')
    
    ;with cte as (select RecID, [1] as [State1], [2] as State2, [3] as State3, [4] as State4
    from (select RecID, [State], DateFld from @t) t
    PIVOT (max(DateFld) for [State] in ([1],[2],[3],[4])) pvt)
    
    select c1.RecID, c1.State1, 
    case when c1.State2 IS NULL then 0 else DATEDIFF(day, c1.State1, c1.State2) end as [Days Between 1&2],
    case when c1.State3 IS NULL then 0 else DATEDIFF(day, coalesce(c1.State2,c1.State1), c1.State3) end as [Days Between 2&3],
    case when c1.State4 IS NULL then 0 else DATEDIFF(day, coalesce(c1.State3, c1.State2, c1.State1), c1.State4) end 
    as [Days Between 3&4],
    case when c1.State4 IS NULL then 0 else DATEDIFF(day, c1.State1, c1.State4) end as [Days Between 1 & 4]
    from cte c1 
    

    If you're using datetime, you may want to report time difference in seconds instead of days, for example. The idea still will be the same, you just use second as the first parameter in DATEDIFF function.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, June 25, 2010 5:47 PM
    Moderator
  • DECLARE @InvoiceDeliveries TABLE

    (

    InvoiceNumber     INT

    ,State                  INT

    ,[Datetime]       DATETIME

    )

     

    INSERT INTO @InvoiceDeliveries

    SELECT  1,0,'6/25/2010 11:10 AM' UNION

    SELECT  1,1,'6/25/2010 11:30 AM' UNION

    SELECT  1,2,'6/25/2010 11:55 AM' UNION

    SELECT  1,3,'6/25/2010 12:10 PM' UNION

    SELECT  2,0,'6/25/2010 05:50 PM' UNION

    SELECT  2,2,'6/25/2010 06:30 PM' UNION

    SELECT  2,3,'6/25/2010 06:40 PM'

     

           

    SELECT X.InvoiceNumber

            ,DATEDIFF(MI,X.State0date,COALESCE (x.State1date,x.State2date,x.State3date))

            ,DATEDIFF(MI,X.State1date,COALESCE (x.State2date,x.State3date))

            ,DATEDIFF(MI,X.State2date,x.State3date)

      FROM

          (

          SELECT InvoiceNumber

                  ,State0date = MAX(CASE WHEN STATE =0 THEN [DateTime] END)

                  ,State1date = MAX(CASE WHEN STATE =1 THEN [DateTime] END)

                  ,State2date = MAX(CASE WHEN STATE =2 THEN [DateTime] END)

                  ,State3date = MAX(CASE WHEN STATE =3 THEN [DateTime] END)

            FROM @InvoiceDeliveries

            GROUP BY InvoiceNumber

          ) X

    • Marked as answer by Muniappan Monday, June 28, 2010 2:44 PM
    Saturday, June 26, 2010 6:07 AM

All replies

  • Could you please post the table structures and some sample data to porceed further ?

    Friday, June 25, 2010 5:12 PM
  • Here is the idea:

     

    declare @t table(Id int identity(1,1) primary key, RecId int, [State] int, DateFld date)
    insert into @t values(1,1,'06/01/2010')
    insert into @t values(1,3,'06/03/2010') 
    insert into @t values(1,4,'06/06/2010')
    insert into @t values(2,1,'06/09/2010')
    insert into @t values(2,2,'06/10/2010')
    insert into @t values(2,4,'06/12/2010')
    
    ;with cte as (select RecID, [1] as [State1], [2] as State2, [3] as State3, [4] as State4
    from (select RecID, [State], DateFld from @t) t
    PIVOT (max(DateFld) for [State] in ([1],[2],[3],[4])) pvt)
    
    select c1.RecID, c1.State1, 
    case when c1.State2 IS NULL then 0 else DATEDIFF(day, c1.State1, c1.State2) end as [Days Between 1&2],
    case when c1.State3 IS NULL then 0 else DATEDIFF(day, coalesce(c1.State2,c1.State1), c1.State3) end as [Days Between 2&3],
    case when c1.State4 IS NULL then 0 else DATEDIFF(day, coalesce(c1.State3, c1.State2, c1.State1), c1.State4) end 
    as [Days Between 3&4],
    case when c1.State4 IS NULL then 0 else DATEDIFF(day, c1.State1, c1.State4) end as [Days Between 1 & 4]
    from cte c1 
    

    If you're using datetime, you may want to report time difference in seconds instead of days, for example. The idea still will be the same, you just use second as the first parameter in DATEDIFF function.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, June 25, 2010 5:47 PM
    Moderator
  • Table Name - InvoiceDeliveries
    InvoiceNumber   State     Datetime
    1                         0         6/25/2010 11:10 AM
    1                         1         6/25/2010 11:30 AM
    1                         2         6/25/2010 11:55 AM
    1                         3         6/25/2010 12:10 AM
    2                         0         6/25/2010 05:50 PM
    2                         2         6/25/2010 06:30 PM

    2                         3         6/25/2010 06:40 PM

    0-Preperation; 1-Waiting For Assignment;2-Leave For Delivery;3-Delived

    Report Should show as follows,

    Invoice Number   Preperation   Waiting For Assignment   Time with Driver

    1                                    20               25                                       15

    2                                    40                0                                        10


    test
    Friday, June 25, 2010 9:55 PM
  • DECLARE @InvoiceDeliveries TABLE

    (

    InvoiceNumber     INT

    ,State                  INT

    ,[Datetime]       DATETIME

    )

     

    INSERT INTO @InvoiceDeliveries

    SELECT  1,0,'6/25/2010 11:10 AM' UNION

    SELECT  1,1,'6/25/2010 11:30 AM' UNION

    SELECT  1,2,'6/25/2010 11:55 AM' UNION

    SELECT  1,3,'6/25/2010 12:10 PM' UNION

    SELECT  2,0,'6/25/2010 05:50 PM' UNION

    SELECT  2,2,'6/25/2010 06:30 PM' UNION

    SELECT  2,3,'6/25/2010 06:40 PM'

     

           

    SELECT X.InvoiceNumber

            ,DATEDIFF(MI,X.State0date,COALESCE (x.State1date,x.State2date,x.State3date))

            ,DATEDIFF(MI,X.State1date,COALESCE (x.State2date,x.State3date))

            ,DATEDIFF(MI,X.State2date,x.State3date)

      FROM

          (

          SELECT InvoiceNumber

                  ,State0date = MAX(CASE WHEN STATE =0 THEN [DateTime] END)

                  ,State1date = MAX(CASE WHEN STATE =1 THEN [DateTime] END)

                  ,State2date = MAX(CASE WHEN STATE =2 THEN [DateTime] END)

                  ,State3date = MAX(CASE WHEN STATE =3 THEN [DateTime] END)

            FROM @InvoiceDeliveries

            GROUP BY InvoiceNumber

          ) X

    • Marked as answer by Muniappan Monday, June 28, 2010 2:44 PM
    Saturday, June 26, 2010 6:07 AM
  • Thankyuo both for your answers. It is working. I am using the below SQL,

    SELECT

    (Convert(Varchar(3),Date1/60) + ':' + Convert(Varchar(3),Date1%60)) AS TimeInPreparation

    ,(Convert(Varchar(3),Date2/60) + ':' + Convert(Varchar(3),Date2%60)) AS TimeInAssignment

    ,(Convert(Varchar(3),Date3/60) + ':' + Convert(Varchar(3),Date3%60)) AS TimeToLeave

    ,(Convert(Varchar(3),Date4/60) + ':' + Convert(Varchar(3),Date4%60)) AS TimeWithDriver

    FROM

    (

    SELECT X.Invoice_Number

    ,DATEDIFF(s,X.State0date,COALESCE (x.State1date,x.State2date,x.State3date,x.State4date)) AS Date1

    ,DATEDIFF(s,COALESCE (x.State1date,x.State2date,x.State3date,x.State4date),COALESCE (x.State2date,x.State3date,x.State4date)) AS Date2

    ,DATEDIFF(s,COALESCE (x.State2date,x.State3date,x.State4date),COALESCE (x.State3date,x.State4date)) AS Date3

    ,DATEDIFF(s,COALESCE (x.State3date,x.State4date),x.State4date) AS Date4

    FROM

    (

    SELECT Invoice_Number

    ,State0date = MAX(CASE WHEN STATE =0 THEN [DateTime] END)

    ,State1date = MAX(CASE WHEN STATE =1 THEN [DateTime] END)

    ,State2date = MAX(CASE WHEN STATE =2 THEN [DateTime] END)

    ,State3date = MAX(CASE WHEN STATE =3 THEN [DateTime] END)

    ,State4date = MAX(CASE WHEN STATE =4 THEN [DateTime] END)

    FROM Invoice_StateChanges

    GROUP BY Invoice_Number

    ) X

    )Y


    test
    Monday, June 28, 2010 2:44 PM