Dear All,

I’m Bharathiraja, having 2 years of experience in SAP B1 functional.

Here i am posting some queries using dynamic pivot.

1.Total Sales Query with respective to customer and Item group.

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.LineTotal) as Sal from

OINV T0 Inner join INV1 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 (‘+@OINV+’)

group by T0.CardName ,T4.ItmsGrpNam

union all

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)

Your Result will be like this

Total Sale.png

2. Total Sales Qty  with respective to customer and Item group

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)

For above query you will get result like attached image

Total Qty.pngI

3.Stock in all the warehouse

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)

For the above query you will get the following image result

Whs Stock.png

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

      1. Taseeb Saeed

        Hi Raja

        could u pls help me on this one

        ————————————————————————————–

        Need query in Month wise consolidate and Dept wise

        This question is Not Answered.(Mark as assumed answered)

        Shahan ShamsGlass

        Hi experts

        i had created a SPT based on HEM1 Table

        pls find the SPT Below

        ————————————————————————————————————————————————————————————–

        ALTER PROCEDURE [dbo].[Employee Vaccation Details based on HEM-1]

        (

        @LeaveFrom DateTime,

        @LeaveTo   DateTime

        )

        AS

        Begin

        SELECT OHEM.empID as ‘Emp SAP ID’,OHEM.U_EMPOLD_ID AS ‘Emp File No’,OHEM.firstName + ‘ ‘ + isNULL(OHEM.middleName,”) + ‘ ‘ + OHEM.lastName AS ‘Employee Name’,

               OUDP.Name AS ‘Department’,OHEM.U_DeptName AS ‘Previous Dept’,OHEM.U_IQProf as ‘Iqama Profession’,OHPS.name as ‘Position’,OCRY.Name as ‘Citizenship’,OHEM.U_IQId as ‘Iqama ID’,

               OHEM.passportNo as ‘Passport No’,OHEM.U_IQExDate as ‘Iqama Exp Date’,HEM1.U_SYear as ‘Year’,[@ABLEAVET].Name as ‘Holiday Type’,

               HEM1.fromDate as ‘Leave Start Date’,HEM1.toDate as ‘Leave End Date’, HEM1.U_ResumeDt AS ‘Resume Date’,

               HEM1.U_TotDays as ‘Total days Leave Alloted’,HEM1.reason as ‘Any Reason’ from OHEM

             

                        inner join OUDP on OHEM.dept=OUDP.Code

                        inner join OHPS on OHEM.position=OHPS.posID

                        inner join OCRY on OHEM.citizenshp=OCRY.Code

                        inner join HEM1 on OHEM.empID=HEM1.empID

                        inner join [@ABLEAVET] on HEM1.U_LeaveID=[@ABLEAVET].Code

        Where OHEM.dept not in (47,49) and HEM1.fromDate between @LeaveFrom and @LeaveTo AND HEM1.reason = ‘Vacation’

        END

        ——————————————————————————————————————————————————————————

        i need this in the below given format

        company name     Type of Actions       Jan   –      Feb    –      Mar     –      Apr        –       May       –       June

        ABCD                   Vaccation                1              0                1           5                     6                    0

                                     Terminiation             5               0              0             5                   10                   15

        ——————————————————————————————————————————————————————-

        EFGH                   Vaccation               0                  1            6              5                   15                   25

                                

                                     Termination           10                5               0             0                   0                     1

        ———————————————————————————————————————————————————————-

        filtering is not needed

        also in the same query i need to attach the Terminated Employees count in the same format

        Regards

        (0) 

Leave a Reply