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 |
|
bilal_fazlani
Starting Member
3 Posts |
 Posted - 2012-02-21 : 04:56:19
|
[code]SELECT convert(varchar,dateadd(month,datediff(month,0,inv_date),0),101) AS 'months with date format', sum(inv_amount) AS 'business' FROM invoices invoices WHERE cust_id=13 GROUP BY cust_id,dateadd(month,datediff(month,0,inv_date),0) ORDER BY dateadd(month,datediff(month,0,inv_date),0)[/code]this is my query for displaying a monthly business graph.and this is what I get :  as you may notice, the months where there was no transaction are missing so the chart looks incomplete.can anyone help me with this ?:::Be::RED::: |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-02-21 : 07:07:12
|
| join to a calendar table for the dates and left join to your invoices table. |
 |
|
|
bilal_fazlani
Starting Member
3 Posts |
Posted - 2012-02-21 : 07:24:05
|
| @RickD : thanks buddy , but do I have to create a calender table ? OR is there any feature in MS SQL 2008 for calender table ...?manually entering all the dates in a table seems a little weird..:::Be::RED::: |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-02-21 : 12:01:08
|
| There are functions in the script library to create one. If you are using SSAS you can have a time dimension in SQL 2008, but otherwise, create it, it take seconds to create and is so worthwhile having. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 14:34:56
|
quote:
Originally posted by bilal_fazlani
@RickD : thanks buddy , but do I have to create a calender table ? OR is there any feature in MS SQL 2008 for calender table ...?manually entering all the dates in a table seems a little weird..:::Be::RED:::
see a calendar table function herehttp://visakhm.blogspot.com/2010/02/generating-calendar-table.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
bilal_fazlani
Starting Member
3 Posts |
Posted - 2012-02-22 : 13:13:11
|
| that's very helpful thank u :):::Be::RED::: |
|
|
|
|
|
|
|
|
sql monthly data