quote:

---

Originally posted by mike13

Hi all,

I want to loop thru the 12 month, so i do not need to change the value of (MONTH(dbo.T_Order_Main.Shipdate) = 12) each time

here is my sql:
SELECT SUM(totals) AS totals
FROM (SELECT SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals, dbo.T_Order_Ship.payment
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Order_Ship ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Ship.OrderID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (YEAR(dbo.T_Order_Main.Shipdate) = 2011) AND (MONTH(dbo.T_Order_Main.Shipdate) = 12) AND
(dbo.T_Order_Ship.payment = 'btransfer')
GROUP BY MONTH(dbo.T_Order_Main.Shipdate),
dbo.T_Order_Detail.OrderID, dbo.T_Order_Ship.payment) AS DERIVEDTBL

thanks a lot,

mike

---

SELECT
	SUM([totals]) AS [totalMonthPayment]
	, [ShipDateMonth] AS [ShipDateMonth]
FROM (
	SELECT
		SUM(od.[Quantity] * od.[Cost]) AS [totals]
		, os.[payment] AS [payment]
		, MONTH(om.[ShipDate]) AS [ShipDateMonth]
	FROM
		dbo.T_Order_Main AS om
		INNER JOIN dbo.T_Order_Ship  AS os ON om.ORDERID = os.OrderID
		RIGHT OUTER JOIN dbo.T_Order_Detail AS od ON om.ORDERID = od.OrderID
	WHERE
		om = 8
		--AND YEAR(om.Shipdate) = 2011 (THIS IS BAD IT CAN'T USE AN INDEX)
		AND om.[ShipDate] >= '20110101'
		AND om.[ShipDate] < '20120101'
		AND os.[payment] = 'btransfer'
	GROUP BY
		od.[OrderID]
		, os.[payment]
		, MONTH(om.[ShipDate])
		)
		AS DERIVEDTBL
GROUP BY
	[ShipDateMonth]