SUM(CASE(DATEDIFF Calculation Problem Involving a searched CASE Expression

  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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