One Selection criteria have to add in Query report

[Former Member]

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 .

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thank u sir