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