Query help -

[Former Member]

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

[former_member325312]

Hi Jan

check out this query i think this will solve ur prob

SELECT T3.[DocDate][Invoice date], T0.[DocNum][SO No], T0.[CardCode], T0.[CardName], T1.[ItemCode],

T1.[Dscription], T1.[Quantity][SO Qty], T2.[CardCode], T2.[CardType],

T2.[CardName], T3.[DocNum][Invoice No], T3.[CardCode], T2.[State1], T2.[IntrntSite],

T3.[CardName], T4.[ItemCode], T4.[Quantity][Invoice Qty], T5.[GroupName]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

INNER JOIN OINV T3 ON T2.CardCode = T3.CardCode

INNER JOIN INV1 T4 ON T3.DocEntry = T4.DocEntry

INNER JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode

GROUP BY T3.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode],

T1.[Dscription], T1.[Quantity], T2.[CardCode], T2.[CardType], T2.[CardName],

T3.[DocNum], T3.[CardCode], T2.[State1], T2.[IntrntSite], T3.[CardName],

T4.[ItemCode], T4.[Quantity], T5.[GroupName]

regards

Jenny

[Former Member]

Hi John,

Do you have Delivery or copy Order to Invoice directly?

Thanks,

Gordon

[JesperB1]

Hi John,

I tried to write something here when I realised that it doesn't really make sense to have the order quantity on the same line. Should there be a relationship to the Invoice? Is it the basedocument? If not, why should it be on the same row and how can you relate it to the Invoice?

Since there is no sum, for it to be on the same row there should logically be a relationship between the Order and Invoice.

The other alternative is to make a union query and display all open order quantities on their own rows.

Let us know.

Jesper