Skip to Content
0
Former Member
Oct 16, 2012 at 08:35 AM

Query for Monthly Total Sales Order report.

2814 Views

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