Apr 09, 2013 at 04:50 AM

Help needed adding to a WHERE statement

24 Views

Hi,

I have the below query, which brings up a list of customer details along with their previous years sales history:

SELECT T0.[CardCode], T0.[CardName], T0.[Phone1], T0.[CntctPrsn], T0.[MailAddres], T0.[MailCity], T0.[State2], T0.[MailZipCod], T1.[SlpName], T1.[SlpCode], T0.[Currency], T0.[E_Mail], T0.[Fax], T0.[frozenFor], T2.Sales as '2010', T3.Sales as '2011', T4.Sales as '2012', T5.Sales as '2013' FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

LEFT OUTER JOIN

(

Select SubString(T10.Code,1,4) as Year, T11.CardCode, Sum(T11.Sales) as Sales from

(

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period FROM [dbo].[@PERIOD_HISTORY] T0

UNION ALL

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[T_RefDate], 113), 4, 8) AS Period FROM OFPR T0

) AS T10

LEFT OUTER JOIN

(

SELECT T0.CardCode, T0.Period as Period, Sum(T0.Sales) as Sales

FROM

(

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum]) as Sales FROM OINV T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum])*-1 as Sales FROM ORIN T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period , Sum(T0.[U_Subtotal]) as Sales FROM [dbo].[@OINV] T0 GROUP BY T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8)

) as T0

Group By T0.CardCode, T0.Period

) AS T11 ON T10.Period = T11.Period

GROUP BY SubString(T10.Code,1,4), T11.CardCode

) as T2 on T0.CardCode = T2.CardCode and T2.Year = 2010

LEFT OUTER JOIN

(

Select SubString(T10.Code,1,4) as Year, T11.CardCode, Sum(T11.Sales) as Sales from

(

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period FROM [dbo].[@PERIOD_HISTORY] T0

UNION ALL

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[T_RefDate], 113), 4, 8) AS Period FROM OFPR T0

) AS T10

LEFT OUTER JOIN

(

SELECT T0.CardCode, T0.Period as Period, Sum(T0.Sales) as Sales

FROM

(

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum]) as Sales FROM OINV T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum])*-1 as Sales FROM ORIN T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period , Sum(T0.[U_Subtotal]) as Sales FROM [dbo].[@OINV] T0 GROUP BY T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8)

) as T0

Group By T0.CardCode, T0.Period

) AS T11 ON T10.Period = T11.Period

GROUP BY SubString(T10.Code,1,4), T11.CardCode

) as T3 on T0.CardCode = T3.CardCode and T3.Year = 2011

LEFT OUTER JOIN

(

Select SubString(T10.Code,1,4) as Year, T11.CardCode, Sum(T11.Sales) as Sales from

(

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period FROM [dbo].[@PERIOD_HISTORY] T0

UNION ALL

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[T_RefDate], 113), 4, 8) AS Period FROM OFPR T0

) AS T10

LEFT OUTER JOIN

(

SELECT T0.CardCode, T0.Period as Period, Sum(T0.Sales) as Sales

FROM

(

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum]) as Sales FROM OINV T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum])*-1 as Sales FROM ORIN T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period , Sum(T0.[U_Subtotal]) as Sales FROM [dbo].[@OINV] T0 GROUP BY T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8)

) as T0

Group By T0.CardCode, T0.Period

) AS T11 ON T10.Period = T11.Period

GROUP BY SubString(T10.Code,1,4), T11.CardCode

) as T4 on T0.CardCode = T4.CardCode and T4.Year = 2012

LEFT OUTER JOIN

(

Select SubString(T10.Code,1,4) as Year, T11.CardCode, Sum(T11.Sales) as Sales from

(

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period FROM [dbo].[@PERIOD_HISTORY] T0

UNION ALL

SELECT T0.[Code], T0.[Name], SUBSTRING(CONVERT(VARCHAR(11), T0.[T_RefDate], 113), 4, 8) AS Period FROM OFPR T0

) AS T10

LEFT OUTER JOIN

(

SELECT T0.CardCode, T0.Period as Period, Sum(T0.Sales) as Sales

FROM

(

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum]) as Sales FROM OINV T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[CardCode] as CardCode, SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8) AS Period, Sum(T0.[DocTotal]-T0.[VatSum])*-1 as Sales FROM ORIN T0 GROUP BY T0.[CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[DocDate], 113), 4, 8)

UNION ALL

SELECT T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8) AS Period , Sum(T0.[U_Subtotal]) as Sales FROM [dbo].[@OINV] T0 GROUP BY T0.[U_CardCode], SUBSTRING(CONVERT(VARCHAR(11), T0.[U_Date], 113), 4, 8)

) as T0

Group By T0.CardCode, T0.Period

) AS T11 ON T10.Period = T11.Period

GROUP BY SubString(T10.Code,1,4), T11.CardCode

) as T5 on T0.CardCode = T5.CardCode and T5.Year = 2013

WHERE T0.[CardType] = 'C'

I need to add a WHERE statement to the above to ask the user for the salespersons name.

When I try to add AND T1.SlpName = [%0] to the WHERE statement, it doesn't work.

Any help would be appreciated!!

Thanks

Rick