on 10-21-2015 3:40 AM
Good Day Experts,
How can I get Sale report total in a Month and per customer, Example.
C.Code Item1 Item2 Item3
Cus1 20kg 10kg 30kg
Cus2 20kg 10kg 30kg
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Select T0.CardName,T4.ItmsGrpNam,sum(-T1.LineTotal) as Sal from
ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@ORIN+')
group by T0.CardName ,T4.ItmsGrpNam
) src
PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)
that code I will put in query generator?
Hi,
Paste this query
DECLARE @listCol VARCHAR(8000)
DECLARE @Query VARCHAR(8000)
DECLARE @OINV VARCHAR(8000)
DECLARE @ORIN VARCHAR(8000)
DECLARE @From varchar(15)
DECLARE @To varchar(15)
Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)
SET @OINV = isnull( STUFF((Select ',' + convert(varchar(10),Docentry) from OINV
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SET @ORIN = isnull(STUFF((Select ',' + convert(varchar(10),Docentry) from ORIN
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB
ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'
SET @Query = 'Select * from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.Quantity) as Sal from
OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@OINV+')
group by T0.CardName ,T4.ItmsGrpNam
union all
Select T0.CardName,T4.ItmsGrpNam,sum(-T1.Quantity) as Sal from
ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@ORIN+')
group by T0.CardName ,T4.ItmsGrpNam
) src
PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)
declare @column as varchar(max)
declare @Query as varchar(max)
set @column= STUFF((Select '],['+whsname from OWHS FOR XML PATH('')),1,2,'') +']'
set @Query='select * from (select w.ItemCode,m.Itemname ,s.WhsName ,sum(w.OnHand) Stock
from oitw w inner join oitm m on m.itemcode=w.itemcode inner join OWHS s on s.WhsCode=w.WhsCode group by w.ItemCode,m.itemname ,s.WhsName) fg
Pivot (Sum(Stock) for
Whsname in ('+@column+')) AS pvtb'
EXECUTE (@Query)
what is "pvtb"????
Try this,
DECLARE @listCol VARCHAR(8000)
DECLARE @Query VARCHAR(8000)
DECLARE @OINV VARCHAR(8000)
DECLARE @ORIN VARCHAR(8000)
DECLARE @From varchar(15)
DECLARE @To varchar(15)
Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)
SET @OINV = isnull( STUFF((Select ',' + convert(varchar(10),Docentry) from OINV
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SET @ORIN = isnull(STUFF((Select ',' + convert(varchar(10),Docentry) from ORIN
where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)
SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((t1.ItemCode)) from OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry
where t0.DocType='I' ORDER BY '],[' + ltrim((t1.ItemCode)) FOR XML PATH('') ), 1, 2, '') + ']'
SET @Query = 'Select * from (Select T0.CardName,T1.Itemcode,sum(T1.Quantity) as Sal from
OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@OINV+')
group by T0.CardName ,T1.Itemcode
union all
Select T0.CardName,T1.Itemcode,sum(-T1.Quantity) as Sal from
ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)
and t0.docentry not in ('+@ORIN+')
group by T0.CardName ,T1.Itemcode
) src
PIVOT (sum(Sal) for Itemcode IN ('+@listCol+')) AS pvt'
EXECUTE (@Query)
How many items do u have?
If it is not too many, u can use CTE to get sum result, and then pivot the result to achieve this.
Frank
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
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.