none
Calculate Difference In Times RRS feed

  • Question

  • Good day to you all.  I have a situation where I am not certain how to make the desired update.  I need to verify that the following criteria is met with date/time fields in my table.  Below is what I need to happen:

    if #draft.specialinstance = 1 THEN
    if #draft.startday is > #SI.startday then update #draft.startdaydiff with the difference
    if #draft.endday is < #SI.endday then update #draft.enddaydiff with the difference
    ELSE (#draft.specialinstance <> 1 THEN
    if #draft.startday > 08:00:00.000 then update #draft.startdaydiff with the difference
    if #draft.endday < 16:00:00.000 then update #draft.enddaydiff with the difference

    And this is DDL of both of the tables being used

    Create Table #draft
    (
    	name varchar(500)
    	,nameid int
    	,startday datetime
    	,endday datetime
    	,specialinstance int
    	,startdaydiff decimal(16,4)
    	,enddaydiff decimal(16,4)
    )
    
    Create Table #SI
    (
    	nameid int
    	,startday datetime
    	,endday dattime
    )
    
    Insert Into #draft Values
    ('ben folds five', '22', '01/01/2000 08:15:00.000', '01/01/2000 14:00:00.000', '0')
    ,('ben folds five', '22', '01/02/2000 08:00:00.000', '01/02/2000 16:00:00.000', '0')
    ,('ben folds five', '22', '01/03/2000 11:00:00.000', '01/03/2000 14:00:00.000', '1')
    ,('ben folds five', '22', '01/04/2000 12:00:00.000', '01/04/2000 18:00:00.000', '1')
    
    
    INSERT INTO #SI VALUES
    ('22', '01/03/2000 10:00:00.000', '01/03/2000 14:00:00.000')
    ,('22', '01/04/2000 11:00:00.000', '01/04/2000 18:00:00.000')


    My desired updates would be

    since #draft.startday = 08:15 I want to update #drat.Startdaydiff with Select DATEDIFF(SS,'01/01/2000 08:00:00.000','01/01/2000 08:15:00.000')
    since #draft.endday = 14:00 I want to update #draft.enddaydiff with Select DATEDIFF(ss, '01/01/2000 14:00:00.000', '01/01/2000 16:00:00.000')
    
    since #draft.startday = 11:00 (and not 10) then #draft.startdaydiff = Select DATEDIFF(ss, '01/03/2000 11:00:00.000', '01/03/2000 10:00:00.000')
    since #draft.startday = 12:00 (and not 11) then #draft.startdaydiff = Select DATEDIFF(ss, '01/04/2000 12:00:00.000', '01/04/2000 11:00:00.000')

    • Edited by WeFlyFree Wednesday, February 10, 2016 8:23 PM
    Wednesday, February 10, 2016 8:09 PM

Answers

  • Here is logic for one column, similar logic will apply for the other column:

    ;with cte as (select D.name, D.nameid, D.startday, D.endday, D.specialinstance, 
    SI.StartDay as SI_StartDay, SI.endDay as SI_EndDay, case when D.specialinstance = 1 
    then case when D.startday > SI.startday then datediff(second, SI.StartDay, D.StartDay) else D.startdaydiff end 
    else case when cast(D.startday as time) > '08:00:00' then datediff(second, '08:00:00', cast(D.StartDay as time)) else D.startdaydiff end 
    end as StartDayDiff  
     
    from #draft D LEFT JOIN #SI SI ON D.nameid = SI.nameid and cast(D.startday as date) = cast(SI.StartDay as date))
    
    select * from cte ;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by WeFlyFree Thursday, February 11, 2016 2:32 AM
    Wednesday, February 10, 2016 9:41 PM
    Moderator
  • Create Table #draft
    (
       name varchar(500)
       ,nameid int
       ,startday datetime
       ,endday datetime
       ,specialinstance int
       ,startdaydiff decimal(16,4)
       ,enddaydiff decimal(16,4)
    )

    Create Table #SI
    (
       nameid int
       ,startday datetime
       ,endday datetime
    )

    Insert Into #draft   (name, nameid, startday, endday, specialinstance) VALUES
    ('ben folds five', '22', '01/01/2000 08:15:00.000', '01/01/2000 14:00:00.000', '0')
    ,('ben folds five', '22', '01/02/2000 08:00:00.000', '01/02/2000 16:00:00.000', '0')
    ,('ben folds five', '22', '01/03/2000 11:00:00.000', '01/03/2000 14:00:00.000', '1')
    ,('ben folds five', '22', '01/04/2000 12:00:00.000', '01/04/2000 18:00:00.000', '1')

    INSERT INTO #SI VALUES
    ('22', '01/03/2000 10:00:00.000', '01/03/2000 14:00:00.000')
    ,('22', '01/04/2000 11:00:00.000', '01/04/2000 18:00:00.000')
    go
    UPDATE #draft
    SET    startdaydiff = datediff(ss,                              CASE d.specialinstance
                                      WHEN 0 THEN convert(time(0), '08:00')                                   ELSE convert(time(0), si.startday)
                                   END, convert(time(0), d.startday)),
           enddaydiff = datediff(ss, convert(time(0), d.endday),
                                 CASE d.specialinstance
                                      WHEN 0 THEN convert(time(0), '16:00')                                   ELSE convert(time(0), si.endday)
                                   END)
    FROM   #draft d
    LEFT   JOIN #SI si ON d.nameid = si.nameid
                      AND convert(date, d.startday) = convert(date, si.startday)
    go
    SELECT * FROM #draft
    SELECT * FROM #SI
    go
    DROP TABLE #draft, #SI


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 10, 2016 11:02 PM

All replies

  • What is your expected result from this sample?
    Wednesday, February 10, 2016 8:17 PM
    Moderator
  • What is your expected result from this sample?

    See my edit.  I want to update #draft.startdaydiff with  the difference in seconds from the

    #draft.startday and #si.startday

    and I want to update #draft.enddaydiff with the difference in seconds from 

    #dratf.edday and #si.endday

    Wednesday, February 10, 2016 8:24 PM
  • What is the relations between the tables ?

    There are several rows with the same values for nameid 22, so which one do we use in the conditions that your wrote?

    for example: if #draft.startday is > #SI.startday then update #draft.startdaydiff with the difference

    which #draft.startday should we compare to which #SI.startday ? what is the relations between the tables


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, February 10, 2016 8:58 PM
    Moderator
  • What is the relations between the tables ?

    There are several rows with the same values for nameid 22, so which one do we use in the conditions that your wrote?

    for example: if #draft.startday is > #SI.startday then update #draft.startdaydiff with the difference

    which #draft.startday should we compare to which #SI.startday ? what is the relations between the tables


    NameID is the common field.  You can join on that field.  The other factor would be to join on 

    Cast(#draft.startday As Date) = CAST(#si.startday As Date)

    Wednesday, February 10, 2016 9:06 PM
  • Can you post a tabular dataset for your result? A better way is to provide your table DDL (key information) to help us to understand the question.
    Wednesday, February 10, 2016 9:26 PM
    Moderator
  • Here is logic for one column, similar logic will apply for the other column:

    ;with cte as (select D.name, D.nameid, D.startday, D.endday, D.specialinstance, 
    SI.StartDay as SI_StartDay, SI.endDay as SI_EndDay, case when D.specialinstance = 1 
    then case when D.startday > SI.startday then datediff(second, SI.StartDay, D.StartDay) else D.startdaydiff end 
    else case when cast(D.startday as time) > '08:00:00' then datediff(second, '08:00:00', cast(D.StartDay as time)) else D.startdaydiff end 
    end as StartDayDiff  
     
    from #draft D LEFT JOIN #SI SI ON D.nameid = SI.nameid and cast(D.startday as date) = cast(SI.StartDay as date))
    
    select * from cte ;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by WeFlyFree Thursday, February 11, 2016 2:32 AM
    Wednesday, February 10, 2016 9:41 PM
    Moderator
  • This is what I was missing :-)
    The other factor would be to join on Cast(#draft.startday As Date) = CAST(#si.startday As Date)

    Please check If Naomi's response is the answer that you are looking for.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, February 10, 2016 10:52 PM
    Moderator
  • Create Table #draft
    (
       name varchar(500)
       ,nameid int
       ,startday datetime
       ,endday datetime
       ,specialinstance int
       ,startdaydiff decimal(16,4)
       ,enddaydiff decimal(16,4)
    )

    Create Table #SI
    (
       nameid int
       ,startday datetime
       ,endday datetime
    )

    Insert Into #draft   (name, nameid, startday, endday, specialinstance) VALUES
    ('ben folds five', '22', '01/01/2000 08:15:00.000', '01/01/2000 14:00:00.000', '0')
    ,('ben folds five', '22', '01/02/2000 08:00:00.000', '01/02/2000 16:00:00.000', '0')
    ,('ben folds five', '22', '01/03/2000 11:00:00.000', '01/03/2000 14:00:00.000', '1')
    ,('ben folds five', '22', '01/04/2000 12:00:00.000', '01/04/2000 18:00:00.000', '1')

    INSERT INTO #SI VALUES
    ('22', '01/03/2000 10:00:00.000', '01/03/2000 14:00:00.000')
    ,('22', '01/04/2000 11:00:00.000', '01/04/2000 18:00:00.000')
    go
    UPDATE #draft
    SET    startdaydiff = datediff(ss,                              CASE d.specialinstance
                                      WHEN 0 THEN convert(time(0), '08:00')                                   ELSE convert(time(0), si.startday)
                                   END, convert(time(0), d.startday)),
           enddaydiff = datediff(ss, convert(time(0), d.endday),
                                 CASE d.specialinstance
                                      WHEN 0 THEN convert(time(0), '16:00')                                   ELSE convert(time(0), si.endday)
                                   END)
    FROM   #draft d
    LEFT   JOIN #SI si ON d.nameid = si.nameid
                      AND convert(date, d.startday) = convert(date, si.startday)
    go
    SELECT * FROM #draft
    SELECT * FROM #SI
    go
    DROP TABLE #draft, #SI


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 10, 2016 11:02 PM