How to get Monthly Sales Analysis in DevEx Reports and Sql

Question

4.00/5 (1 vote)

See more:

How to retreive Monthly Sales Analysis Order By Amount And Qty.

I have Written this Query in Sql but I am getting Sum(Qty) using Pivot. I want only Qty

and Amount Not Sum.

SQL

SELECT *
FROM (SELECT QTY,AMOUNT,PRODUCT_NO,AC_NO,
       DATENAME(MONTH, DATE_CREATED) [Month]
      FROM invINVOICEDETAILS where DATENAME(MONTH, DATE_CREATED)  in ('January','February','March','April','May',
    'June','July','August','September','October','November',
    'December')
      GROUP BY QTY,AMOUNT,PRODUCT_NO,AC_NO,
      DATENAME(MONTH, DATE_CREATED)) AS MontlySalesData
      PIVOT(sum([QTY])
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

I want to retreive Qty,Product_No,AC_NO and Amount According to the sales in months in the year. In Reports I want to Display All Months i.e January to December and also print Qty and Amount based on Product_no according to sales in Prooper Months.

For Example: I have sold two products in the month of january.Similarly I sold 3 products in the month of Febrauary.

Now I want to display those Qty and Amount For the months of January and Febrauary.

Similarly I want to Display All Months in the report with Null values.

Here I want to display all columns from january to December.

Can u please suggest me right query for this.

Posted 15-Feb-15 22:13pm

TarunKumarSusarapu

Updated 16-Feb-15 1:19am

v3

Add a Solution

Comments

Wendelius 16-Feb-15 4:42am

If a single month contains several invoices, what do you mean when you say that you want only Qty, not SUM(Qty)? For example if you have 2 invoices for January Qty 2 and 5, what should be shown in January?

TarunKumarSusarapu 16-Feb-15 4:47am

I want to retreive Qty,Product_No,AC_NO and Amount According to the sales in months in the year. In Reports I want to Display All Months i.e January to December and also print Qty and Amount based on Product_no according to sales in Prooper Months.

Herman<T>.Instance 16-Feb-15 5:00am

When pivoting each column mus be unique. You cant have 2 columns January. So create a column January-QTY and January-AMT. Then you create columns for both values.

TarunKumarSusarapu 16-Feb-15 5:12am

Can u please suggest me another way of getting this because i didn't get u

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-02-16T00:58:00

If I understood you correctly, you're searching for a solution where the amount of columns would vary based on the products sold each month. If this is true, then SQL is not the place where you should be doing this. All formatting should be done on the calling side, not in the queries.

Without knowing anything more about your environment, I suggest that you query on the level of detail you wish to show data on your report and let the reporting tool handle the visualization.