Hi All,
I have a table where we load data on daily snapshot.
Now the ask is
- It should contain only past 12 months data at any point
- It should hold Month End Dates and Most recent I mean if month has not ended as in this case June then today it will hold 20230331,20230430, 20230531 , 20230603 post tomorrow's load 20230603 should be deleted and 20230604 should come and data in table should be of 20230331,20230430, 20230531 , 20230604.
I do not have Oracle 9version 19c) on my local system and we are not allowed to post actual data from Company network hence prepared a dummy set , sincere apologies in advance if there is some bug.
Below Code has data for dates -20230430,20230501,20230502,20230530,20230531,20230603.
- So 20230501,20230502,… and so on should be deleted if Month has ended as we do have 20230531 for May no daily dates for May should be available
- As June has not ended yet it will have most recent daily as of now 20230603 but after today's load 20230603 should be Deleted and only 20230604 for June should be there.
- At any point there should be only past 12 months data June 2023- 12 months
With test_Table AS
(
Select 20230430 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230430 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230430 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230430 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230430 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230501 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230501 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230501 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230501 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230501 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230502 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230502 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230502 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230502 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230502 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230530 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230530 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230530 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230530 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230530 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230531 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230531 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230531 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230531 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230531 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230601 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230601 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230601 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230601 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230601 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230602 AS SNP_DT_KEY,1 AS ID ,12 AS region_id,23 AS tot_sales ,45 AS tot_cost ,-22 AS tot_profit from dual UNION ALL
Select 20230602 AS SNP_DT_KEY,2 AS ID ,12 AS region_id,61 AS tot_sales ,44 AS tot_cost ,17 AS tot_profit from dual UNION ALL
Select 20230602 AS SNP_DT_KEY,3 AS ID ,13 AS region_id,42 AS tot_sales ,33 AS tot_cost ,9 AS tot_profit from dual UNION ALL
Select 20230602 AS SNP_DT_KEY,4 AS ID ,14 AS region_id,56 AS tot_sales ,22 AS tot_cost ,34 AS tot_profit from dual UNION ALL
Select 20230602 AS SNP_DT_KEY,5 AS ID ,15 AS region_id,45 AS tot_sales ,11 AS tot_cost ,34 AS tot_profit from dual
)
Select * from test_table;