Home Forums SQL Server 2005 T-SQL (SS2K5) SUM(CASE(DATEDIFF Calculation Problem Involving a searched CASE Expression RE: SUM(CASE(DATEDIFF Calculation Problem Involving a searched CASE Expression

  • 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