cancel
Showing results for 
Search instead for 
Did you mean: 

Query for Monthly Total Sales Order report.

Former Member
0 Kudos

Dear Experts,

I have tried the following query to get the Total Sales order created on month wise.

I want this sort of all Sales Documents in single query. For Sales Order, Delivery, AR Invoice.

I am getting the following error

"1). [Microsoft][SQL Native Client][SQL Server]Column 'OPOR.DocDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2). [Microsoft][SQL Native Client][SQL Server]No column was specified for column 1 of 'S'. 3). [Microsoft][SQL Native Client][SQL Server]The column prefix 't0' does not match with a table name or alias name used in the query. 4). [Microsoft][SQL Native Client][SQL Server]Cannot find either column "P" or the user-defined function or aggregate "P.Count", or the name is ambiguous. 5). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared."

Query:

Select P.Count(t0.docentry),

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

From

(SELECT Count(t0.docentry), month(t0.docdate) as 'Month'

FROM OPOR T0

WHERE T0.[DocDate]  >=[%0] AND  T0.[DocDate] <=[%1]) S

Pivot (Count(t0.docentry) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

How to Fix this ?

Regards,

Dwarak

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

OPOR is related to Purchase order. Use ORDR table to get your desired result.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

For Sales Order

Hi ,

Try this..Its working on my end

SELECT

  [4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec],

[1] as [Jan],

[2] as [Feb],

[3] as [Mar]

FROM(SELECT Count(t0.docentry) as 'Docentry', month(t0.docdate) as 'Month'

FROM ORDR T0

WHERE T0.[DocDate]  >=[%0] AND  T0.[DocDate] <=[%1]

Group By t0.docdate) S

  PIVOT  (count(S.Docentry) FOR [month] IN

([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

Hope Helpful

Thanks

Taruna

Try and kindly close the thread.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

The above query is not required for me.

Thanks,

Nagarajan

Former Member
0 Kudos

This reply was for Dwarakanath P. I asked him to kindly check and close this thread

Former Member
0 Kudos

Hi,

I created a sales order. But after creation of sales order hit enter button. it is  showing  one message information. That information is AHMED ROCKS.

HOW SHOULD  I RESOLVE THE  ISSUE.

could you please any one tell me how to resolve that issue.

KennedyT21
Active Contributor
0 Kudos

Hi Former Member,

You should open a new tread...

Regards

Kennedy

Former Member
0 Kudos

Hi,

I am getting the wrong data. Kindly find in the below screen shot.

As per your query for Apr = 12 and in my simple ORDR query from 01.04.2012 to 30.04.2012 it going more than 12 , which is actually 53.

What could be the issue ?

KennedyT21
Active Contributor
0 Kudos

HI

try This

SELECT  

[1]  as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

From (SELECT Count(t0.docentry) as Docentry, month(t0.docdate) as 'Month' FROM ordr T0

group by month(t0.docdate)

) p    Pivot  (count(docentry) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

KennedyT21
Active Contributor
0 Kudos

Hi Dwarakanath P

Try This ignore Previous one

SELECT  

[1]  as [Jan],[2] as [Feb],[3] as [Mar],

[4] as [Apr],[5] as [May],[6] as [Jun],

[7] as [Jul],[8] as [Aug],[9] as [Sep],

[10] as [Oct],[11] as [Nov],[12] as [Dec]

From (SELECT  t0.docentry , month(t0.docdate) as 'Month' FROM ordr T0

) p   

Pivot  (count(p.docentry) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

Regards

Kennedy

Former Member
0 Kudos

Hi Kennedy,

Still its not correct.Kindly find in the below screen shot.

You can see data in in Nov and Dec and all.

KennedyT21
Active Contributor
0 Kudos

Hi Dwarakanath P

Try This ignore Previous one

SELECT  

[1]  as [Jan],[2] as [Feb],[3] as [Mar],

[4] as [Apr],[5] as [May],[6] as [Jun],

[7] as [Jul],[8] as [Aug],[9] as [Sep],

[10] as [Oct],[11] as [Nov],[12] as [Dec]

From (SELECT  t0.docentry , month(t0.docdate) as 'Month' FROM ordr T0

) op   

Pivot  (count(op.docentry) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

Regards

Kennedy

Former Member
0 Kudos

Try:

SELECT 

[1]  as [Jan],[2] as [Feb],[3] as [Mar],

[4] as [Apr],[5] as [May],[6] as [Jun],

[7] as [Jul],[8] as [Aug],[9] as [Sep],

[10] as [Oct],[11] as [Nov],[12] as [Dec]

From (SELECT  t0.docentry , month(t0.docdate) as 'Month' FROM ordr T0 WHERE Year(T0.DocDate)='2012'

) S  

Pivot  (count(S.docentry) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Need to include the posting year in order to get current year. Please try this  and this is working for me.

SELECT 

[1] as Jan, [2] as Feb, [3] as Mar, [4] as Apr, [5] as May, [6] as June, [7] as July, [8] as Aug, [9] as Sept, [10] as Oct, [11] as Nov, [12] as Dec

From
(Select  T0.DocEntry  as Total, month(T0.[DocDate]) as Month

From dbo. ORDR T0 where  T0.[DocStatus] NOT IN ('N') and year(T0.[DocDate]) = 2012 )S

Pivot
(count(Total) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Regards,

Nagarajan

Former Member
0 Kudos

Hi Taruna,

Thanks for this query..

Answers (7)

Answers (7)

KennedyT21
Active Contributor

Hi Dwarakanath.....

Try This

SELECT  

[1]  as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

From (SELECT Count(t0.docentry) as Docentry, month(t0.docdate) as 'Month' FROM ordr T0

WHERE T0.DocDate>='[%0]' and T0.DocDate<='[%1]'

group by month(t0.docdate)

) p    Pivot  (count(docentry) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

Regards

Kennedy

Former Member
0 Kudos

Query For Monthly Total of Business Partner

SELECT p.cardcode,p.CardName,

[1] as [Jan],[2] as [Feb],[3] as [Mar], [4] as [Apr],[5] as [May],[6] as [Jun],[7] as [Jul],[8] as [Aug],[9] as [Sep],[10] as [Oct],[11] as [Nov],[12] as [Dec]

FROM (SELECT t0.cardcode,t0.CardName ,(T0.DocTotal) as DocTotal,MONTH(T0.Docdate)as month

FROM dbo.ORDR T0

WHERE year(T0.DocDate)='2014'

) S

  PIVOT  (sum(DocTotal) FOR [month] IN

([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

ORDER BY p.cardcode,p.CardName

Former Member
0 Kudos

Dear Gordon,

Please recommend me a query to develop a monthly sales report for Invoices as follows

Customer Name   Jan   Feb   Mar April May June

Former Member
0 Kudos

HI Junaid Ahmad,

You raise your query in the new thread to answer .

Regards,

Dwarak

Former Member
0 Kudos

Hi,

  Try this.... It will show the customer wise sales order count...

SELECT p.cardcode,p.CardName,

  [4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec],

[1] as [Jan],

[2] as [Feb],

[3] as [Mar]

FROM (SELECT t0.cardcode,t0.CardName ,(T0.docentry) as Docentry,MONTH(T0.Docdate)as month

FROM dbo.ORDR T0

WHERE year(T0.DocDate)='2012'

) S

  PIVOT  (count(Docentry) FOR [month] IN

([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

ORDER BY p.cardcode,p.CardName

Hope Helpful....

Regards,

Priya

Former Member
0 Kudos

Hi SD experts,

I created a sales order. But after creation of sales order hit enter button. it is  showing  one message information. That information is AHMED ROCKS.

HOW SHOULD  I RESOLVE THE  ISSUE.

could you please any one tell me how to resolve that issue.

Former Member
0 Kudos

Can you paste the screen Shot ?

Former Member
0 Kudos

Hi Dwarak,

The P.Count(t0.docentry) is not right SQL in your query. Do you want to get the total count?

Thanks,

Gordon

Former Member
0 Kudos

Dear Gordon,

Yes.

Former Member
0 Kudos

Hi,

Try this...

SELECT P.Docentry,

  [4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec],

[1] as [Jan],

[2] as [Feb],

[3] as [Mar]

FROM (SELECT (T0.docentry) as Docentry,MONTH(T0.Docdate)as month

FROM dbo.opor T0

WHERE T0.DocDate>='[%0]' and T0.DocDate<='[%1]'

) S

  PIVOT  (count(Docentry) FOR [month] IN

([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

ORDER BY P.Docentry

Former Member
0 Kudos

Hi ,

Try this..Its working on my end

SELECT

  [4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec],

[1] as [Jan],

[2] as [Feb],

[3] as [Mar]

FROM(SELECT Count(t0.docentry) as 'Docentry', month(t0.docdate) as 'Month'

FROM OPOR T0

WHERE T0.[DocDate]  >=[%0] AND  T0.[DocDate] <=[%1]

Group By t0.docdate) S

  PIVOT  (count(S.Docentry) FOR [month] IN

([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

Hope Helpful

Thanks

Taruna