Quert Adjust For Specific BP Property

[Former Member]

Hello All -

We would like to modify the query below so that it only pulls data for BP's that have BP Property #2 selected. Can anyone help with this change?

Thanks,

Mike

SELECT T.CardCode 'Acct#',T.CardFName,U.SlpName 'Sales Rep',T.CardName 'Company', T.Address,T.City,

T.ZipCode,T.State1,

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

FROM dbo.OCRD T

LEFT JOIN (SELECT T1.CardCode as [Acct#],

T0.DocTotal as [DocTotal],

MONTH(T0.docdate) as [month]

FROM dbo.OCRD T1

LEFT JOIN dbo.OINV T0 ON T1.CardCode = T0.CardCode

WHERE Year(T0.docdate)=2011 AND T1.CardType = 'C'

) S

PIVOT (SUM(DocTotal) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

ON P.[Acct#] = T.CardCode

INNER JOIN dbo.OSLP U ON U.SlpCode = T.SlpCode

WHERE T.CardType = 'C'

ORDER BY T.CardCode

[Former Member]

Hi Mike,

Try:

SELECT T.CardCode 'Acct#',T.CardFName,U.SlpName 'Sales Rep',T.CardName 'Company', T.Address,T.City,
T.ZipCode,T.State1,
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM dbo.OCRD T
LEFT JOIN (SELECT T1.CardCode as [Acct#],
T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.OCRD T1
LEFT JOIN dbo.OINV T0 ON T1.CardCode = T0.CardCode
LEFT JOIN dbo.OSLP T2 ON T2.SlpCode = T0.SlpCode
WHERE Year(T0.docdate)=2011 AND T1.CardType = 'C' ) S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ON P.Acct# = T.CardCode
INNER JOIN dbo.OSLP U ON U.SlpCode = T.SlpCode
WHERE T.CardType = 'C' AND T.QryGroup2='Y'
ORDER BY T.CardCode

Thanks,

Gordon