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.

Replication/Admin Help

We run a small business with a LAN of 2 pcs, a laptop pc, and a web server that runs Win2k Server with Exchange on it. My pc is used for most of the development and it also houses SQL Server (Developer) 2k. We have SQL Server (Developer) installed on the other network pc as well.

Here's the problem: We sometimes have to take my pc with us to use at different sites (with no internet connectivity at the time). When this happens, any data access on our web site is 'down' because our web data is in the sql server db on my machine. I want to replicate the db on the other pc so that we can take that machine to the different sites. I am having trouble getting this done.

Can someone give me some direction? Is there a good tutorial or a book or whatever somewhere? I am really in need of some sql server admin knowledgebase.

Thanks!!Have you considered merge replication? you could then publish to your network sites from either computer with transactional publications and have the two "master" databases have a merge publication between them. When the machine goes off site, the other will continue to provide data to the site and when the other machine returns, any update made to it can be merged with the local machine.

if this will do the trick, books online should have all you need as far as setting it up goes...|||Have you considered merge replication? you could then publish to your network sites from either computer with transactional publications and have the two "master" databases have a merge publication between them. When the machine goes off site, the other will continue to provide data to the site and when the other machine returns, any update made to it can be merged with the local machine.

if this will do the trick, books online should have all you need as far as setting it up goes...