on 04-03-2012 12:59 PM
Dear Members,
I need query for sales analysis report that involves two years.As Our company fiscal year starts from April to March(i.e.,April 2011-March2012).I need query for state wise sales analysis report from march to april rather than January to December.It is prefer to have a query than that of std reports available in SAP.
With Regards,
Revathy
Hi Revathy,
Try:
SELECT P.[STATE],
[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 T1.State as [State],
T0.DocTotal as [DocTotal],
MONTH(T0.docdate) as [month]
FROM dbo.OINV T0
INNER JOIN dbo.INV12 T1 ON T1.DocEntry=T0.DocEntry
WHERE T0.docdate BETWEEN '04/01/2011' AND '03/31/2011'
UNION
SELECT 1.State as [State],
-T0.DocTotal as [DocTotal],
MONTH(T0.docdate) as [month]
FROM dbo.ORIN T0
INNER JOIN dbo.RIN12 T1 ON T1.DocEntry=T0.DocEntry
WHERE T0.docdate BETWEEN '04/01/2011' AND '03/31/2011') S
PIVOT (SUM(DocTotal) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[State]
Thanks, Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Check this for a start and see if you can modify and bring it to ur requirement:
http://scn.sap.com/thread/1897769
Kind Regards,
Jitin
SAP Business One Forum Team
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
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.