Skip to Content
author's profile photo Former Member
0
Former Member

Query help needed (OINV - ORIN)

Posted on Aug 22, 2016 at 09:13 AM 311 Views

Hello,

i need help with a query. I want to have the Item quantities from Invoices - quantities from creditnotes

This is my query:


Select SUM
(SELECT T1.[ItemCode],  SUM(T1.[Quantity]) 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE T0.[DocDate] between '2015-10-01' and '2016-09-30' 
and T1.Quantity > '0' Group By T1.ItemCode)

-

(SELECT T2.[ItemCode],  SUM(T2.[Quantity])*-1 
FROM ORIN T3  
INNER JOIN RIN1 T2 ON T3.[DocEntry] = T2.[DocEntry] 
WHERE T3.[DocDate] between '2015-10-01' and '2016-09-30'
 and T2.Quantity > '0' Group By T2.ItemCode)

Where T1.itemCode = T2.ItemCode

Can you help me please ?

thanks a lot

Markus

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Kennedy T
    Kennedy T
    Posted on Aug 22, 2016 at 09:38 AM
    3

    Try This

    SELECT CardCode,

    CardName,

    ItemCode,

    ItemName,

    SUM([QTY]) 'QTY',

    SUM([TOTAL]) 'Sales'

    FROM (

    SELECT T0.CardCode,

    T0.CardName,

    T1.ItemCode,

    T2.ItemName,

    T1.Quantity [QTY],

    T1.LineTotal [TOTAL]

    FROM OINV T0

    INNER JOIN INV1 T1

    ON T0.DocEntry = T1.DocEntry

    INNER JOIN OITM T2

    ON T2.ItemCode = T1.ItemCode

    UNION ALL

    SELECT T0.CardCode,

    T0.CardName,

    T1.ItemCode,

    T2.ItemName,

    -T1.Quantity [QTY],

    -T1.LineTotal [TOTAL]

    FROM ORIN T0

    INNER JOIN RIN1 T1

    ON T0.DocEntry = T1.DocEntry

    INNER JOIN OITM T2

    ON T2.ItemCode = T1.ItemCode

    ) S

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Aug 22, 2016 at 10:00 AM

      Hi again,

      thanks to Kennedy... your query was very helpful for my issue.

      here is my query to get the quantities:

      SELECT S.ItemCode, SUM([QTY]) 'QTY'


FROM  
(
Select T1.ItemCode, SUM(T1.[Quantity]) [QTY] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]  
WHERE T0.[DocDate] between '2015-10-01' and '2016-09-30' and T1.Quantity > '0'
Group By T1.ItemCode

UNION ALL

Select T1.Itemcode,SUM(T1.[Quantity])*-1 [QTY] FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]  
WHERE T0.[DocDate] between '2015-10-01' and '2016-09-30' and T1.Quantity > '0'
Group By T1.ItemCode
)S

group by S.ItemCode
  • author's profile photo Logesan S
    Logesan S
    Posted on Aug 22, 2016 at 09:30 AM
    0

    Hi,

    Use below Query..

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],

    T1.[Dscription], T1.[Quantity], T1.[LineTotal] , T0.[VatSum],T0.[RoundDif],

    T0.[DocTotal],T4.ExpnsName ,T3.[LineTotal] AS 'Freight',T0.Comments FROM OINV T0 Left Outer JOIN INV1

    T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN INV3 T3 ON

    T0.[DocEntry] = T3.[DocEntry] Left Outer JOIN OEXD T4 ON T3.[ExpnsCode] = T4.[ExpnsCode]

    WHERE T0.[DocDate] >=[%0] AND

    T0.[DocDate] <=[%1]

    UNION

    SELECT T2.[DocNum], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode],

    T3.[Dscription], T3.[Quantity], -(T3.[LineTotal]), -(T2.[VatSum]), -(T2.[RoundDif]),

    -(T2.[DocTotal]),T5.ExpnsName ,-(T4.[LineTotal]),T2.Comments FROM [dbo].[ORIN] T2

    Left outer JOIN RIN1 T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT OUTER JOIN RIN3 T4

    ON T2.[DocEntry] = T4.[DocEntry] Left Outer JOIN OEXD T5 ON T4.[ExpnsCode] = T5.[ExpnsCode]

    WHERE T2.[DocDate] >=[%0] AND T2.[DocDate] <=[%1]

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.