SSRS SQL I need to display data for dates used in the parameter and the previous month

Question

I have an SSRS report with parameters for Created On Start and Created On End. Users run this manually and choose the date range to display records for. I need to display in two different columnns the records for the month the user entered in the parameters and the previous month for the dates used in the parameters.

For example the user uses the the following dates in the parameters:

Start Date: 03/01/2016 EndDate: 03/31/2016

The Report should display in one column the records for march 2016 and next to it the records for february 2016

Answer 1

You could write one query which queries both months.

Add a field that will act as the column label eg format the date as the first of the month.

Then create a pivot table to show the two months as the columns with the usual rows .

EDIT - new details So: dateStart = '2016-03-01' dateEnd = '2016-03-31' These could be less than the whole month, but should be in the same month. prevStart = DATEADD(month, DATEDIFF(month, '2000-01-01', dateStart)-1, '2000-01-01') the first day of the previous month. Use similar for the prevEnd to calculate the last day of previous month.

OK. Now build your select:

SELECT xxxx, yyyy, zzzz
, DATEADD(month, DATEDIFF(month, '2000-01-01', createdOnDate), '2000-01-01')  as MonthCol
FROM tables
WHERE (createdOnDate>= prevStart and createdOnDate<=prevEnd)
 OR (createdOnDate>= dateStart and createdOnDate<=dateEnd)

Build a pivot table style grid with monthCol as the heading of the columns and your usual data as the rows. That way you can get your "previous Month" columns as well as the date range that you selected