Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Clemmy
Starting Member
2 Posts |
 Posted - 2009-09-28 : 11:03:40
|
| Hi, i'm trying to compare the results of sales figures, last month and the current month, in a bar chart. I have the results in two datasets, using the same query for both with the only difference being (month(getdate())-1 on the query for last months sales. This is the querySELECT TOP (5) ITEMID, SUM(QTY) AS [Total Qty], MONTH(INVOICEDATE) AS Month, YEAR(INVOICEDATE) AS YearFROM CUSTINVOICETRANSGROUP BY ITEMID, MONTH(INVOICEDATE), YEAR(INVOICEDATE)HAVING (MONTH(INVOICEDATE) = MONTH(GETDATE())) AND (YEAR(INVOICEDATE) = YEAR(GETDATE()))ORDER BY [Total Qty] DESCWhat i am returning is an item list and a sum qty of those items sold, sorted by qty,. and from this list I select the top 10 by qty. Yes the top 10 items can be different on both months, most of the items are the same month after month, but a few change. both of these datasets work fine, but i can only use one dataset in a graph. ideally i would like a bar chart to show the results, but i can't create one dataset to contain both of these results, the top 10 for each month. Can anyone give me a point in the right direction please? thanks for any help you can give, this is driving me nuts! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-28 : 11:12:28
|
select ... -- your first selectunion allselect ... -- your second selectgives ONE result set from both queries.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Clemmy
Starting Member
2 Posts |
Posted - 2009-09-29 : 06:37:03
|
| Fantastic, thank you very much. I had to change the select statments to views to get it to work correctly, then use union on the views. It now works!! i'm very grateful for that tip. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-29 : 07:02:00
|
| why use views? you can use union directly on tables itself |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-29 : 07:09:10
|
quote:
Originally posted by visakh16
why use views? you can use union directly on tables itself
Becuase it has Order by clause?It is better if OP posted the codeMadhivananFailing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-29 : 07:12:58
|
quote:
Originally posted by Clemmy
Fantastic, thank you very much. I had to change the select statments to views to get it to work correctly, then use union on the views. It now works!! i'm very grateful for that tip.
welcome  glad that I could help.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-29 : 07:41:51
|
quote:
Originally posted by madhivanan
quote:
Originally posted by visakh16
why use views? you can use union directly on tables itself
Becuase it has Order by clause?It is better if OP posted the codeMadhivananFailing to plan is Planning to fail
seems like that might be the case. nice guess |
|
|
|
|
|
|
|
|
Comparing two results in a graph