Hello experts!
I thought this was going to work but it seems I am missing something here.
I have two tables specifically: see sample DDL
- Purchase table with purchase date (month-end), product, and value
- Derived table from the purchase table, called ‘Dates’, which holds purchase date (month-end), previous month-end, last three months, and fiscal year start date, and fiscal year end date
--purchase table
CREATE TABLE sample (
rpt_dt varchar(10),
product varchar(1),
value numeric(3)
);
INSERT INTO sample VALUES ('2022-10-31','A','90');
INSERT INTO sample VALUES ('2022-10-31','A','100');
INSERT INTO sample VALUES ('2022-10-31','B','90');
INSERT INTO sample VALUES ('2022-11-30','A','100');
INSERT INTO sample VALUES ('2022-11-30','B','70');
INSERT INTO sample VALUES ('2022-11-30','B','80');
INSERT INTO sample VALUES ('2022-12-31','A','100');
INSERT INTO sample VALUES ('2023-01-31','A','100');
INSERT INTO sample VALUES ('2023-01-31','B','100');
INSERT INTO sample VALUES ('2023-01-31','B','100');
INSERT INTO sample VALUES ('2023-02-28','A','60');
INSERT INTO sample VALUES ('2023-02-28','A','100');
INSERT INTO sample VALUES ('2023-02-28','B','100');
INSERT INTO sample VALUES ('2023-03-31','A','100');
INSERT INTO sample VALUES ('2023-03-31','B','100');
--dates table
CREATE TABLE dates (
rpt_dt varchar(10),
prev_mth varchar(10),
three_mths_ago varchar(10),
last_fy_start varchar(10),
last_fy_end varchar(10)
)
;
INSERT INTO dates VALUES (
INSERT INTO dates VALUES ('2022-10-31','2022-09-30','2022-07-31','2020-11-30','2021-10-31');
INSERT INTO dates VALUES ('2022-11-30','2022-10-31','2022-08-31','2021-11-30','2021-10-31');
INSERT INTO dates VALUES ('2022-12-31','2022-11-30','2022-09-30','2021-11-30','2021-10-31');
INSERT INTO dates VALUES ('2023-01-31','2022-12-31','2022-10-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-02-28','2023-01-31','2022-11-30','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-03-31','2023-02-28','2022-12-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-04-30','2023-03-31','2023-01-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-05-31','2023-04-30','2023-02-28','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-06-30','2023-05-31','2023-03-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-07-31','2023-06-30','2023-04-30','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-08-31','2023-07-31','2023-05-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-09-30','2023-08-31','2023-06-30','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-10-31','2023-09-30','2023-07-31','2021-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-11-30','2023-10-31','2023-08-31','2022-11-30','2022-10-31');
INSERT INTO dates VALUES ('2023-12-31','2023-11-30','2023-09-30','2022-11-30','2022-10-31');
INSERT INTO dates VALUES ('2024-01-31','2023-12-31','2023-10-31','2022-11-30','2023-10-31');
INSERT INTO dates VALUES ('2024-02-29','2024-01-31','2023-11-30','2022-11-30','2023-10-31');
INSERT INTO dates VALUES ('2024-03-31','2024-02-29','2023-12-31','2022-11-30','2023-10-31');
General requirements:
query example: this produces no results which I thought it would?! lol…given that I am choosing the previous month and the two tables have the exact same rpt_dt.
with test as (
select rpt_dt, product, sum(values) from sample group by rpt_dt, product)
, dates as (
select * from dates
)
, prev_mth as (
select *
from test a
where a.rpt_dt = (select prev_mth from dates b where b.rpt_dt = a.rpt_dt)
)
select * from prev_mth
;
So, I must be doing something wrong here! If someone can advise, that would be helpful. In the query above, I didn't even do the sum and grouping as I couldn't even get any results returned, but, if someone advises why the join is incorrect, I can finish the rest of!
I want an output that basically shows the rpt_dt, product, sum value per month, sum value prev mth, sum value 3 months ago.
Thanks in advance for the advice!
Humbly yours, John
Here is what the output I'm trying to get:
