July 21, 2008 at 5:22 am
Hello
I am trying to write a query, that will retrieve records of customers that are about to expire within two week of expiry.
This is the query done so far by myself:
USE db
--XX ABOUT TO EXPIRE REPORT--
SELECTT_OrderHeader.OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle,
T_OrderHeader.ShipForename, T_OrderHeader.ShipSurname, T_OrderHeader.OrderTotal,
T_OrderHeader.OrderStatusID, T_Customers.ExpiryDate
FROMT_OrderHeader INNER JOIN T_Customers
ONT_OrderHeader.CustomerID = T_Customers.CustomerID
WHERET_Customers.ExpiryDate = (GETDATE() + 7)
Can anyone help or put me on the right track?
July 21, 2008 at 5:46 am
WHERE T_Customers.ExpiryDate = DATEADD(day,7,GETDATE())
July 21, 2008 at 6:18 am
Thanks Hari
Somehow it didnt return any records, but I used the following code:
WHERET_OrderHeader.OrderDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7,
GETDATE())), 0)
AND T_OrderHeader.OrderDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
it returned records, but when i used on another table as:
WHERET_Customers.ExpiryDate.OrderDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7,
GETDATE())), 0)
AND T_Customers.ExpiryDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
I got the following error message, do you have an idea why?
Msg 258, Level 15, State 1, Line 8
Cannot call methods on datetime.
July 21, 2008 at 6:30 am
Do they have same data types?
I think some column is of varchar
July 21, 2008 at 6:40 am
The data type is datetime, and this is the full copy of the cold:
SELECTCONVERT(varchar(11), T_OrderHeader.OrderDate, 106) AS [Order Date], T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle,
T_OrderHeader.ShipForename, T_OrderHeader.ShipSurname, T_OrderHeader.OrderTotal,
T_OrderHeader.OrderStatusID, T_Customers.ExpiryDate
FROMT_OrderHeader INNER JOIN T_Customers
ONT_OrderHeader.CustomerID = T_Customers.CustomerID
WHERET_Customers.ExpiryDate >= DATEADD(day,-7,GETDATE())
Also, if the T_Customers.ExpiryDate is null field will it matter?
July 21, 2008 at 7:18 am
b_boy
What if there are no customers about to expire? You will get no data returned from your query and think there's something wrong with your coding.
Often eyeballing the data can give you a good idea of the spread of values which you are likely to find. You can restrict a returned data set in a whole lot of ways until you can see only the data you are interested in, so you know - if only roughly - how many rows there are, and what values might be equal to or similar to those which you are using for your restriction.
You should try this on your customer table. Find out roughly how many customers are about to expire (:hehe:) by playing with the customers table alone, doing different cuts based on ExpiryDate.
DECLARE @ExpiryDate DATETIME
SET @ExpiryDate = CONVERT(DATETIME, '2008-08-31', 120)
SELECT TOP 100 ExpiryDate FROM T_Customers WHERE ExpiryDate < @ExpiryDate ORDER BY ExpiryDate DESC
Running this really simple query will give you an idea of how the data looks, it could even include the rows you want. You will still have to correctly code for the beginning and end of your range but at least you will know what to expect. When you're confident that you've coded correctly to capture the relevant rows, THEN join the Orders table.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2008 at 7:50 am
Sorry "Old Hand"
But this just messes things up, you may need to explain a little more in-depth, if you don,t mind
July 21, 2008 at 8:06 am
Don't mind at all but like everybody else here I'm doing real-time work simultaneously so responses may not be immediate.
Back to the beginning...
What if there are no customers about to expire? You will get no data returned from your query and think there's something wrong with your coding.
Does your query bring back any results? If not, expand the date range which you're using for your selection. Right now you can't tell if your query is incorrect or if you have no data which matches your selection criteria. The easiest way to do that is to look specifically at the ExpiryDate column in the customers table and see if you have any matching data. If you don't, then in order to test your query you might have to select a different date range and then switch back when you're satisfied that it will work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2008 at 9:06 am
b_boy (7/21/2008)
I am trying to write a query, that will retrieve records of customers that are about to expire within two week of expiry....
WHERET_Customers.ExpiryDate = (GETDATE() + 7)
Did you really mean "with two weeks"? Because if you did, then you need to add 14 days, not 7.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 21, 2008 at 9:15 am
rbarryyoung (7/21/2008)
b_boy (7/21/2008)
I am trying to write a query, that will retrieve records of customers that are about to expire within two week of expiry....
WHERET_Customers.ExpiryDate = (GETDATE() + 7)
Did you really mean "with two weeks"? Because if you did, then you need to add 14 days, not 7.
It's even more complicated than that...the original date coding in the OP's second post gives the start date as 14th July and the end date as first thing today...
SELECT DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7, GETDATE())), 0)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Which should be (using variables 'cos you can peek more easily)...
DECLARE @DateRangeStart DATETIME, @DateRangeEnd DATETIME
SET @DateRangeStart = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) -- first thing today (monday)
SET @DateRangeEnd = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 15) -- first thing on tuesday 5th August
-- Check the dates are what you expect them to be:
-- "in the next two weeks" is ambiguous e.g. could start today or tomorrow
-- could be whatever is left of this working week plus next working week
SELECT @DateRangeStart, @DateRangeEnd
SELECT TOP 100 *
FROM T_Customers
WHERE ExpiryDate >= @DateRangeStart -- from the start of today
AND ExpiryDate < @DateRangeEnd -- to the end of monday 4th August (or sunday 3rd if you prefer)
ORDER BY ExpiryDate
Some clarification required I reckon, Barry 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2008 at 9:17 am
b_boy (7/21/2008)
Thanks HariSomehow it didnt return any records, but I used the following code:
WHERET_OrderHeader.OrderDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7,
GETDATE())), 0)
AND T_OrderHeader.OrderDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
it returned records, but when i used on another table as:
WHERET_Customers.ExpiryDate.OrderDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7,
GETDATE())), 0)
AND T_Customers.ExpiryDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
I got the following error message, do you have an idea why?
Msg 258, Level 15, State 1, Line 8
Cannot call methods on datetime.
The reason you get this error is because of you reference to the date column.
You have said T_Customers.ExpiryDate.OrderDate
I'm assuming you mean either
T_Customers.ExpiryDate
Or
T_Customers.OrderDate
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 21, 2008 at 9:52 am
Hello All
I kinda found a way which is KISS (Keep It Simple and Simple), i used in my select query:
WHERE[ExpiryDate] BETWEEN getdate() AND getdate() +30
July 21, 2008 at 10:01 am
b_boy (7/21/2008)
Hello AllI kinda found a way which is KISS (Keep It Simple and Simple), i used in my select query:
WHERE[ExpiryDate] BETWEEN getdate() AND getdate() +30
??? So I guess the time frame is completely arbitrary?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 21, 2008 at 10:02 am
b_boy (7/21/2008)
Hello AllI kinda found a way which is KISS (Keep It Simple and Simple), i used in my select query:
WHERE[ExpiryDate] BETWEEN getdate() AND getdate() +30
b_boy, KISS is an acronym for Keep It Simple, Stupid.
WHERE[ExpiryDate] BETWEEN getdate() AND getdate() +30
will fail on some boundary dates because ExpiryDate and getdate() are datetimes.
I am trying to write a query, that will retrieve records of customers that are about to expire within two week of expiry.
Here in the UK, a fortnight is two weeks (14 days), not 30 days.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2008 at 10:40 am
Sorry for the mix up about the dates i was just using 14 days as an example.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply