Add DateTime Based on Duration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add DateTime Based on Duration

Author

Topic

sisterwolf
Starting Member

4 Posts

Posted - 2013-06-05 : 16:36:28

The database that I'm querying doesn't capture the DateTimes of the daily operations only the report. Based on the Daily Report DateTime, the duration of the operations and their sequence, what's the code to create the "Ops Code Start DateTime"?

Report Date Ops Code Duration(hrs) Seq#
2013-04-05 06:00:00.000 MIRU 8.5 1
2013-04-05 06:00:00.000 DR 4.5 2
2013-04-05 06:00:00.000 WOD 11.0 3

2013-04-11 06:00:00.000 TOH 1.5 1
2013-04-11 06:00:00.000 TIH 2.0 2
2013-04-11 06:00:00.000 DR 6.0 3
2013-04-11 06:00:00.000 CIRC 0.5 4
2013-04-11 06:00:00.000 INACT 14.0 5

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 17:20:09

quote:
Originally posted by sisterwolf

The database that I'm querying doesn't capture the DateTimes of the daily operations only the report. Based on the Daily Report DateTime, the duration of the operations and their sequence, what's the code to create the "Ops Code Start DateTime"?

Report Date Ops Code Duration(hrs) Seq#
2013-04-05 06:00:00.000 MIRU 8.5 1
2013-04-05 06:00:00.000 DR 4.5 2
2013-04-05 06:00:00.000 WOD 11.0 3

2013-04-11 06:00:00.000 TOH 1.5 1
2013-04-11 06:00:00.000 TIH 2.0 2
2013-04-11 06:00:00.000 DR 6.0 3
2013-04-11 06:00:00.000 CIRC 0.5 4
2013-04-11 06:00:00.000 INACT 14.0 5

I don't know if there is sufficient data to be able to capture the "Ops Code Start DateTime" For example, if OpsCode = DR on 2013-04-05 started at 1:00 AM (and ended at 5:30 AM after 4.5 hours), you would not be able to get the 1:00 AM timestamp unless there was some other piece of information.

Now, if all of these operations are ongoing operations at the time the report is generated, then you could subtract the duration to get the start time. Is that the case?

sisterwolf
Starting Member

4 Posts

Posted - 2013-06-06 : 08:41:24

As I was starting to write more detail on my problem, I had a deja vue moment. I'd written the same description before. So, I again scrolled through my code files and finally found it. I'd asked this question 6 months ago. Below is the answer for those wanting to know. I'd gotten two different ways to calculate the DateTime.

SELECT
job.idrec AS JobID,
jobrpt.idrec AS RptID,
jobrpt.dttmstart AS RptStartDate,
CASE sysseq WHEN 1 THEN jobrpt.dttmstart
ElSE (SELECT
DATEADD(MI,SUM(timelog2.duration * 1440),MAX(jobrpt2.dttmstart))
FROM
wv90.dbo.wvjobreport AS jobrpt2
JOIN wv90.dbo.wvjobreporttimelog AS timelog2
ON jobrpt2.idrec = timelog2.idrecparent
and jobrpt2.idrec = jobrpt.idrec
WHERE
timelog2.sysseq < timelog.sysseq
and jobrpt2.idrec = jobrpt.idrec
GROUP BY jobrpt2.idrec
)
END AS OpsStartDate,
timelog.sysseq AS LogSeq#,
timelog.code1 AS OpsCode,
timelog.code2 AS OpsDetail,
ROUND(timelog.duration * 24,1) AS HrsDur
FROM
wv90.dbo.wvjob As job
INNER JOIN wv90.dbo.wvjobreport AS jobrpt
ON job.idrec = jobrpt.idrecparent
INNER JOIN wv90.dbo.wvjobreporttimelog AS timelog
ON jobrpt.idrec = timelog.idrecparent
AND jobrpt.idwell = timelog.idwell
ORDER BY
jobrpt.dttmstart, timelog.sysseq

-----------------------------------------------------------------------------------------------
-- *** Using Cross Apply **

SELECT
job.idrec AS JobID,
jobrpt.idrec AS RptID,
jobrpt.dttmstart AS RptStartDate,
ISNULL(DateAdd(MI,SUM(S.HrsDur * 1440),jobrpt.dttmstart),jobrpt.dttmstart) AS OpsStartDttm,
ROUND(timelog.duration*24,1) AS HrsDur,
timelog.code1 AS OpsCode,
timelog.sysseq AS LogSeq#
FROM
wv90.dbo.wvjob AS job
INNER JOIN wv90.dbo.wvjobreport AS jobrpt
ON job.idrec = jobrpt.idrecparent
INNER JOIN wv90.dbo.wvjobreporttimelog AS timelog
ON jobrpt.idrec = timelog.idrecparent
OUTER APPLY (Select
tlog2.idrecparent,
SUM(tlog2.duration) AS HrsDur
FROM
wv90.dbo.wvjobreporttimelog AS tlog2
LEFT JOIN wv90.dbo.wvjobreporttimelog AS tlog3
ON tlog3.idrecparent = tlog2.idrecparent
AND tlog3.sysseq = tlog2.sysseq + 1
WHERE
tlog2.idrecparent = timelog.idrecparent
AND tlog3.sysseq <= timelog.sysseq
GROUP BY
tlog2.idrecparent
)S
GROUP BY
job.idrec,
jobrpt.idrec,
jobrpt.dttmstart,
ROUND(timelog.duration*24,1),
timelog.code1,
timelog.sysseq
ORDER BY
jobrpt.dttmstart, timelog.sysseq

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources