Sql code sales value last day of every month

Transact-SQL
1

Hi guys

hope you are all well

I am struggling to write a sql code which will pick up the sales value of the last date of each month.

I have this below example table showing sales which accumulate daily for each month, the late day of the month is the final sales value ( this is just example table in reality there are much more months)

from the table I need to write a query ( select query etc) that will show the below

The past sales is the sales number of the last day of the previous month ( for this case we start at November so the past sales for November is blank, for December past sales is the value you see for 31/11/2020 ) current sales is the last day of the month in question.

Can this be done? can anyone help me do this?

thank you

2

hi hope this helps

drop create sample data .. click arrow to the left 
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',75) 
, ('20/12/2020',110) 
, ('31/12/2020',130) 
, ('27/01/2021',140) 
, ('28/01/2021',195) 
, ('29/01/2021',250)

; with cte as 
(
   select 
            convert(varchar(7), [Date], 126) as date
		 ,  max(Sales) as currentsales 
   from 
        #SampleData  
  group by 
       convert(varchar(7), [Date], 126)
)
select [date],lag(currentsales) over(order by [date]) pastsales,currentsales  from cte

image

3

Thank you