Hello SAP Gents,
I need the same report could you fix Sales Employee Team Wise Item Group Sale instead of Customer BP Name
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.CARDNAME,
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '100') as 'Hygienic & Personal',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '101') as 'Commodities',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '102') as 'Bevrages',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '103') as 'Spices',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '104') as 'Grocery & Staples',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '105') as 'Cooking Oil',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '106') as 'Biscuits Snacks',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '107') as 'Breakfast & Pasta',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '108') as 'Smallware',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '109') as 'Baby Cereals',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '110') as 'Confectionery',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '111')as 'Dairy Coffee Tea',
(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '112') as 'Misc'
FROM
OINV T0 where T0.docdate between @FromDate and @ToDate
GROUP BY
T0.CARDNAME
Regards,
Mohammd Firoz