Solved

Rolling Date Range

Posted on 2007-04-09
14
596 Views
Last Modified: 2011-04-14
'morning. SQl 2k5. I need to modify my access query into an sp. i have started it, but am having issues with the date range. i need the range to be current month (partial or full) and prior 11 months. For example, today's report should reference: 5/01/06 to 4/09/07. Here is what I have:

(I got the date part from a prior post, but it isn't working as a rolling 12 month. It is returning Jun 2006 thru March 2007.)

ALTER PROCEDURE [dbo].[upOrders_WrntyExchsCY] AS
DECLARE @d datetime
SET @d = CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)
SET @d = dateadd(day, 1- datepart(day, @d ), @d )
SELECT ord.ServiceType, ord.OrderType, ord.ReportMonth,
CONVERT(CHAR(8), lmd.Report_M_Date, 10) AS Report_M_Date
FROM dbo.tblOrders AS ord LEFT OUTER JOIN dbo.tblLU_RptMDate AS lmd ON
ord.ReportMonth = lmd.ReportMonth WHERE (ord.OrderType <> 'PO') AND
(ord.OrderType <> 'SP') AND (lmd.Report_M_Date >= dateadd(month, -11, @d ))



0
Comment
Question by:adraughn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18875948
try

 AND (lmd.Report_M_Date >= dateadd('m', -11, @d ))
0
 
LVL 13

Author Comment

by:adraughn
ID: 18875983
that returned an error.

Invalid parameter specified for DateAdd (maybe because you are now passing 'm' as a string instead of month?)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18876023
adraughn,
sorry about that, thinking it was on access.
does SQL accept dateserial ?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 13

Author Comment

by:adraughn
ID: 18876040
i don't believe so, but i could be wrong. i have only used it in vb.

adria
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18876488
is Report_M_Date in the same format as @d ?
0
 
LVL 13

Author Comment

by:adraughn
ID: 18876752
yes, they are both dates.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18876783
that is not what i meant
mm/dd/yy is a date format
mm/dd/yyyy is another date format

are they both in this format  mm/dd/yy  ?
0
 
LVL 13

Author Comment

by:adraughn
ID: 18877113
not exactly, but i don't see why that matters, because the calcualtion in SQLwill use the date field, no matter how you format it.
0
 
LVL 13

Accepted Solution

by:
adraughn earned 0 total points
ID: 18877209
I ended up solvign it with a simple view instead of the stored procedure.

Here is the view:

SELECT     ord.ServiceType, ord.OrderType, ord.ReportMonth, CONVERT(CHAR(8), lmd.Report_M_Date, 10) AS Report_M_Date
FROM         dbo.tblOrders AS ord LEFT OUTER JOIN
                      dbo.tblLU_RptMDate AS lmd ON ord.ReportMonth = lmd.ReportMonth
WHERE     (ord.OrderType <> 'PO') AND (ord.OrderType <> 'SP') AND (lmd.Report_M_Date >= DATEADD(month, - 12, GETDATE()))
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18877222
In Access results varies if one of criteria in a query is a datefield and the datefield is not in the same format as the criteria.

see this thread
http://www.experts-exchange.com/Database/Miscellaneous/Q_22496839.html

and the selected answer

http://www.experts-exchange.com/Database/Miscellaneous/Q_22496839.html?cid=236#a18866018

try using the same format in both sides.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18877232
well, thats good
0
 
LVL 13

Author Comment

by:adraughn
ID: 18877243
this was all in SQL 2k5 - created for an access FE so the formatting issue does not apply. Thanks anyway for your resposnes.

adria
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question