August 19, 2008 at 4:37 am
Hello am using the following query:
SELECTDISTINCT
T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename, T_OrderHeader.ShipSurname,
T_OrderHeader.ShipAdd1, T_OrderHeader.ShipCounty, T_OrderHeader.ShipPostcode, T_OrderHeader.OrderTotal,
T_OrderHeader.OrderDate, T_OrderHeader.ShipDate,
T_OrderHeader.OrderStatusID, T_Customers.Forename, T_Customers.Surname, T_Customers.Add1, T_Customers.County, T_Cards.CCNumber,
T_Customers.Postcode, T_Customers.[e-mail],T_Customers.[Entrydate], T_OrdersOnHold.HoldReasonCode
FROM T_OrderHeader INNER JOIN
T_Customers ON T_Customers.CustomerID = T_OrderHeader.CustomerID LEFT OUTER JOIN
T_Cards ON T_Cards.OrderID = T_OrderHeader.OrderID LEFT OUTER JOIN
T_OrdersOnHold ON T_OrderHeader.OrderID = T_OrdersOnHold.Orderid LEFT JOIN
T_HoldReasons ON T_OrdersOnHold.Orderid = T_OrderHeader.OrderID
WHERET_Customers.[Regdate] = GETDATE()-31 AND
((T_OrderHeader.ShipAdd1 <> T_Customers.Add1)OR
(T_OrderHeader.ShipPostcode <> T_Customers.Postcode) OR
T_OrderHeader.OrderTotal >= 200 OR
ABS(DATEDIFF(dd,T_OrderHeader.OrderDate,GETDATE())) BETWEEN 1 AND 7)
ORDER BYT_Customers.[Entrydate] DESC
---
with this query, i want to pull out records of NEW customers who have:
1-placed an order over a certain amount
2-requested for an order over a certain amount to be delivered to a different address,
3-requested for an order over a certain amount to be delivered to a different address, the next day
4-any new customer who has already placed an order within the last 2 weeks
Can anyone help?
August 19, 2008 at 5:02 am
The datetime data type stores time with the date, so instead of "T_Customers.[Regdate] = GETDATE()-31"
you may want to do something like:
DATEDIFF(day,getdate(), T_Customers.[Regdate]) = -31
Regards,
Andras
August 19, 2008 at 7:25 am
What constitutes a "new" customer?
August 19, 2008 at 7:34 am
Any customer who registered within the last 30 days
August 19, 2008 at 7:38 am
b_boy (8/19/2008)
with this query, i want to pull out records of NEW customers who have:1-placed an order over a certain amount
2-requested for an order over a certain amount to be delivered to a different address,
3-requested for an order over a certain amount to be delivered to a different address, the next day
4-any new customer who has already placed an order within the last 2 weeks
Is the "certain amount" referred to in 1,2 & 3 the same?
August 19, 2008 at 9:52 am
1-placed an order over a certain amount -(all orders)
2-requested for an order over a certain amount to be delivered to a different address,-( all orders for new customers over 90 pounds)
3-requested for an order over a certain amount to be delivered to a different address, the next day - (all orders for new customers over 20 pounds)
4-any new customer who has already placed an order within the last 2 weeks - (new customers placing an order within a 14 day period, irrespective of amount)
August 19, 2008 at 9:55 am
What is the amount in condition #1?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy