Solved

Rolling Date Range

Posted on 2007-04-09
14
598 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update 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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

615 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