CASE statement

  • I don't get that error message with the sample data you supplied. If your test data has more than one date within the range, you have to do some sort of aggregation like SUM() or MAX().

    Think about what you are doing for a moment. You have a GROUP BY query, and one of the values you are returning from your case statement is a MAX().

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Sample data :

    CREATE TABLE #Table1

    (

    ID1 UNIQUEIDENTIFIER,

    ID2 UNIQUEIDENTIFIER,

    TimeOfDay DATETIME,

    Cumulative DECIMAL (38,6)

    )

    INSERT INTO #Table1 (ID1,ID2,TimeOfDay,Cumulative )

    SELECT '14F10A98-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-06 00:00:00.000',32.263052 UNION

    SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-07 00:00:00.000',38.263052 UNION

    SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-08 00:00:00.000',39.263052 UNION

    SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-09 00:00:00.000',40.328815 UNION

    SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-08-08 00:00:00.000',44.328815 UNION

    SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13 00:00:00.000',22.400000 UNION

    SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-14 00:00:00.000',28.400000 UNION

    SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-08-08 00:00:00.000',18.400000 UNION

    SELECT '700B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-03 00:00:00.000',28.400000 UNION

    SELECT '710B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-04 00:00:00.000',29.400000 UNION

    SELECT '711B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-05 00:00:00.000',30.400000 UNION

    SELECT '712B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-06 00:00:00.000',27.400000 UNION

    SELECT '713B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-07 00:00:00.000',42.400000 UNION

    SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-08 00:00:00.000',76.400000 UNION

    SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-09 00:00:00.000',86.400000

    CREATE TABLE #Table2

    (

    ID1 UNIQUEIDENTIFIER,

    StartDate DATETIME,

    EndDate DATETIME,

    )

    INSERT INTO #Table2 ( ID1, StartDate, EndDate )

    SELECT '4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-03 08:00:00.000','2016-05-10 17:00:00.000' UNION

    SELECT '17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13 08:00:00.000','2016-09-14 17:00:00.000' UNION

    SELECT 'AD556826-4444-E611-8426-80000B9AEB87','2016-08-03 08:00:00.000','2016-08-09 17:00:00.000'

    SELECT * FROM #Table1 ORDER BY ID2,Cumulative

    SELECT * FROM #Table2

    SELECT CAST(GETDATE() AS DATE)

    SELECT

    T.ID1,CAST(T.[StartDate] AS DATE) TaskStartDate,--CAST([EndDate] AS DATE) TaskEndDate,CAST(GETDATE() AS DATE) Today, CAST(GETDATE() AS DATE),

    CASE WHEN DATEDIFF(DAY, getdate(), T.[StartDate]) > 0 THEN 0

    WHEN DATEDIFF(DAY, getdate(), T.[EndDate])< 0 THEN MAX(ROUND(A.Cumulative ,3))

    WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([T].[StartDate] AS DATE) AND CAST(T.[EndDate] AS DATE) THEN (SELECT A.Cumulative FROM #Table1 A WHERE CAST(A.TimeOfDay AS DATE) = CAST(GETDATE() AS DATE) )

    END AS Cumulative_Original

    FROM #Table1 A JOIN #Table2 T ON A.ID2 = T.ID1

    -- WHERE T.ID1 = 'AD556826-4444-E611-8426-80000B9AEB87'

    GROUP BY T.ID1,T.[StartDate],[EndDate]

    DROP TABLE #Table1

    DROP TABLE #Table2

    -- IF from #Table1 , enddate is in the past , then max(Cumulative)

    -- IF from #Table1 , startdate is in the future , then 0 as Cumulative

    -- IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate()

    --Desired RESULT

    SELECT 'AD556826-4444-E611-8426-80000B9AEB87','2016-08-03' AS TaskStartDate,76.400000 AS Cumulative_Original

    SELECT '17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13' AS TaskStartDate,0.000000 AS Cumulative_Original

    SELECT '4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-03' AS TaskStartDate,44.329000 AS Cumulative_Original

  • PSB: you've been here long enough to be aware of the code="sql" tags. Is there a reason why you choose not to use them, to help others read your posts?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Try this:

    Select

    t2.ID1,

    t2.startdate as taskstartdate,

    case

    when t2.enddate < getdate() then max(t1.Cumulative)

    when t2.StartDate > getdate() then 0

    when getdate() < t2.enddate and getdate() > t2.StartDate

    then (Select max(t3.Cumulative) from #Table1 as t3

    where cast(t3.TimeOfDay as date) =cast(getdate() as date))

    End AS cumulative_original

    from #table1 as t1

    inner join #Table2 as t2

    on t1.ID2=t2.ID1

    group by t2.ID1,

    t2.StartDate,

    t2.endDate

  • SELECT T.ID1,

    CAST(T.[StartDate] AS DATE) TaskStartDate,

    MAX(CASE WHEN DATEDIFF(DAY, getdate(), T.[StartDate]) > 0 THEN ROUND(0 ,3)

    WHEN DATEDIFF(DAY, getdate(), T.[EndDate])< 0 THEN ROUND(A.Cumulative ,3)

    WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([T].[StartDate] AS DATE)

    AND CAST(T.[EndDate] AS DATE)

    AND CAST(A.TimeOfDay AS DATE) = CAST(GETDATE() AS DATE) THEN A.Cumulative

    END) AS Cumulative_Original

    FROM #Table1 A

    JOIN #Table2 T ON A.ID2 = T.ID1

    GROUP BY T.ID1,T.[StartDate],[EndDate]

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply