CODEWITHSUNDEEP
sqlvisual-studio-2010parameters
AceAlfred
AceAlfred

Reputation: 1191

SQL query for displaying sales for two different months


I was wondering what method is most used when creating a query that displays sales for two different months (selected in the parameter).

My database looks something like this:

Posting Date    Company      Size    Sales
01/01/2011      Microsoft    1000    900

I already have a parameter where "year month" is selected. What I want is to have two parameters so that I can compare the sales in "year month" side by side in Microsoft Visual Studio.

So the query should have two parameters, @PostingDate1 and @PostingDate2

Thanks for any help!

--UPDATE--

Trying to make this more understandable.
The two parameters to select from will be "year month"
So that the result table will look like this when "year month" is selected for parameter 1: january 2011, and parameter 2: february 2011 (doesn´t matter what months are selected, just that the results will show the different months) 

Company     Size     Sales1    Sales2
Microsoft   1000     100       200

That is if sales for january 2011 was 100
and sales for february 2011 was 200

Upvotes: 1

Views: 1600

Answers (2)

AceAlfred
AceAlfred

Reputation: 1191

My answer:

I ended up using "UNION", don´t know whether this is more appropriate, but it got rid of the redundant data from using "CROSS JOIN".

SELECT 
A.Company, A.Size, SUM(A.Sales) as Sales1, SUM(B.Sales2)
FROM
(
(SELECT Company, Size, Sales as Sales, 0 as Sales2
FROM Sales
WHERE Posting date = @PostingDate1) AS A
UNION
(SELECT Company, Size, 0 as Sales, Sales as Sales2
FROM Sales
WHERE Posting date = @PostingDate2)
) AS B
)
GROUP BY 
A.Company, A.Size

Upvotes: 0

Lamak
Lamak

Reputation: 70678

I think that you want to do a CROSS JOIN, but I'm not completely sure that I understood your question. If the results of your query are onle ONE row, then I recommend a CROSS JOIN, of not, then its probably better not to use it. It should be something like this:

SELECT A.[Posting Date] [Posting Date 1], A.Company Company1, A.Size Size1, A.Sales Sales1,
       B.[Posting Date] [Posting Date 2], B.Company Company2, B.Size Size2, B.Sales Sales2
FROM (SELECT [Posting Date], Company, Size, Sales
      FROM YourTable
      WHERE [Posting Date] = @PostingDate1) A
CROSS JOIN (SELECT [Posting Date], Company, Size, Sales
            FROM YourTable
            WHERE [Posting Date] = @PostingDate2) B

Upvotes: 1

Related Questions