Return error with Automated sp date Variable

[DJo]

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. - See example at end

If I use the @endDate variable, it fails as the enddate return (month #)< begdate return (month #)

If I do not use the @begdate, I get Jan of the @begyr - getdate(); it does not limit the return to the last 12 months.

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

Example Return

ID Date Billed

45 Mar 14, 2006 56583.5700
50 Mar 28, 2006 897942.2600
145 Mar 07, 2006 2097684.8200
10 Mar 21, 2006 61789.5500
33 Apr 25, 2006 796758.0900
104 Apr 11, 2006 696103.9600
79 Apr 04, 2006 469806.5300
48 Apr 18, 2006 754714.9300
57 May 02, 2006 1032867.0500
35 May 16, 2006 5287487.2100
17 May 30, 2006 801157.9900
66 May 09, 2006 471842.9800
9 May 23, 2006 38599.2000
15 Jun 13, 2006 112468.2200
9 Jun 20, 2006 391731.0300
7 Jun 27, 2006 278360.9700
14 Jun 06, 2006 52760.0700
9 Jul 18, 2006 1258783.4200
21 Jul 04, 2006 461645.9600
6 Jul 11, 2006 18131.0100
12 Jul 25, 2006 1650286.9800
17 Aug 15, 2006 251152.0800
9 Aug 01, 2006 236567.5800
3 Aug 08, 2006 8022.4400
3 Aug 22, 2006 97479.7400
4 Aug 29, 2006 114616.0700
3 Sep 26, 2006 1118.7900
5 Sep 12, 2006 16873.8300
5 Sep 05, 2006 170047.7500
1 Sep 19, 2006 15041.6500
18 Oct 17, 2006 362827.3700
8 Oct 24, 2006 75506.4300
6 Oct 10, 2006 34126.1500
7 Oct 03, 2006 60572.8400
13 Oct 31, 2006 14734.3600
1 Nov 21, 2006 82.8000
12 Nov 28, 2006 257806.6500
8 Nov 07, 2006 588884.7800
2 Nov 14, 2006 249230.1200
4 Dec 26, 2006 209609.8200
9 Dec 19, 2006 114250.2100
4 Dec 12, 2006 6261.0800
7 Dec 05, 2006 42137.7500
2 Mar 06, 2007 397475.5100

[dorinbogdan]

Use just 2 variables for dates.
@StartDate as first day of 13th prior month date
@EndDate as last day of prior month date

declare @EndDate smalldatetime
declare @StartDate smalldatetime
set @EndDate = dateadd(mm,datediff(mm,30,dateadd(mm,-1, getdate())),30)
set @StartDate = dateadd(yy, -1, dateadd(mm,datediff(mm,0,dateadd(mm,-1, getdate())),0))
 
Select Count(id) ID, Convert(Varchar (12),Date, 107) date, Sum(TotalAmt) Billed
From Inv (nolock)
Where Paiddate between @StartDate and @EndDate