Calculate Difference In Times

• 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')```

```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 Wednesday, February 10, 2016 8:23 PM
Wednesday, February 10, 2016 8:09 PM

• 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 Thursday, February 11, 2016 2:32 AM
Wednesday, February 10, 2016 9:41 PM
• 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
• 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

Wednesday, February 10, 2016 8:58 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

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
• 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 Thursday, February 11, 2016 2:32 AM
Wednesday, February 10, 2016 9:41 PM
• 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.

Wednesday, February 10, 2016 10:52 PM
• 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