SQL Check in which months an ID appears and show the results in a single row for each ID

Question

I have 3 month tables for January, February and March.

January table:

ID	Date
1	01/01/2022
2	01/02/2022
3	01/02/2022
1	01/10/2022
4	01/12/2022

February table:

ID	Date
1	02/01/2022
2	02/07/2022
3	02/10/2022
2	02/15/2022
5	02/17/2022

March table:

ID	Date
1	03/19/2022
2	03/11/2022
3	03/14/2022
3	03/25/2022
6	03/13/2022

Lets assume i make a UNION ALL between all three tables. Now taking March as reference point, i need to classify each ID according to the last date it appeared in the table (the same ID can be repeated in the table with the same or different date, the max date is to be used for the excercise). Now i need to check in which months the IDs appear and be able to sort them according to the month where the ID is present.

Months	ID	Last Date
Jan-Feb-Mar	1	03/19/12
Jan-Feb-Mar	2	03/11/12
Jan-Feb-Mar	3	03/14/12
Jan	4	01/12/2022
Feb	5	02/17/2022
Mar	6	03/13/2022

Also i would need to classify according to a range of days since the last date an ID appeared. The range of days are the following: 1-30, 31-60 and 61-90. It should look something like this table below, even better if the field Months from the previous example shown is added too

Range of Days	ID	Last Date	Months
1-30	1	03/19/12	Jan-Feb-Mar
1-30	2	03/11/12	Jan-Feb-Mar
1-30	3	03/14/12	Jan-Feb-Mar
61-90	4	01/12/2022	Jan
31-60	5	02/17/2022	Feb
1-30	6	03/13/2022	Mar

What have you tried so far? It's a good learning practice to share that as well. On the other hand, what does "Range of days" compute? The date difference between the last date and the current date? — Aleix CC, May 19 '22 at 07:11
I didn't add my current solution because I simplified the tables to get the point across in an easier way. Range of days is actually just a string '1-30', '31-60' or '61-90' but in order to get in one of those categories the last recorded date for an id has to be within the specified range. For example using March 31 as the reference date for evaluating the ranges: if an ID has a last recorded date of March 2 it is > than 03/31/2022 - 30 days and it should be classified as '1-30'. Now if the last recorded date is February 5 we would evaluate < than 03/31/2022 -31 days and > than 03/31/2022 -60 — Roberto Astudillo, May 19 '22 at 12:39
First try to attempt the complex problem yourself, then we can help you by troubleshooting your query and improve it further. In this way you'll get some learning from the process of attempting to solve it. — lemon, May 19 '22 at 12:52
So I understand that 'Range of Days' is computed based on the maximum 'Last Day'? — Aleix CC, May 19 '22 at 13:10

score 0 · Answer 1 · answered May 24 '22 at 20:56

Try this...

WITH 
    t_jan AS 
        (
            Select 1 "ID",  To_Date('01/01/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 2 "ID",  To_Date('01/02/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 3 "ID",  To_Date('01/02/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 1 "ID",  To_Date('01/10/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 4 "ID",  To_Date('01/12/2022', 'mm/dd/yyyy') "DT" From DUAL 
        ),
    t_feb AS
        (
            Select 1 "ID",  To_Date('02/01/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 2 "ID",  To_Date('02/07/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 3 "ID",  To_Date('02/10/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 2 "ID",  To_Date('02/15/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 5 "ID",  To_Date('02/17/2022', 'mm/dd/yyyy') "DT" From DUAL 
        ),
    t_mar AS
        (
            Select 1 "ID",  To_Date('03/19/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 2 "ID",  To_Date('03/11/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 3 "ID",  To_Date('03/14/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 3 "ID",  To_Date('03/25/2022', 'mm/dd/yyyy') "DT" From DUAL UNION ALL
            Select 6 "ID",  To_Date('03/13/2022', 'mm/dd/yyyy') "DT" From DUAL 
        ),
-- -- -- -- -- -- -- -- -- -- -- -- 
    mnths AS
        (
            Select 
                LISTAGG(MONTHS, '-') WITHIN GROUP (ORDER BY To_Char(DT, 'mm')) "MONTHS", 
                ID "ID", 
                Max(DT) "DT"
            From
                (
                    Select To_Char(Max(DT), 'Mon') "MONTHS", ID, Max(DT) "DT" From t_jan Group By ID UNION ALL
                    Select To_Char(Max(DT), 'Mon') "MONTHS", ID, Max(DT) "DT" From t_feb Group By ID UNION ALL
                    Select To_Char(Max(DT), 'Mon') "MONTHS", ID, Max(DT) "DT" From t_mar Group By ID 
                )
      Group By
          ID
        ),
    days AS
        (
            Select
                m.MONTHS "MONTHS",
                m.ID "ID",
                m.DT "DT",
                Max(LAST_DAY(m.DT)) OVER() "LAST_DT",
                Max(LAST_DAY(m.DT)) OVER() - m.DT "DAYS"
            From
                mnths m
        )
SELECT
    CASE 
        WHEN d.DAYS Between 1 And 30 THEN '1-30'
        WHEN d.DAYS Between 31 And 60 THEN '31-60'
        WHEN d.DAYS Between 61 And 90 THEN '61-90'
    ELSE
        '???'
    END "RANGE_OF_DAYS",
    d.ID "ID",
    d.DT "LAST_DATE",
    d.MONTHS "MONTHS"
FROM
    days d
ORDER BY 
    d.ID
--
-- Result
--
-- RANGE_OF_DAYS         ID LAST_DATE MONTHS
-- 1-30                   1 19-MAR-22 Jan-Feb-Mar
-- 1-30                   2 11-MAR-22 Jan-Feb-Mar
-- 1-30                   3 25-MAR-22 Jan-Feb-Mar
-- 61-90                  4 12-JAN-22 Jan
-- 31-60                  5 17-FEB-22 Feb
-- 1-30                   6 13-MAR-22 Mar

SQL Check in which months an ID appears and show the results in a single row for each ID

1 Answers1