ITEM PROPERTY SALES QUERY

[former_member752097]

HI EXPERTS,

PLEASE HELP ME WITH THE QUERY,

I NEED SALES AMOUNT FROM A/R AMOUNT WITH ITEM PROPERTY AND SALES EMPLOYEE

IT SHOULD HAVE DATE RANGE OPTION. PLEASE HELP WITH THIS

[former_member4944 · Accepted solution]

Hi iemebco

Try This Query,

/*SELECT FROM [dbo].[Oinv] T0*/

declare @fromdate as datetime

/* WHERE */

set @fromdate = /* T0.DocDate */'[%0]'

/*SELECT FROM [dbo].[Oinv] T1*/

declare @tilldate as datetime

/* WHERE */

set @tilldate = /* T1.DocDate */'[%1]'

/*SELECT FROM [dbo].[OSLP] T2*/

declare @SlpName AS Varchar(50)

/* WHERE */

set @SlpName = /* T2.SlpName */'[%2]'

/*SELECT FROM [dbo].[OITG] T3*/

declare @ItmsGrpNam AS Varchar(50)

/* WHERE */

set @ItmsGrpNam =/* T3.ItmsGrpNam */ '[%3]'

Select DISTINCT

(Select Convert(nvarchar,SeriesName) from NNM1 where A.Series=Series)[Series],

A.DocNum[Invoice No],A.NumAtCard[Reference No],

A.Cardname[Party Name(Debtor/Creditor)],H.SlpName[SalesPerson],d.SalUnitMsr[KG],I.GroupName,

A.DocDate[Invoice Date],f.PymntGroup,E.ItmsGrpNam,B.Dscription[Item Description],

B.Quantity[Qty],B.Price[Unit Price],(B.Quantity*B.Price) AS LineTotal ,A.DocTotal[Net Amount]

from OINV A

Left Join INV1 B ON A.DocEntry=B.DocEntry

Left Join OCRD C ON C.CardCode=A.CardCode

left join crd1 g on g.CardCode=c.CardCode

Left Join OITM D ON D.ItemCode=B.ItemCode

Left Join OITB E ON E.ItmsGrpCod=D.ItmsGrpCod

left join octg f on f.GroupNum=a.GroupNum

Left Join OCRG I on I.GroupCode=C.GroupCode

LEFT JOIN OSLP H ON H.SlpCode=A.SlpCode

LEFT JOIN OITG J ON CASE WHEN ISNULL(D.QryGroup1, '') = 'Y' THEN 1 WHEN ISNULL(D.QryGroup2, '') = 'Y' THEN 2 WHEN ISNULL(D.QryGroup3, '') = 'Y' THEN 3 WHEN ISNULL(D.QryGroup4, '') = 'Y' THEN 4 WHEN ISNULL(D.QryGroup5, '') = 'Y' THEN 5 WHEN ISNULL(D.QryGroup6, '') = 'Y' THEN 6 WHEN ISNULL(D.QryGroup7, '') = 'Y' THEN 7 WHEN ISNULL(D.QryGroup8, '') = 'Y' THEN 8 WHEN ISNULL(D.QryGroup9, '') = 'Y' THEN 9 WHEN ISNULL(D.QryGroup10, '') = 'Y' THEN 10 WHEN ISNULL(D.QryGroup11, '') = 'Y' THEN 11 WHEN ISNULL(D.QryGroup12, '') = 'Y' THEN 12

END = ItmsTypCod

where

(A.DocDate>=@fromdate or @fromdate='') and (A.DocDate<=@tilldate or @tilldate='' )

And A.CANCELED ='N' and C.CardType='C' AND G.AdresType='B' AND B.Quantity<>'0' and (H.SlpName=@SlpName or @SlpName='' )

and (J.ItmsGrpNam=@ItmsGrpNam or @ItmsGrpNam='')

Order By DocNum

Regards

Abdul Basith

[former_member752097 · Accepted solution]

THANKS FOR YOUR HELP BROTHER,

BUT IM GETTING THIS RESULT

[former_member4944 · Accepted solution]

Try This...

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'