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 |
|
Haz
Starting Member
38 Posts |
 Posted - 2009-01-06 : 22:04:01
|
| Hi there..i am right now create a yearly sale report,this report like thisCustNo CustName Month Amount123 CustA JAN 5.0 Feb 5.0 MAR 5.0 APR 5.0 MAY 5.0 ...this report using parameter which is startdate and enddateStartDate:1/8/07EnDDAte: 1/11/07if enddate is in november, amount will be display zero for dec.I had problem on how to retrieve all amount by month for each customerany idea anyone? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-07 : 10:52:09
|
| you mean you need to display months even if there's no data for it in table? in that case, you need to create a tally table to display all months just in case you dont already have a table for months and then use it as master and take left join with your current table. then use COALESCE() or ISNULL functions to get 0 for months where you've no data (they will return a NULL value) |
 |
|
|
Haz
Starting Member
38 Posts |
Posted - 2009-01-07 : 20:17:43
|
quote:
you mean you need to display months even if there's no data for it in table?
Yes..You mention tally table,is that mean temp table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-09 : 11:47:35
|
quote:
Originally posted by Haz
quote:
you mean you need to display months even if there's no data for it in table?
Yes..You mention tally table,is that mean temp table?
yup...just create a table with all the month name.then use this table and left join onto others to get all month names irrespective of whether they have data. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-23 : 12:56:45
|
quote:
Originally posted by Haz
any body who have the same problem like this post..go to this link..
which link buddy?  |
|
|
|
Haz
Starting Member
38 Posts |
Posted - 2009-03-17 : 03:16:08
|
| Hi here the link..sorry post the wrong link before I delete it...http://weblogs.sqlteam.com/jeffs/archive/2007/09/14/sql-filter-by-month.aspxAnd until right now,I still figure out on this report..Is there any other way to do beside create a temp table..?I just think but I am not sure whether is possible or not..Each time this report generate, it will create a new temp table and after finish+display record this temp table will be deleted..Thanks and Regards,Haz |
|
|
|
collie
Constraint Violating Yak Guru
400 Posts |
Posted - 2009-03-20 : 12:41:26
|
quote:
Originally posted by visakh16
quote:
Originally posted by Haz
quote:
you mean you need to display months even if there's no data for it in table?
Yes..You mention tally table,is that mean temp table?
yup...just create a table with all the month name.then use this table and left join onto others to get all month names irrespective of whether they have data.
visakh, if the date is a datetime field then instead of creating a temp table isn't it possible in the report x-axis properties to select numeric or time-scale values and to set the intervals? Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much. |
|
|
|
|
|
|
Get amount by month