Skip to Content
0
Former Member
Jul 21, 2010 at 12:33 PM

Query help -

13 Views

Hi all,

I have a query that returns the qty of Items invoiced to a BP for a particular Item ( as user variable) in the last 30 days.

I would like to include the qty on order (if any) from the said BP in the same row... but I'm not sure how.

My query so far is as follows..

SELECT T2.[DocDate], T2.[DocNum], CAST (T1.[Quantity] As INT) As 'Quantity', 
T0.[CardCode], T0.[CardName], T0.[State1], T2.[NumAtCard],  T0.[Phone1], T0.[CntctPrsn], 
T0.[IntrntSite], T3.[GroupName] FROM OCRD T0  INNER JOIN INV1 T1 ON T0.CardCode = T1.BaseCard 
INNER JOIN OCRG T3 ON T0.GroupCode = T3.GroupCode INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry 
WHERE T1.[ItemCode] = [%0] And datediff(dd,T2.[DocDate],getdate()) < 30

Thanks,

John