Need Help Pulling MySQL Data from Mutiple Dates
Posted on 2011-04-28
I have a query where I am trying to pull data from a DB that houses deltailed customer Invoice History. The issue I am having is I need each row of the result to produce data for 2008, 2009, and 2010. When I try to run the query I get an invalid join error message for T6. Can anyone assist?
T1.InvoiceNum As InvoiceNum,
T5.SubCatCode As SubCatCode,
T5.SubCatDescription As SubCatDescription,
SUM(T.QtyShipped) as 'Qty08',
SUM(T.Amount) as 'Sales08',
SUM(T6.QtyShipped) as 'Qty09',
SUM(T6.Amount) as 'Sales09',
SUM(T7.QtyShipped) as 'Qty2010',
SUM(T7.Amount) as 'Sales2010'
FROM arinvdet T
Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID
Left Outer Join arcusts T2 On T1.CustID = T2.CustID
Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID
Left Outer Join icitems T4 On T.ItemID = T4.ItemID
Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID
Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID
Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID
Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID
Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID
WHERE T2.CustAcct ='75^'
and T4.ItemCode <>''
and T1.InvoiceDate Between '2008-1-1' and '2008-12-31'
and T8.InvoiceDate Between '2009-1-1' and '2009-12-31'
and T9.InvoiceDate Between '2010-1-1' and '2010-12-31'
Group By t4.ItemCode