Automate running prior 12 months data - return problem

Question

Post reply

Automate running prior 12 months data - return problem

D J-418094

SSC Rookie

Points: 26
More actions
March 13, 2007 at 11:27 am

#123150

I am trying to insert a set of date variables to use in a stored proc that will automatically select the last 12 running months worth of data grouped monthly. This code stops at the end of the prior year. and grabs data in the current month of the current year.
If I use the @endDate variable, it fails as the enddate < begdate
If I do not use the @begdate, I get Jan of the @begyr - getdate()
The subqueries in all the @'s return the desired data.
How do I adapt this code to produce the running prior 12 months ending at the end of the prior month?
Declare @begdate smalldatetime
Declare @enddate Smalldatetime
Declare @begYr SmallDatetime
Declare @endyr SmallDatetime
Set @begdate = (select case
When datepart(mm,Getdate())-12 = 0 Then 12
When datepart(mm,Getdate())-12 = -1 Then 11
When datepart(mm,Getdate())-12 = -2 Then 10
When datepart(mm,Getdate())-12 = -3 Then 9
When datepart(mm,Getdate())-12 = -4 Then 8
When datepart(mm,Getdate())-12 = -5 Then 7
When datepart(mm,Getdate())-12 = -6 Then 6
When datepart(mm,Getdate())-12 = -7 Then 5
When datepart(mm,Getdate())-12 = -8 Then 4
When datepart(mm,Getdate())-12 = -9 Then 3
When datepart(mm,Getdate())-12 = -10 Then 2
When datepart(mm,Getdate())-12 = -11 Then 1
End)


Set @begYr = (Select case
  when @Begdate >= datepart(mm,Getdate()) then datepart(yyyy,Getdate())-1
  Else datepart(yyyy,Getdate())
  End)
Set @enddate = (select case
When datepart(mm,Getdate())-1 = 0 Then 12
Else Datepart(mm,Getdate())-1
End)

Set @endYr = (Select case
  when @enddate >= datepart(mm,Getdate()) then datepart(yyyy,Getdate())-1
  Else datepart(yyyy,Getdate())
  End)
Select Count(id)ID
,Convert(Varchar (12),Date, 107) date
,Sum(TotalAmt)Billed
From Inv (nolock)
Where Datepart(yyyy, Paiddate) between @begyr and @endyr
and status = 'Paid'
and MOnth(PaidDate) > @begdate - 1

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

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

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

Check this out and see if it gives you what you are looking for:

declare @begindate datetime,

@enddate datetime

set @enddate = dateadd(dd, datediff(dd, 0, dateadd(dd, -1 * DAY(getdate()), getdate())), 0)

set @begindate = dateadd(yyyy, -1, dateadd(dd, 1, @enddate))

select @begindate. @enddate

Ken McKelvey SSCoach Points: 18865 More actions · Answer 2

I find your question confusing.

You are more likely to get appropriate help if sample data and expected results are provided.

Maybe you want something like:

DECLARE @EndDate smalldatetime

,@StartDate smalldatetime

SET @EndDate = CAST(CONVERT(varchar(6), GETDATE(), 112) + '01' AS smalldatetime)

SET @StartDate = DATEADD(m, -13, @EndDate)

SELECT YEAR(Paiddate) AS PaidYear

,MONTH(Paiddate) AS PaidMonth

,COUNT([id]) AS IDCount

,SUM(TotalAmt) AS Billed

FROM Inv WITH (NOLOCK)

WHERE status = 'Paid'

AND Paiddate >= @StartDate

AND Paiddate < @EndDate

GROUP BY YEAR(Paiddate)

,MONTH(Paiddate)

ORDER BY YEAR(Paiddate)

,MONTH(Paiddate)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

It looks like youare comparing date parts to actual date fields. When I ran your statement and printed your variables I got

begdate = Jan 4 1900 12:00AM

enddate = Jan 3 1900 12:00AM

begyr = Jun 30 1905 12:00AM

endyr = Jul 1 1905 12:00AM

This may do what you need to compare the actual dates instead of the parts

Declare @begdate smalldatetime

Declare @enddate Smalldatetime

Declare @begYr SmallDatetime

Declare @endyr SmallDatetime

select @begdate = dateadd(mm,-13,getdate())

select @enddate = dateadd(mm,-1,getdate())

select @begyr = convert(varchar,datepart(mm,@begdate)) + '/1/' + convert(varchar,datepart(yyyy,@begdate))

select @endyr = convert(varchar,datepart(mm,@enddate)) + '/1/' + convert(varchar,datepart(yyyy,@enddate))

print @begdate

print @enddate

print @begyr

print @endyr