January 27, 2015 at 2:07 pm
I have a searched CASE expression that SUM()s two different TIMESTAMP dates ( DATEDIFF() ) and returns number of days per row. I have looked online for similar SUM(CASE(DATEDIFF calculation solutions without success. What is the correct way to rewrite my particular Case statement?
Thanks,
Mindy
SELECT
well.wellide AS Prospect,
well.wellidc AS Pad,
well.wellname AS Well,
job.idrec AS JobID,
CASE WHEN rig.rigno IS NULL THEN rig.contractor
ELSE (rig.contractor + ' '+ rig.rigno)
END AS Rig,
CASE WHEN rig.dttmstart < '01/01/2014' THEN
SUM(CAST(CAST(DATEDIFF(MI, '01/01/2014', rig.dttmend) AS DECIMAL(12,2))/1440 AS DECIMAL(12,2)))
WHEN rig.dttmend > '01/01/2015' THEN
SUM(CAST(CAST(DATEDIFF(MI, rig.dttmstart, '01/01/2015') AS DECIMAL(12,2))/1440 AS DECIMAL(12,2)))
ELSE SUM(CAST(CAST(DATEDIFF(MI, rig.dttmstart, rig.dttmend) AS DECIMAL(12,2))/1440 AS DECIMAL(12,2)))
END AS DaysDuration
FROM
wv90.dbo.wvwellheader AS well
INNER JOIN wv90.dbo.wvjob AS job
ON well.idwell = job.idwell
LEFT JOIN wv90.dbo.wvjobrig AS rig
ON job.idrec = rig.idrecparent
GROUP BY
well.wellide,
well.wellidc,
well.wellname,
job.idrec,
CASE WHEN rig.rigno IS NULL THEN rig.contractor
ELSE (rig.contractor + ' '+ rig.rigno)
END
January 27, 2015 at 2:19 pm
Would this work for you?
SELECT
well.wellide AS Prospect,
well.wellidc AS Pad,
well.wellname AS Well,
job.idrec AS JobID,
rig.contractor + ISNULL(' '+ rig.rigno, '') AS Rig,
SUM(CASE WHEN rig.dttmstart < '01/01/2014' THEN
CAST(CAST(DATEDIFF(MI, '01/01/2014', rig.dttmend) AS DECIMAL(12,2))/1440 AS DECIMAL(12,2))
WHEN rig.dttmend > '01/01/2015' THEN
CAST(CAST(DATEDIFF(MI, rig.dttmstart, '01/01/2015') AS DECIMAL(12,2))/1440 AS DECIMAL(12,2))
ELSE CAST(CAST(DATEDIFF(MI, rig.dttmstart, rig.dttmend) AS DECIMAL(12,2))/1440 AS DECIMAL(12,2))
END) AS DaysDuration
FROM
wv90.dbo.wvwellheader AS well
INNER JOIN wv90.dbo.wvjob AS job
ON well.idwell = job.idwell
LEFT JOIN wv90.dbo.wvjobrig AS rig
ON job.idrec = rig.idrecparent
GROUP BY
well.wellide,
well.wellidc,
well.wellname,
job.idrec,
rig.contractor + ISNULL(' '+ rig.rigno, '')
I changed something else just for fun. I hope that you like that alternative.
January 27, 2015 at 8:26 pm
The code works and the alternative is a lot cleaner.
Thanks a lot,
Mindy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply