December 11, 2008 at 11:44 am
SELECT od.OrderDetailID
, Dbo.TruncToDay(OD.shippingdate) as ShippingDate
, o.OrderID
, Dbo.TruncToDay(o.OrderDate) as OrderDate
, o.SaleCodeID
, o.Distributor
,OD. Returndate
,O.DistributorInventory
,O.WebOrderID
,CASE
WHEN LEN(PaymentType) > 2
THEN LEFT(PaymentType, LEN(PaymentType) - 2)
ELSE PaymentType
END AS PaymentType
FROM
(
SELECT o.OrderID ,
COALESCE(c.BillableAccount, 0) AS BillableAccount ,
COALESCE(o.DirectBill, 0) AS DirectBill ,
COALESCE(cp.CheckPaymentCount, 0) AS CheckPaymentCount ,
COALESCE(ccp.CreditCardPaymentCount, 0) AS CreditCardPaymentCount ,
(
CASE
WHEN COALESCE(c.BillableAccount, 0) = 1
THEN 'Billable + '
ELSE ''
END +
CASE
WHEN COALESCE(o.DirectBill, 0) = 1
THEN 'Direct + '
ELSE ''
END +
CASE
WHEN COALESCE(cp.CheckPaymentCount, 0) > 0
THEN 'Check + '
ELSE ''
END +
CASE
WHEN COALESCE(ccp.CreditCardPaymentCount, 0) > 0
THEN 'CreditCard + '
ELSE ''
END ) AS PaymentType
FROM Production.dbo.Orders AS o
LEFT JOIN Production.dbo.Customer AS c
ON c.CustomerID = o.CustomerID
LEFT JOIN
(
SELECT OrderID,
COUNT(*) AS CreditCardPaymentCount
FROM Production.dbo.CreditCardPayment
GROUP BY OrderID
) AS ccp
ON ccp.OrderID = o.OrderID
LEFT JOIN
(
SELECT OrderID,
COUNT(*) AS CheckPaymentCount
FROM Production.dbo.CheckPayment
GROUP BY OrderID
) AS cp
ON cp.OrderID = o.OrderID
WHERE EXISTS
(
SELECT *
FROM Production.dbo.OrderDetail AS od
JOIN Production.dbo.Product AS p
ON p.ProductID = od.ProductID
WHERE O.OrderDate >= DATEADD(mm, -48, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) -- keep 48 months history
)
)FROM Production.dbo.ORDERS AS O
LEFT OUTER JOIN
Production.dbo.ORDERDETAIL AS OD ON O.ORDERID = OD.ORDERID
LEFT OUTER JOIN
ORDERADDRESS AS OA ON O.ORDERID = OA.ORDERID
Help Appreciated
December 11, 2008 at 12:06 pm
Please help us out by telling us what "Some Error" is.....
Remember, we don't have your tables on our servers, so we can't just cut and paste your code into a window and see what errors come back. For tips on how to set up your question to get valid answers quicker, see:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply