CODEWITHSUNDEEP
oracle-database
user19110389
user19110389

Reputation:

Applying case statement on date

I am working on reports where we have activity start date(Date when the activity has begun) and activity end date(When the activity ended). I have a requirement wherein if the activity has begun before 2021 then I have to set the start date as 1/1/21 and if it continues after 2021 then set the activity end date as 31/12/21.And if the start date and end date lie in the same year keeping them as it is. How can I achieve this scenario.

Upvotes: 0

Views: 57

Answers (1)

MT0
MT0

Reputation: 167982

You do not need a CASE expression; you can use GREATEST and LEAST:

UPDATE table_name
SET    start_date = GREATEST(start_date, DATE '2021-01-01'),
       end_date   = LEAST(end_date, TIMESTAMP '2021-12-31 23:59:59')
WHERE  start_date < DATE '2022-01-01'
AND    (  start_date <  DATE '2021-01-01'
       OR end_date   >= DATE '2022-01-01');

If you want to just select the values where the date range overlaps 2021, and limit the range to be from 2021, then:

SELECT column1,
       column2,
       GREATEST(start_date, DATE '2021-01-01') AS start_date,
       LEAST(end_date, TIMESTAMP '2021-12-31 23:59:59') AS end_date
FROM   table_name
WHERE  start_date <  DATE '2022-01-01'
AND    end_date   >= DATE '2021-01-01'

If you want a generic query for any given year, starting from the :year_start bind variable, then:

SELECT column1,
       column2,
       GREATEST(start_date, :year_start) AS start_date,
       LEAST(end_date, ADD_MONTHS(:year_start, 12) - INTERVAL '1' SECOND) AS end_date
FROM   table_name
WHERE  start_date <  ADD_MONTHS(:year_start, 12)
AND    end_date   >= :year_start;

Upvotes: 1

Related Questions