SQLTeam.com | Weblogs | Forums

Sql query showing previous month value HELP

#1

HI Guys

i have some data and I am having difficulty creating a query to group the data by month year and then show current month value and the previous month value by city, example shown below

i have the below data

date city value
05/01/2021 London 25
11/01/2021 London 23
15/01/2021 London 22
25/01/2021 leeds 78
01/02/2021 London 12
02/02/2021 leeds 12
03/02/2021 leeds 12

i need a query which will return the below ( based on current month being February)

date city previous Value Current Value
Feb-21 London 70 12
Feb-21 Leeds 78 24

can this be done?

thanks for any help provided

#2

I don't have usable data to test with, but something like this should work:

;WITH cte_dates AS (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS start_of_previous_month,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS start_of_current_month,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AS start_of_next_month
)
SELECT
    STUFF(RIGHT(CONVERT(varchar(30), GETDATE(), 6), 6), 4, 1, '-') AS date,
    city,
    SUM(CASE WHEN date < start_of_current_month THEN value ELSE 0 END) AS [previous value],
    SUM(CASE WHEN date >= start_of_next_month THEN value ELSE 0 END) AS [current value]
FROM dbo.table_name
WHERE
    date >= start_of_previous_month AND
    date < start_of_next_month
GROUP BY city
ORDER BY city
#4

hi

hope this helps

drop create sample data .. click arrow to the left 
set dateformat dmy 
drop table #Sampledata 
create table #Sampledata 
([date] date,city varchar(20) , value int 
)
insert into #Sampledata select '05/01/2021','London',25
insert into #Sampledata select '11/01/2021','London',23
insert into #Sampledata select '15/01/2021','London',22
insert into #Sampledata select '25/01/2021','leeds',78
insert into #Sampledata select '01/02/2021','London',12
insert into #Sampledata select '02/02/2021','leeds' ,12
insert into #Sampledata select '03/02/2021','leeds',12

select * from #Sampledata

-- February 
declare  @curr_month int = 2

; with cte as 
(
    select 
	    year([date])  yr ,
		month([date]) mm ,
		city             ,
		sum(value) as sm 
 	from 
	     #Sampledata 
	group by 
	  year([date]),month([date]),city
) 
select 
  * 
from 
  cte a join cte b 
      on a.city = b.city and a.yr= b.yr and  a.mm = @curr_month -1 and b.mm = @curr_month

image

#5

thanks