on 12-28-2017 4:42 PM
Hello Folks.
I have a query for sales and i need to add item group property from OITG table. but couldn't find any direct link. I am a novice so really have no idea how to add Item group property in Below query
Select T0.[DocNum],
T0.[DocDate],
Case when T0.[ObjType] = 13 then 'INVOICE' when T0.[ObjType] = 14 then 'CREDIT NOTE' end as 'Type',
T0.[CardCode],
T0.[CardName],
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T0.[DocCur],
T1.[LineTotal] as 'Canadian,
Case when T1.[Rate] = 0 then T1.[LineTotal] else (T1.[LineTotal]/T1.[Rate]) end as 'Equivalent Value',
Case when (T1.[Quantity] = 0 and (T1.[StockPrice]*T1.[Quantity])= 0 ) then T1.[LineTotal] else (T1.[INMPrice]*T1.[Quantity]) end as 'Sales - Net',
(T1.[StockPrice]*T1.[Quantity]) as 'Cost',
T1.[GrssProfit],
Case when (T1.[INMPrice]*T1.[Quantity]) =0 then 0 else (T1.[GrssProfit]/(T1.[INMPrice]*T1.[Quantity]))*100 end as 'GP %age'
FROM
OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE
T0.[CANCELED] = 'N' and
T0.[DocDate] >= [%0] and
T0.[DocDate] <=[%1] and
T1.[LineTotal] <> '0'
Select T0.[DocNum],
T0.[DocDate],
Case when T0.[ObjType] = 13 then 'INVOICE' when T0.[ObjType] = 14 then 'CREDIT NOTE' end as 'Type',
T0.[CardCode],
T0.[CardName],
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T0.[DocCur],
T1.[LineTotal] as 'Canadian',
Case when T1.[Rate] = 0 then T1.[LineTotal] else (T1.[LineTotal]/T1.[Rate]) end as 'Equivalent Value',
Case when (T1.[Quantity] = 0 and (T1.[StockPrice]*T1.[Quantity])= 0 ) then T1.[LineTotal] else (T1.[INMPrice]*T1.[Quantity]) end as 'Sales - Net',
(T1.[StockPrice]*T1.[Quantity]) as 'Cost',
T1.[GrssProfit],
Case when (T1.[INMPrice]*T1.[Quantity]) =0 then 0 else (T1.[GrssProfit]/(T1.[INMPrice]*T1.[Quantity]))*100 end as 'GP %age'
, T3.ItmsGrpNam
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
LEFT JOIN OITG T3 ON CASE WHEN ISNULL(T2.QryGroup1, '') = 'Y' THEN 1 WHEN ISNULL(T2.QryGroup2, '') = 'Y' THEN 2 END = ItmsTypCod
WHERE
T0.[CANCELED] = 'N' and
T0.[DocDate] >= [%0] and
T0.[DocDate] <=[%1] and
T1.[LineTotal] <> '0'
you can join with OITG as per above given example
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Or is there any way i can add this sub query to my above mentioned query ?
select
src.ItemCode,src.ItemName,src.ItmsGrpNam
from
(select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam
from OITM CROSS JOIN OITG
where OITM.QryGroup1 = 'Y' and OITG.ItmsTypCod = 1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.