SQL/Report Builder - Report to return data based on certain multple month entries

1
votes

I need to create a report that returns a range of data, based on an open date and any records opened within a month for each previous quarter.

So if I was to run the report at the start of August - it would show records which have an open date within May, Feb, November, August with no limit on the year. The open date month will be defined by the month where the report is run.

I have this very basic code

SELECT RecordMonth, RecordYear, caseRef, caseDescription, 
       caseOpenDate, caseClosedDate, PersonName, PersonSurname
  FROM dbo.casereview
 WHERE month(caseopendate) = 03
    OR month(casecopendate) = 06 
    OR month(caseopendate) = 09 
    OR month(caseopendate) = 12

Issue is, I need this to run dynamically, so whichever month it is run, it can show cases where the open date falls within the previous quarters/months.

2
sqlsql-serverdatereporting-servicesmonthcalendar

2 Answers

1
votes

You can use Modulo to calculate the 4 month numbers you need based on the current system date then filter on those.

In you case something like this will work.

DECLARE @month int = month(getdate())

DECLARE @m1 int = ((@month -1 ) % 12) +1
DECLARE @m2 int = ((@month + 2) % 12) +1
DECLARE @m3 int = ((@month + 5) % 12) +1
DECLARE @m4 int = ((@month + 8) % 12) +1

SELECT RecordMonth, RecordYear, caseRef, caseDescription, 
       caseOpenDate, caseClosedDate, PersonName, PersonSurname
  FROM dbo.casereview
 WHERE month(caseopendate) IN (@m1, @m2, @m3, @m4)

So first this is to get the current month number, we do this by using getdate() to get the current date and the MONTH function to return just the month number. So today this will equal 8.

Next we use Modulo (the % sign) to work out what months we need. So for example if month = 6 then @m4 is worked out as

(@month + 8) = 14

14 % 12 = 2 (remainder of 14 divided by 12)

2 + 1 = 3

Note: I've made this a bit longer than it needs to be so it's a bit easier to read. We could put the calcs for @m1, @m2 etc directly into the select statement but doing this separately makes it clearer. Notice I also used an IN statement rather than a bunch of ORs in the WHERE clause. IN just matches on any of the values listed in the parentheses.

0
votes

Assuming you want all matching rows in opendate order, we can simply use the Modulus function. We could do four MONTH % 12 queries, or a single MONTH % 3 query.

I built a test database to show the basic process:

create database [_TEST_QUARTERS];
GO

USE [_TEST_QUARTERS];
GO

create table [CASEREVIEW] (
    [CASE_ID]       integer     primary key,
    [CASEOPENDATE]  date
);

insert into [CASEREVIEW] values ( 1, '2016-09-19');
insert into [CASEREVIEW] values ( 2, '2016-10-20');
insert into [CASEREVIEW] values ( 3, '2016-11-11');
insert into [CASEREVIEW] values ( 4, '2016-12-22');
insert into [CASEREVIEW] values ( 5, '2017-01-11');
insert into [CASEREVIEW] values ( 6, '2017-02-12');
insert into [CASEREVIEW] values ( 7, '2017-03-13');
insert into [CASEREVIEW] values ( 8, '2017-04-14');
insert into [CASEREVIEW] values ( 9, '2017-05-15');
insert into [CASEREVIEW] values (10, '2017-06-16');
insert into [CASEREVIEW] values (11, '2017-07-17');
insert into [CASEREVIEW] values (12, '2017-08-18');

So we can see the data from which it has to choose: 

select CURRENT_TIMESTAMP as [Today];
select * from [CASEREVIEW];

And the actual query:

select [CASE_ID], [CASEOPENDATE] from [CASEREVIEW]
    where MONTH(CONVERT(VARCHAR(10), [CASEOPENDATE], 120)) % 3 = MONTH(CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 120)) % 3
ORDER BY [CASEOPENDATE];

Results:

CASE_ID     CASEOPENDATE
----------- ------------
3           2016-11-11
6           2017-02-12
9           2017-05-15
12          2017-08-18

(4 row(s) affected)