Hi guys
hope you are all well
I am struggling to write a sql code on the table below, in a nutshell i just need to pick up the last date of the months data and show that only
e.g i have this table
and i am trying to write a code that will show the sales only of the last date of each month so the results should be as below
can this be done?
thank you
drop table #SampleData
create table #SampleData
(
[Date] date , Sales int
)
set dateformat dmy
insert into #SampleData values
('10/11/2020',30)
, ('21/11/2020',60)
, ('30/11/2020',30)
, ('20/12/2020',110)
, ('31/12/2020',130)
, ('27/01/2021',140)
, ('28/01/2021',195)
, ('29/01/2021',180)
; with cte as
(
select
convert(varchar(7), [Date], 126) as date
, ROW_NUMBER() over(partition by convert(varchar(7), [Date], 126) order by [date] desc ) as rn
, sales
from
#SampleData
)
select * from cte where rn = 1
;WITH cte_last_sale_of_month AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0)
ORDER BY date DESC) AS row_num
FROM #SampleData
)
SELECT
STUFF(RIGHT(CONVERT(varchar(30), date, 6), 6), 4, 1, '-') AS date,
sales
FROM cte_last_sale_of_month c
WHERE row_num = 1
ORDER BY c.date