Getting previous month data when new year starts

Question

Post reply

Getting previous month data when new year starts

alex_martinez

SSCrazy

Points: 2144
More actions
November 14, 2015 at 12:41 pm

#322654

Hello,
I am using 2008 SSRS I have a subscription report. On the first of every month I am able to get the previous month data for my report, which send it to a file on the first of every month. The code I am using for the month parameter is Month(Now() -1, I am having trouble with the code for the year I can ask for current year, but When January 1st comes around I want to get December of 2015. When February 1st 2016 comes around I want January 2016 data, etc. If I set the Year parameter for current year I won't get December 2015. Any help will be appreciated on coding. Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63488 More actions · Answer 1

Instead of using this:

Month(Now() -1

you could use DateAdd() and subtract a month from some date, and then return all dates between the first of the month and the last.

alex_martinez SSCrazy Points: 2144 More actions · Answer 2

Thank you, I still a little fizz on this, can you give me an example. Thank you.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

alex_martinez (11/14/2015)
Thank you, I still a little fizz on this, can you give me an example. Thank you.

I'm not at a PC so I can't test this but this should work.

SELECT DATEADD(MONTH,-1, GETDATE ())

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63488 More actions · Answer 4

I am assuming you have a table of dates in your database or a Calendar table (basically a contiguous list of dates). Because dates are continuous, you could just use something like:

BETWEEN @SomeDate AND DATEADD(month,1,@SomeDate) to filter your dataset.

so your whole query might be something like:

SELECT {field list}

FROM Calendar c LEFT JOIN Data d ON c.CalendarDate = d.EventDate

WHERE d.EventDate BETWEEN @StartDate AND DATEADD(Day,-1,DATEADD(Month,1,@StartDate))

then because the December- January dates are all continuous, you wouldn't have to worry about year ends etc. The other nice thing a Calendar table does for you is providing easy to use groupings (like MonthOfYear, DayOfYear, etc...)

alex_martinez SSCrazy Points: 2144 More actions · Answer 5

alex_martinez

SSCrazy

Points: 2144

November 15, 2015 at 10:25 am

#1839849

Thanks pietlinden I appreciate your help.

alex_martinez SSCrazy Points: 2144 More actions · Answer 6

Alan, thanks for giving me the tip I appreciate your help.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

You can have your main query in the form of ::

SELECT /* your columns */

FROMyour table

WHEREdateField>= @startDate and dateField < @endDate

Set startDate ,@endDate default to the value of datasets like :

declare @startDate DATE, @endDate DATE;

SELECT @startDate=CAST(DATEADD(d, -day(current_timestamp)+1,DATEADD(MONTH,-1, Current_timestamp)) as DATE) ;

SELECT @endDate = dateadd(m,1,@startDate) ;

--select @startdate, @enddate;

----------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Another opinion.

declare @CurrentDate datetime = dateadd(month,1,getdate()); -- Say current date is a month from now. In the declares below you could use GETDATE() instead.

declare @StartDate datetime = dateadd(month,datediff(month,0,@CurrentDate) - 1, 0),

@EndDate datetime = dateadd(month,datediff(month,0,@CurrentDate), 0);

select @CurrentDate, @StartDate, @EndDate;

-- Your query would look something like this:

select

-- your columns --

from

dbo.YourTable

where

YourDateColumn >= @StartDate and

YourDateColumn < @EndDate;