Skip to Content
avatar image
Former Member

One Selection criteria have to add in Query report

Hii Experts,

In cost of sales query report i have to  add item group name as selection criteria ,so please help me

below is query

Select Distinct x.[ItemCode],x.[Dscription],x.[DocDate],x.[CalcPrice] as [Cost of Purchase],

(sum(x.[LineTotal])/ sum(x.[Quantity])) as [cost of sales],

(x.[CalcPrice]/(sum(x.[LineTotal])/ sum(x.[Quantity])) )*100 as[Percentage %] from

(SELECT T0.[ItemCode],T0.[Dscription],T0.[Quantity], T0.[LineTotal],T4.[DocNum],T3.[CalcPrice] ,

T3.[DocDate], T3.[InQty],T3.[TransSeq] ,T3.[CardCode] FROM DLN1 T0  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] INNER JOIN OINM T3 ON T1.[ItemCode] = T3.[ItemCode]

  INNER JOIN ODLN T4 ON T0.[DocEntry] = T4.[DocEntry] WHERE T4.[CardCode] = T3.[CardCode] and

   T0.[ItemCode] = T1.[ItemCode] and  T4.[DocDate] = T3.[DocDate]  ) x INNER  JOIN [dbo].[OINM] T0

   ON  x.[ItemCode] = T0.[ItemCode]

INNER  JOIN [dbo].[OITM] T1  ON  T1.[ItemCode] = T0.[ItemCode]   INNER  JOIN [dbo].[OWHS] T2

ON  T2.[WhsCode] = T0.[Warehouse]   WHERE (T0.[TransValue] <> 0  OR

T0.[InQty] <> 0  OR  T0.[OutQty] <> 0  OR  T0.[TransType] = 0 )

and x.[DocDate] BETWEEN [%0] and [%1]

group by x.[ItemCode],x.[Dscription],x.[DocDate],x.[CalcPrice]  ORDER BY x.[ItemCode]

Thank You .

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Oct 29, 2015 at 10:13 AM

    Hi Nagendra,

    Please try this.

    Select Distinct x.[ItemCode],x.[Dscription],x.[DocDate],x.[CalcPrice] as [Cost of Purchase],

    (sum(x.[LineTotal])/ sum(x.[Quantity])) as [cost of sales],

    (x.[CalcPrice]/(sum(x.[LineTotal])/ sum(x.[Quantity])) )*100 as[Percentage %] from

    (SELECT T0.[ItemCode],T0.[Dscription],T0.[Quantity], T0.[LineTotal],T4.[DocNum],T3.[CalcPrice] ,

    T3.[DocDate], T3.[InQty],T3.[TransSeq] ,T3.[CardCode]

    FROM DLN1 T0  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

    INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] INNER JOIN OINM T3 ON T1.[ItemCode] = T3.[ItemCode]

      INNER JOIN ODLN T4 ON T0.[DocEntry] = T4.[DocEntry] WHERE T4.[CardCode] = T3.[CardCode] and

       T0.[ItemCode] = T1.[ItemCode] and  T4.[DocDate] = T3.[DocDate]  ) x INNER  JOIN [dbo].[OINM] T0

       ON  x.[ItemCode] = T0.[ItemCode]

    INNER  JOIN [dbo].[OITM] T1  ON  T1.[ItemCode] = T0.[ItemCode]   INNER  JOIN [dbo].[OWHS] T2

    ON  T2.[WhsCode] = T0.[Warehouse] INNER JOIN OITB T7 ON T1.[ItmsGrpCod] = T7.[ItmsGrpCod]

    WHERE (T0.[TransValue] <> 0  OR

    T0.[InQty] <> 0  OR  T0.[OutQty] <> 0  OR  T0.[TransType] = 0 )

    and x.[DocDate] BETWEEN [%0] and [%1] and T7.[ItmsGrpNam]='[%2]'

    group by x.[ItemCode],x.[Dscription],x.[DocDate],x.[CalcPrice]  ORDER BY x.[ItemCode]

    Thanks,

    Rahul

    Add comment
    10|10000 characters needed characters exceeded