473,216 Members | 1,275 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Return error with Automated sp date Variable

1
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
Mar 13 '07 #1
1 1818
dorinbogdan
839 Expert 512MB
Use just 2 variables for dates.
@StartDate as first day of 13th prior month date
@EndDate as last day of prior month date
Expand|Select|Wrap|Line Numbers
  1. declare @EndDate smalldatetime
  2. declare @StartDate smalldatetime
  3. set @EndDate = dateadd(mm,datediff(mm,30,dateadd(mm,-1, getdate())),30)
  4. set @StartDate = dateadd(yy, -1, dateadd(mm,datediff(mm,0,dateadd(mm,-1, getdate())),0))
  5.  
  6. Select Count(id) ID, Convert(Varchar (12),Date, 107) date, Sum(TotalAmt) Billed
  7. From Inv (nolock)
  8. Where Paiddate between @StartDate and @EndDate
  9.  
Mar 14 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Martin Lucas-Smith | last post by:
I've noticed since moving from Apache1/mod_php4/linux to Apache2/mod_php5/linux that when an error is produced by PHP and put in the error_log, the date is no longer logged as well: ...
2
by: Konstantin Zakharenko | last post by:
Hello, Our QA team have running a lot of test scripts (for automated regression testing), they run them on the different databases (Oracle/MS SQL). Several of those tests are dependent on the...
2
by: Paolo | last post by:
I have a table named CASES based used to store data for a small insurance company used to keep track of all the different forms used for a specific client. Some field refers to a different forms...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
2
by: Lee Silver | last post by:
Environment: W2K SP3 and .NET 1.1 I'm trying to set my machine up to do some Web-form development but am unable to successfully configure the IIS Extensions. When I click Finish at the end of the...
5
by: Dmitriy Lapshin [C# / .NET MVP] | last post by:
Hi all, I think the VB .NET compiler should at least issue a warning when a function does not return value. C# and C++ compilers treat this situation as an error and I believe this is the right...
4
by: =?Utf-8?B?TWlrZSBI?= | last post by:
I'm using a block of ASP to allow a user to send a form via e-mail. However, someone keeps sending me spam through this form and they're using a bogus return address. I'm testing for a successful...
3
by: Leighya | last post by:
Im currently working on this xml file but when i load it to Mozilla, i got an error "Error Loading Stylesheet: Xpath parse failure: invalid variable name" It loads on IE properly. Only with the...
4
by: wizardry | last post by:
hello - i've created a form that has multiple inserts. it inserts the data fine if i manually parse the data to it but when i use the form to test the inserts it errors out. it errors out at...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.