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
Solved

Rolling Date Range

Posted on 2007-04-09
14
594 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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