CODEWITHSUNDEEP
sqloracle-database
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Assign year to months of past 6 months

From below data, how do i assign year in column DS_YEAR where Year is not assigned based on DS_MONTH.

DS_MONTH|   DS_DAY| DS_YEAR
--------|---------|--------
Mar     |   2     | 2019
Jan     |   4     | 2020
Apr     |   2     | 07:43
Sep     |   1     | 06:00
Jul     |   2     | 05:00
Dec     |   4     | 2019
Feb     |   7     | 2020
Nov     |   9     | 2019

From the above data; any data that is between now and past 6 months has a TIME instead of YEAR. However i want to write a query to attach the respective year instead of time.

I have below query which will attach the current year, however in case our data will lie in the months transitioning previous year it wont be accurate.

SELECT
    DS_MONTH || '-' || DS_DAY || '-' ||
    CASE
        WHEN DS_YEAR LIKE '%:%' THEN TO_CHAR(sysdate, 'YYYY')
        ELSE DS_YEAR
    END dsf
FROM
    MY_TABLE

How do i check the month whether it lies in current year or previous year, so that i can assign the correct year?

Example: if today was FEBRUARY. and in my data i have Sep | 1 | 06:00 then my query should return Sep-1-2019 and if today was OCTOBER it should return Sep-1-2020

Upvotes: 0

Views: 53

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18650

Is this what you want ?

WITH sampledata (mon, d, yt) AS
(
SELECT 'Mar','2','2019' FROM DUAL UNION
SELECT 'Jan','4','2020' FROM DUAL UNION
SELECT 'Apr','2','07:43' FROM DUAL UNION
SELECT 'Sep','1','06:00' FROM DUAL UNION
SELECT 'Jul','2','05:00' FROM DUAL UNION
SELECT 'Dec','4','2019' FROM DUAL UNION
SELECT 'Feb','7','2020' FROM DUAL UNION
SELECT 'Nov','9','2019' FROM DUAL
),rundate (dt) AS
(
SELECT DATE'2021-05-30' FROM DUAL
)
SELECT s.mon, s.d, s.yt, 
       CASE WHEN TO_DATE(s.mon||'-'||s.d||'-'|| extract(year from r.dt),'Mon-dd-YYYY') > r.dt THEN TO_DATE(s.mon||'-'||s.d||'-'|| extract(year from ADD_MONTHS(r.dt,-12)),'Mon-dd-YYYY')
            ELSE TO_DATE(s.mon||'-'||s.d||'-'|| extract(year from r.dt),'Mon-dd-YYYY')
       END
  FROM sampledata s CROSS JOIN rundate r
 WHERE INSTR(s.yt,':') > 0
UNION
SELECT s.mon, s.d, s.yt, TO_DATE(s.mon||'-'||s.d||'-'|| s.yt,'Mon-dd-YYYY') 
  FROM sampledata s
 WHERE INSTR(s.yt,':') = 0;

UPDATE: Added the 'rundate' CTE so you can test with any date, not just sysdate. Also added the case expression to check ensure the date is in the past.

Upvotes: 1

Sushant
Sushant

Reputation: 180

This is just pseudo code, may require few changes in date/month conversion as per db syntax

SELECT
    DS_MONTH || '-' || DS_DAY || '-' ||
    CASE
        WHEN DS_YEAR LIKE '%:%' and Month(sysdate)>6 THEN TO_CHAR(sysdate, 'YYYY')
        WHEN DS_YEAR LIKE '%:%' and Month(cast(DS_MONTH+'1 2015' as datetime)) < 6 THEN TO_CHAR(sysdate, 'YYYY')
        WHEN DS_YEAR LIKE '%:%' and Month(cast(DS_MONTH+'1 2015' as datetime)) > 6 THEN TO_CHAR(DATEADD(YEAR, -1, GETDATE()), 'YYYY')
        ELSE DS_YEAR
    END dsf
FROM
    MY_TABLE

Upvotes: 0

Related Questions