Solved

Rolling Date Range

Posted on 2007-04-09
14
595 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Technology Partners: 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!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

740 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