finding previous 12 months data, not including current month

Question

finding previous 12 months data, not including current month

jeffshelix

SSCrazy

Points: 2257
More actions
October 4, 2018 at 9:46 am

#373399

on the 10th of each month i run a report for the 12 month period, not including the month i am in.
So in October 2018, i want the report to pull for Oct 2017 through Sept 2018. With no Oct 2018 data.
I have this . ..
And h.Import_Date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE)) --And h.Import_Date < ????
and have been fixing it in Excel . . .but i am passing this off to someone else who shouldnt have to fix in excel.
As usual, thanks all

Viewing 5 posts - 1 through 4 (of 4 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

The problem is GETDATE(). It's not going to return the first of the month. Try this:
DECLARE @Today AS date = GETDATE(); DECLARE @FirstOfTheMonth AS date = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1); DECLARE @TodayMinus13Months AS date = DATEADD(month, -13, @FirstOfTheMonth); . . . . AND h.Import_Date >= @TodayMinus13Months AND h,Import_Date < @FirstOfTheMonth

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

jeffshelix - Thursday, October 4, 2018 9:46 AM
on the 10th of each month i run a report for the 12 month period, not including the month i am in.
So in October 2018, i want the report to pull for Oct 2017 through Sept 2018. With no Oct 2018 data.
I have this . ..
And h.Import_Date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE)) --And h.Import_Date < ????
and have been fixing it in Excel . . .but i am passing this off to someone else who shouldnt have to fix in excel.
As usual, thanks all

This?select getdate() , dateadd(month,datediff(month,0,getdate()) - 12,0) as ForGreaterThenOrEqual , dateadd(month,datediff(month,0,getdate()),0) as ForLessThan;

below86 SSChampion Points: 11532 More actions · Answer 3

Changed what Steve posted:
DECLARE @Today AS date = GETDATE(); DECLARE @FirstOfTheMonth AS date = DATEADD(DD, 1 -DATEPART(DD, @Today), @Today); DECLARE @TodayMinus12Months AS date = DATEADD(month, -12, @FirstOfTheMonth);

AND h.Import_Date >= @TodayMinus12Months AND h.Import_Date < @FirstOfTheMonth This returns: Today FirstOfTheMonth TodayMinus12Months 2018-10-04 2018-10-01 2017-10-01

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

jeffshelix - Thursday, October 4, 2018 9:46 AM
on the 10th of each month i run a report for the 12 month period, not including the month i am in.
So in October 2018, i want the report to pull for Oct 2017 through Sept 2018. With no Oct 2018 data.
I have this . ..
And h.Import_Date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE)) --And h.Import_Date < ????
and have been fixing it in Excel . . .but i am passing this off to someone else who shouldnt have to fix in excel.
As usual, thanks all

So what happens when you're out sick that day and no one runs the report and you come in to work on the 11th or 12th of the month?

And, for the report, do you want all of the data for the last 12 months or monthly aggregates or what? And what day of the month will that data start on... the 10th 13 months ago? And where do you actually want it to cut off? Prior to the first of the current month or prior to the 10th of the current month?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)