Hi @Mehdi Djemame ,
Thanks for your update.
It is recommended for you to post more sample data and expected output if you have extra requirement.
I added some sample data as below:
insert into F_SALES values
('North','John','2020-12',30),
('North','Jack','2020-12',20),
('North','Jones','2019-11',10),
('North','John','2019-10',30),
('South','Jack','2020-09',20),
('South','Jones','2020-09',11),
('South','Jones','2019-08',12)
Please check whether below whether it is helpful to you.
Query A to calculate and store the Previous years YTD (yyyy_M12_YTD basically)
;with cte as (
select Region, year(cast(Month+'-01' as date)) [year],sales from F_SALES
where YEAR(cast(Month+'-01' as date))<YEAR(GETDATE()))
select Region,year,sum(sales) Sales_MTD
from cte
group by Region,year
Output:
Region year Sales_MTD
North 2019 40
South 2019 12
North 2020 50
South 2020 31
Query B to calculate and store the MTD on the current year + Append the yyyy_M12_YTD
;with cte as (
select Region, min(Month) min, max(Month) max from F_SALES
where YEAR(cast(Month+'-01' as date))=YEAR(GETDATE())
group by Region)
,cte1 as (
SELECT Region,CONVERT(date, min+'-01') dates,CONVERT(date,max+'-01') AS max from cte
UNION ALL
SELECT Region,DATEADD(MONTH, 1, dates),max
FROM cte1
WHERE CONVERT(date, dates) < CONVERT(date, max))
Select a.Region,
convert(varchar(7),a.dates) [Month],
Sum(isnull(Sales,0)) as Sales_MTD
From cte1 a
left join F_SALES b on a.Region=b.Region and convert(varchar(7),a.dates)=b.[Month]
group by a.Region, convert(varchar(7),a.dates)
order by a.Region,convert(varchar(7),a.dates)
Output:
Region Month Sales_MTD
North 2021-01 22
North 2021-02 15
North 2021-03 30
South 2021-01 20
South 2021-02 0
South 2021-03 25
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.