SQLTeam.com | Weblogs | Forums

Last day of month data only

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

image

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

image

can this be done?

thank you

drop create sample data ..
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 

image

;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