CASE statement

• PSB

  SSChampion

  Points: 13195

  More actions

  August 5, 2016 at 10:12 am

  #312286

  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

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  August 5, 2016 at 10:22 am

  #1894090

  PSB (8/5/2016)

  ---

  --

  Please help.

  Thanks,

  PSB

  What is your question?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• PSB

  SSChampion

  Points: 13195

  More actions

  August 5, 2016 at 10:48 am

  #1894100

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

  Thanks,

  PSB

• PSB

  SSChampion

  Points: 13195

  More actions

  August 5, 2016 at 10:56 am

  #1894106

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

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  August 5, 2016 at 11:41 am

  #1894120

  deleted

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• PSB

  SSChampion

  Points: 13195

  More actions

  August 5, 2016 at 11:45 am

  #1894122

  Can anyone help me with the last condition ?

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

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  August 5, 2016 at 11:51 am

  #1894123

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• PSB

  SSChampion

  Points: 13195

  More actions

  August 5, 2016 at 12:15 pm

  #1894134

  I have used cast as date in both

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  August 6, 2016 at 8:26 am

  #1894186

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 7, 2016 at 3:57 pm

  #1894253

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  August 7, 2016 at 7:54 pm

  #1894257

  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

• Ombir

  SSC-Addicted

  Points: 466

  More actions

  August 8, 2016 at 2:58 am

  #1894280

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 8, 2016 at 5:42 am

  #1894298

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  August 8, 2016 at 5:46 am

  #1894299

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 8, 2016 at 5:55 am

  #1894304

  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