CODEWITHSUNDEEP
sqldynamicquery
Shuvayan Das
Shuvayan Das

Reputation: 1048

Dynamic SQL query which is based on current month

Below is a SQL Code which creates a table from the union of monthly data summaries:

-- Oct 2017

select distinct  a.device_id ,201710 as month_id,
case when group_descr in ('BASE') then 'Basic'
     when group_descr in ('VALUES') then 'Valuable'
     when group_descr in ('PREFERRENCE') then 'Preferr'
     else 'Other'
end as Class
from dbo.DEVICE_HIST a
where a.expired >= '2017-10-01' and a.EFFTV <'2017-10-31'

union

-- Nov 2017
select distinct  a.device_id ,201711 as month_id,
case when group_descr in ('BASE') then 'Basic'
     when group_descr in ('VALUES') then 'Valuable'
     when group_descr in ('PREFERRENCE') then 'Preferr'
     else 'Other'
end as Class
from dbo.DEVICE_HIST a
where a.expired >= '2017-11-01' and a.EFFTV <'2017-11-30'

union

-- Dec 2017
select distinct  a.device_id ,201712 as month_id,
case when group_descr in ('BASE') then 'Basic'
     when group_descr in ('VALUES') then 'Valuable'
     when group_descr in ('PREFERRENCE') then 'Preferr'
     else 'Other'
end as Class
from dbo.DEVICE_HIST a
where a.expired >= '2017-12-01' and a.EFFTV <'2017-12-31'

union

-- Jan 2018
select distinct  a.device_id ,201801 as month_id,
case when group_descr in ('BASE') then 'Basic'
     when group_descr in ('VALUES') then 'Valuable'
     when group_descr in ('PREFERRENCE') then 'Preferr'
     else 'Other'
end as Class
from dbo.DEVICE_HIST a
where a.expired >= '2018-01-01' and a.EFFTV <'2018-01-31'

This is to be run monthly and based on the current month, we need to take 3 months data from last month - 1, backward. For example, if run in Feb it should take from Oct-Dec and if run in Mar it should take from Nov - Jan. This will be based on the current month but can someone please help me to automate this?

Something which takes the current month and goes back - 1 and then from there further 3 months. Though the date calculation is not that complex, how to make the query dynamically change based on current month?

Upvotes: 0

Views: 1996

Answers (1)

B3S
B3S

Reputation: 1051

Assuming this is SQL SERVER 2005+:

-- Oct 2017
select distinct  a.device_id
                ,CONCAT(CAST(YEAR(a.expired) AS varchar(4)),right('00'+CAST(MONTH(a.expired) AS nvarchar(2)), 2))  as month_id,
                case when group_descr in ('BASE') then 'Basic'
                     when group_descr in ('VALUES') then 'Valuable'
                     when group_descr in ('PREFERRENCE') then 'Preferr'
                     else 'Other'
                end as Class
from @test a
where CONVERT(date, a.expired) >= DATEADD(month, DATEDIFF(month, 0, convert(date, DATEADD(MONTH, -3, GETDATE()))), 0)

Upvotes: 1

Related Questions