CODEWITHSUNDEEP
sqloracle-database
Playlist
Playlist

Reputation: 37

Fill missing months in a SELECT query

I'm trying to fill missing months in a SELECT query. It looks like this :

SELECT sl.loonperiode_dt, (sum(slr.uren)) code_220
FROM HR.soc_loonbrief_regels slr,
             HR.soc_loonbrieven sl,
             HR.werknemers w,
             HR.v_kontrakten vk 
WHERE sl.loonperiode_dt BETWEEN '01012018' AND '01122018'
         AND slr.loon_code_id IN (394)
         AND slr.loonbrief_id = sl.loonbrief_id
         AND w.werknemer_id   = sl.werknemer_id
         AND w.werknemer_id   = vk.werknemer_id
         AND vk.functie_id    IN (121, 122, 128)
         AND sl.loonperiode_dt BETWEEN hist_start_dt AND last_day(nvl(hist_eind_dt, sl.loonperiode_dt))
         AND w.afdeling_id  like '961'
GROUP BY sl.loonperiode_dt
ORDER BY sl.loonperiode_dt

It outputs this table :

31/01/18    234
30/04/18    245,8
31/05/18    714,6
31/07/18    288,04
31/08/18    281
30/11/18    515,12

I obviously would like it to be like that :

31/01/18    234
28/02/18    0
31/03/18    0
30/04/18    245,8
31/05/18    714,6
30/06/18    0
31/07/18    288,04
31/08/18    281
30/09/18    0
31/10/18    0
30/11/18    515,12
31/12/18    0

I have a calendar table 'CONV_HC.calendar' with dates in a column named 'DAT'. I have seen many questions and answers about this, but I can't figure out how to apply the LEFT JOIN method or any other one to my current problem.

Thanks a lot in advance,

Upvotes: 0

Views: 1118

Answers (1)

P. Waksman
P. Waksman

Reputation: 1009

You could have a already done table with months and "join" with it, group by the date, or you can create one with subquery or using a with statement, something like

WITH Months (month) AS (
  SELECT 1 AS Month FROM DUAL

  UNION ALL

  SELECT MONTH + 1
  FROM Months
  WHERE MONTH < 12
)
SELECT *
FROM Months

    LEFT JOIN SomeTable
    ON SomeTable.month = Months.MONTH
    --ON Extract(MONTH FROM SomeTable.date) = Months.MONTH

edit

A better example:

--Just to simulate some table data
WITH SomeData AS (
  SELECT TO_DATE('01/01/2019', 'MM/DD/YYYY') AS Dat, 5 AS Value FROM dual
  UNION ALL
  SELECT TO_DATE('01/05/2019', 'MM/DD/YYYY') AS Dat, 7 AS Value FROM dual
  UNION ALL
  SELECT TO_DATE('03/03/2019', 'MM/DD/YYYY') AS Dat, 2 AS Value FROM dual
  UNION ALL
  SELECT TO_DATE('11/05/2019', 'MM/DD/YYYY') AS Dat, 9 AS Value FROM dual
)
, Months (StartDate, MaxYear) AS (
  SELECT CAST(TO_DATE('01/01/2019', 'MM/DD/YYYY') AS DATE) AS StartDate, 2019 AS MaxYear FROM DUAL

  UNION ALL

  SELECT CAST(ADD_MONTHS(StartDate, 1) AS DATE), MaxYear
  FROM Months
  WHERE EXTRACT(YEAR FROM ADD_MONTHS(StartDate, 1)) <= MaxYear 
)
SELECT
    Months.StartDate AS Dat
    , SUM(SomeData.Value) AS SumValue 
FROM Months

    LEFT JOIN SomeData
    ON Extract(MONTH FROM SomeData.Dat) = Extract(MONTH FROM Months.StartDate)

GROUP BY 
    Months.StartDate

edit

You won't find a just copy past solution, you need to get the idea from it and change to your context.

let's try this. You can "add" the missing months in an APP, or you can JOIN it with a already done table, doesn't need to be a real table, you can make one. The with statement is an example of it. So lets get all month, at the last day for 2019:

--Geting the last day of every month for 2019
WITH Months (CurrentMonth, MaxYear) AS (
  SELECT CAST(TO_DATE('01/01/2019', 'MM/DD/YYYY') AS DATE) AS CurrentMonth, 2019 AS MaxYear FROM DUAL

  UNION ALL

  SELECT CAST(ADD_MONTHS(CurrentMonth, 1) AS DATE), MaxYear
  FROM Months
  WHERE EXTRACT(YEAR FROM ADD_MONTHS(CurrentMonth, 1)) <= MaxYear  
)
SELECT LAST_DAY(Months.CurrentMonth) AS LastDay
FROM Months

Ok, now we have all months avaliable for the join. In your query, you already have the sum done so lets skip the sum and just use your data. Just add another with query.

--Geting the last day of every month for 2018
WITH Months (CurrentMonth, MaxYear) AS (
  SELECT CAST(TO_DATE('01/01/2018', 'MM/DD/YYYY') AS DATE) AS CurrentMonth, 2018 AS MaxYear FROM DUAL

  UNION ALL

  SELECT CAST(ADD_MONTHS(CurrentMonth, 1) AS DATE), MaxYear
  FROM Months
  WHERE EXTRACT(YEAR FROM ADD_MONTHS(CurrentMonth, 1)) <= MaxYear  
)
, YourData as (
    SELECT sl.loonperiode_dt, (sum(slr.uren)) code_220
    FROM HR.soc_loonbrief_regels slr,
                 HR.soc_loonbrieven sl,
                 HR.werknemers w,
                 HR.v_kontrakten vk 
    WHERE sl.loonperiode_dt BETWEEN '01012018' AND '01122018'
             AND slr.loon_code_id IN (394)
             AND slr.loonbrief_id = sl.loonbrief_id
             AND w.werknemer_id   = sl.werknemer_id
             AND w.werknemer_id   = vk.werknemer_id
             AND vk.functie_id    IN (121, 122, 128)
             AND sl.loonperiode_dt BETWEEN hist_start_dt AND last_day(nvl(hist_eind_dt, sl.loonperiode_dt))
             AND w.afdeling_id  like '961'
    GROUP BY sl.loonperiode_dt
    --ORDER BY sl.loonperiode_dt
)
SELECT
    LAST_DAY(Months.CurrentMonth) AS LastDay
    , COALESCE(YourData.code_220, 0) AS code_220
FROM Months

    Left Join YourData
    on Extract(MONTH FROM Months.CurrentMonth) = Extract(MONTH FROM YourData.loonperiode_dt)
    --If you have more years: AND Extract(YEAR FROM Months.CurrentMonth) = Extract(YEAR FROM YourData.loonperiode_dt)

ORDER BY LastDay ASC

Upvotes: 1

Related Questions