CASE statement

  • Hi,

    -- 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()

    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',66.400000 UNION

    SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-09 00:00:00.000',76.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

    SELECT * FROM #Table2

    --Worked on the query, but was not able to get result for the last condition

    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

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

    DROP TABLE #Table1

    DROP TABLE #Table2

    Please help.

    Thanks,

    PSB

  • PSB (8/5/2016)


    --

    Please help.

    Thanks,

    PSB

    What is your question?

    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

  • Need help when getdate() between startdate and enddate , then the query should use (Cumulative) value where TimeOfDay = Getdate()

    Thanks,

    PSB

  • It should pick 30.400000 for TimeOfDay 2016-08-05 00:00:00.000 .

  • deleted

    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

  • Can anyone help me with the last condition ?

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

  • PSB (8/5/2016)


    Can anyone help me with the last condition ?

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

    TimeOfDay looks like a date. GetDate() is a datetime. They will therefore rarely be equal.

    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

  • I have used cast as date in both

  • Could you please at least give an example of what the result should be?

    Don't explain it in words, draw us a picture.

    Create a result table and populate it with the results you want to see from the input data in your example.

    It would be a big help. Thank you .

    __________________________________________________

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

  • 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

    Thanks,

    PSB

  • Here's how the line in question should read.

    WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([StartDate] AS DATE) AND CAST([EndDate] AS DATE)

    The key is that [StartDate] comes before [EndDate].

    BETWEEN requires that the earlier data come first.

    BETWEEN [EndDate] and [Startdate] can only be true when both dates are the same.

    __________________________________________________

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

  • Try this for table1 and table2 :

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

  • Getting a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." using

    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) )

    Please help

    Thanks,

    PSB

  • Are you trying to sum up the amount of all rows between a range? If so, you need to use the SUM() function in your subquery.

    __________________________________________________

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

  • Subquery value should be based on Id2 value from #Table1 or Id1 from #Table2

    IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate() where A.ID2 = T.ID1

    When getdate() is in between start and end dates , then for that particular T.Id2 from #Table2 ,I need to get the Cumulative value where GEtDate() and TimeOfDay are equal . Other Id2 from #Table1 will also have TimeOfDay = GetDAte() or greater , but there should be only one value per ID2 level.

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

    Thanks

    PSB

Viewing 15 posts - 1 through 15 (of 19 total)

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