Report based on a Date

Hi,
I have to create a report for all the business days in a current month. (no matter the records are available or not in the database for the day).
If you have any idea, pls tell me
ThanksCan you be a little more specific? Do you need a separate report for each day, or do you need one report that will show all the days in the month regardless if there's a record?|||Thanks Malleyo!

The second condition in your post is my report.

My report should display all the business days in the current month and the corresponding sum of amounts from a table which contains a date field. The sum is grouped by the date for each day.

The problem here is the table doesn't contain records for all the days upto end of the month. But I have to display all the days. Now I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.

But I think this is not the right way to do this?
Ex report is

Date Amount
6/1 100
6/2 50
6/3 90
6/4 150
6/7
6/8
...
...
...
6/30

Ex. The problem is table contains data upto 6/4. So while generating the report it shows the report upto 6/4. But I need upto 6/30 even though the amount field is empty.

Thanks|||I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.
Joining the 2 tables sounds good to me.

Try using a Group for Date. Make sure that you don't have 'SuppressIfBlank' turned on. Make sure that the query you're running is really returning all the info you need.|||Thanks Malleyo,

One more things, I'm using MySQL as my database. While linking the tables using left outer join, it is not working properly. It returns the result of inner join.

Actually my first table contains all the business days and the second table contains records for some of the days. I linked the table on date field using left outer join. It has supposed to return all the days in the first table regardless of second table. But it 's not. It's returning only the matching records.

Any idea please,|||You may be doing the wrong kind of Join. Unfortunately I don't know a whole lot about Joins, and the knowledge that I do have is for SQL Server 7 (which I'm not sure if uses different syntax than MySQL.

Here's what my Joins typically look like:

SELECT * FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T2.Column1 = T1.Column1

That will return all the fields in Table1 with any that match in Table2 (or NULL if there isn't a match in Table2).|||I'm also new to mysql.

I'm using the same conventions as given in mysql documentation.

My query is

select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date where table2.code = '111'

This is not working

But

select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date and table2.code = '111'

This works but it takes so much time to read the records. sometime just hung up. My table1 contains just 22 records.

While adding as tables, it's not working.|||Do you have indexes on table1.date, table2.date, and table2.code? If not, add the indexes and it should speed it up. If you already have indexes, then I'm out of ideas. You can try posting that question on another forum or search www.google.com or something.