Invoice Line Total Query

[former_member509679]

Hi Experts,

I have the code below but i am trying to add extra columns in it, i have added some of the additional FROM sections, but i am struggling with the main section of the query.

I want to add SUM(LineTotal) from INV1 where the preferred supplier of the item is equal to a specific BP, prefered supplier OITM.CardCode.

the reason for this is i would like to know total spend from the customers as well as total spend from our main supplier of goods.

e.g. customer has spent £10,000 total and £5,000 of this is supplied by xxx

Hope the above makes sense?

SELECT TT.CardCode as 'Customer ID', TT.CardName, TT.GroupName as 'Group', TT.QryGroup19 as 'Sub Disti', TT.Name as 'Account Owner', TT.SlpName as 'Int. Account Manager', TT.Descr as 'Ext. Account Manager',
 max(TT.Actual) as 'Current Year Spend', max(TT.Last) as 'Last Years Spend', max(TT.month1) as 'Last 90 Days Spend', max(TT.month2) as 'Last 160 Days Spend'

FROM (
SELECT T0.CardCode, T1.CardName, T3.GroupName, T1.QryGroup19, T2.Name, T6.SlpName,  T5.Descr,
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'month1',
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-160 group by CardCode),0) as 'month2'


FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
LEFT OUTER JOIN [@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
INNER JOIN OCRG T3 ON T1.[GroupCode] = T3.[GroupCode]
left outer join [@commission] T4  on T1.U_commission = T4.Code
left OUTER JOIN UFD1 T5 ON T1.[U_AccMan] = T5.[FldValue] AND TableID = 'OCRD' AND FieldID = 20
INNER JOIN OSLP T6 ON T1.[SlpCode] = T6.[SlpCode] 
INNER JOIN INV1 T7 ON T0.[DocEntry] = T7.[DocEntry]
INNER JOIN OITM T8 ON T7.[ItemCode] = T8.[ItemCode]
) TT

GROUP BY TT.CardCode, TT.CardName, TT.GroupName, TT.QryGroup19, TT.Name, TT.SlpName, TT.Descr
ORDER BY TT.CardCode