Dynamic SQL query which is based on current month

Question

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?

Which version of SQL? SQL Server 2016 has an EOMonth() function where as previous versions didn't — Dave Poole, Mar 02 '18 at 10:18
Which RDMS are you using? You basically need to change all those hardcoded dates to use a function to take today's date, then find the first/ last day of the current month, then last month, etc. But the syntax will depend on which database you are using. — Richard Hansell, Mar 02 '18 at 10:18
Unrelated, but for performance it is better to use `UNION ALL`. Also, why do you use `IN` with only one value? `in ('BASE')` is identical to `= 'BASE'`. — HoneyBadger, Mar 02 '18 at 10:24
What is the relationship between `a.expired` and `a.EFFTV`? Are both of them required? — Peter Abolins, Mar 02 '18 at 11:11
The DB is PostGreSql and yes both the a.expired and a.EFFTV are needed. — Shuvayan Das, Mar 02 '18 at 11:20
You should tag your question properly. `sql` isn't a specific RDBMS. — Peter Abolins, Mar 02 '18 at 12:08

B3S · Accepted Answer · 2018-03-02T12:05:25.393

1

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)

edited Mar 02 '18 at 12:05

answered Mar 02 '18 at 10:54

B3S

1,021
7
18

This doesn't answer the question, but since it is accepted, maybe the question was wrong? – Peter Abolins Mar 02 '18 at 12:01
@PeterAbolins: yes i missed the where clause cause i didnt test on real data. Tried to create a test table and i am going to edit my answer – B3S Mar 02 '18 at 12:04
1

Well... the question specified that the range should be 3 months, but start from one month ago. So, you need to match `getdate() - 4 months` through to `getdate() - 1 month`. Also... `in` makes no sense if there is only one value. – Peter Abolins Mar 02 '18 at 12:12
the `in` may be there cause he wants the code ready for adding more values in future, but yes its is generally correct to use the = operator instead. the `getdate() - 4 months` may not be correct cause he could miss part of the considered month (e.g. if today is march 30 and he wants all december, he will miss days 1-29 ) – B3S Mar 02 '18 at 13:09

Dynamic SQL query which is based on current month

1 Answers1