Skip to Content
0
Former Member
Feb 10, 2012 at 10:04 PM

Quert Adjust For Specific BP Property

20 Views

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