Help with a query!

[richard_thurlow]

Hi,

I need to put together a query - which if you select a salesperson from OSLP table and a year, so let's say

Salesperson = Richard

Year = 2016

It should return the sales persons invoiced total per month (ex tax) - minus the credits applied in those particular months:

Month	$
July	45000
August	55000
September	60000
October	80000
November	90000
December	45000
January	35000
February	110000
March	90000
April	50000
May	40000
June	20000

At the moment - I have the following to

SELECT sum(Case T0.[DocCur] When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns] else T0.[DocTotalFC]-T0.[VatSumFC] end)  as 'Sale Amount'

FROM OINV T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode

WHERE  T1.[SlpName] = [%0] and  T0.[DocDate] >= [%1] and  T0.[DocDate] <= [%2]

UNION ALL

SELECT sum(Case T0.[DocCur] When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1 else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1 end)  as 'Sale Amount'

FROM ORIN T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode

WHERE  T1.[SlpName] = [%0] and  T0.[DocDate] >= [%1] and  T0.[DocDate] <= [%2]

But - this results in two figures being presented - one for the invoices and the other for credits.

How do I go about combining these to one figure?

Further to this - I have no idea how to achieve my list of months in one column with the invoices in the other.

The reason that I need it to display July through June is that in Australia this is the financial year.

Any help with this would be appreciated.

Regards,

Rick

[Johan_H · Accepted solution]

Hi Rick,

Please give this a try:

/* select * from OSLP sp */

DECLARE @SlpName AS NVARCHAR(50)

DECLARE @YEAR AS INT

SET @SlpName = /* sp.SlpName */ '[%0]'

SET @YEAR = /* sp.empID */ [%1]

SELECT @SlpName

, SUM(X.[Sale Amount]) AS 'Sale Amount'

FROM

(SELECT sum(Case T0.[DocCur]

            When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

            else T0.[DocTotalFC]-T0.[VatSumFC]

           end)  as 'Sale Amount'

FROM OINV T0

     INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

     --INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

     --INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode

WHERE  T1.[SlpName] = @SlpName and  DATEPART(YEAR, T0.[DocDate]) = @YEAR

UNION ALL

SELECT sum(Case T0.[DocCur]

            When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

            else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

           end)  as 'Sale Amount'

FROM ORIN T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

--INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

--INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode

WHERE  T1.[SlpName] = @SlpName and  DATEPART(YEAR, T0.[DocDate]) = @YEAR) X

Regards,

Johan

[richard_thurlow · Accepted solution]

Hi Johan,

Thank you.

OK so this gives me the salespersons name - then the resulting total in the next column.

In the end I need to get July through June to display on each line - then the associated total in the next column - as per my above table.

The issue that I can see that we are going to run into - is that the invoices span two years - so in the case of our current financial year (2015/2016).

Regards

Rick

[Johan_H · Accepted solution]

Hi Rick,

Sorry about that, I suppose I could have read your question a little better

Getting the months is going to be a little trickier, especially because technically the reported period spans multiple years (so in which year is a given month?).

If you always only mean to run the report over a specific financial year, it gets a little easier, because you would always get the same 12 months (only the starting year would change). Is that the case ?

Regards,

Johan

[richard_thurlow · Accepted solution]

Hi Johan,

Yes - it does seem tricky doesn't it!

I suppose if we hard code the month and date (so for example july 15 could be 7,15) and I simply set up a different query for each financial year - then would this make it easier?

Regards

Rick

[Johan_H · Accepted solution]

Hi Rick,

It is not that bad, if the months are the same, then we can "hard code" those, so you get a single query, and you just run it with different year and sales person parameters every time.

So perhaps not too elegant, but please give this a try (parameters are the sales person name, and the year in which the financial year starts😞

/* select * from OSLP sp */

DECLARE @SlpName AS NVARCHAR(50)

DECLARE @YEAR AS INT

SET @SlpName = /* sp.SlpName */ '[%0]'

SET @YEAR = /* sp.empID */ [%1]

SELECT SQ.M

      ,SQ.[Sale Amount]

FROM (

      SELECT X.M

           , SUM(ISNULL(X.[Sale Amount], 0)) AS 'Sale Amount'

      FROM (SELECT '01. July' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 7

            UNION ALL

            SELECT '01. July' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                        end)  as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 7

            UNION ALL

            SELECT '02. August' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 8

            UNION ALL

            SELECT '02. August' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 8

            UNION ALL

            SELECT '03. September' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 9

            UNION ALL

            SELECT '03. September' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 9

            UNION ALL

            SELECT '04. October' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 10

            UNION ALL

            SELECT '04. October' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 10

            UNION ALL

            SELECT '05. November' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 11

            UNION ALL

            SELECT '05. November' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 11

            UNION ALL

            SELECT '06. December' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 12

            UNION ALL

            SELECT '06. December' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR

              and DATEPART(MONTH, T0.[DocDate]) = 12

            UNION ALL

            SELECT '07. January' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 1

            UNION ALL

            SELECT '07. January' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 1

            UNION ALL

            SELECT '08. February' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 2

            UNION ALL

            SELECT '08. February' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 2

            UNION ALL

            SELECT '09. March' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 3

            UNION ALL

            SELECT '09. March' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 3

            UNION ALL

            SELECT '10. April' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 4

            UNION ALL

            SELECT '10. April' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 4

              UNION ALL

            SELECT '11. May' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 5

            UNION ALL

            SELECT '11. May' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 5

            UNION ALL

            SELECT '12. June' AS M

                 , sum(Case T0.[DocCur]

                        When 'AUD' then T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns]

                        else T0.[DocTotalFC]-T0.[VatSumFC]

                       end)  as 'Sale Amount'

            FROM OINV T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 6

            UNION ALL

            SELECT '12. June' AS M

                  ,sum(Case T0.[DocCur]

                        When 'AUD' then (T0.[DocTotal]-T0.[VatSum]-T0.[TotalExpns])*-1

                        else (T0.[DocTotalFC]-T0.[VatSumFC]- T0.[TotalExpFC])*-1

                       end) as 'Sale Amount'

            FROM ORIN T0

                 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

            WHERE T1.[SlpName] = @SlpName

              and DATEPART(YEAR, T0.[DocDate]) = @YEAR + 1

              and DATEPART(MONTH, T0.[DocDate]) = 6) X

      GROUP BY X.M) SQ

Regards,

Johan

[kothandaraman_nagarajan]

Hi,

Please refer below thread and modify query for your requirement,

http://scn.sap.com/thread/3370027

Thanks

[agustin_marcoscividanes]

Hi

you have to use the SQL function PIVOT to get the data as you need.

Kind regards

Agustín Marcos Cividanes