Skip to Content
0
Jun 27, 2020 at 08:06 PM

Sales Employees Name Fix

41 Views

Dear SAP Gents,

Could you Fix Sales Employee Name in my below query afer Sales Employee Code.

Declare @FromDate Datetime Declare @ToDate Datetime Set @FromDate = (select min(T0.DocDate) from OINV T0 Where T0.Docdate >= [%0]) Set @ToDate = (Select max(T0.Docdate) from OINV T0 Where T0.DocDate <=[%1]) SELECT T0.[SlpCode] AS "Team Code", (select SUM(ISNULL(GrssProfit,0))/SUM(ISNULL(LineTotal,1) )*100 from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate) As "% GP", (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate) As"Gross Sale", (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '102') as 'Bevrages', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '106') as 'Biscuits Snack & Conf', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '107') as 'Breakfast & Dairy', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '104') as 'Grocery & Staples', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '100') as 'Hygienic & Personal', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '108') as 'Smallware', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '109') as 'Cereals Cofee & Tea', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '101') as 'Commodities', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '110') as 'Confectionery', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '105') as 'Cooking Oil', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '111')as 'Dairy Coffee Tea', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '112') as 'Misc', (select sum(Gtotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv. slpcode=T0.SlpCode and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '103') as 'Rice Lentil & Spices' FROM OINV T0 where T0.docdate between @FromDate and @ToDate GROUP BY T0.SlpCode

Regards,

Mohammad Firoz