Sql sequence

[ashish73p]

Hi all,

I have one table containing date and varioous other columns.
table
id run todate comment
1 fgdsg 11/05/2006 fghfdg
2 hjghjk 06/05/2006 rgrdtg
3 bhjhgjhgj 11/05/2006 gfhfghgfh

I have written a query to get total according to date. I.e

SELECT ESM.todate, Count(ESM.todat e) AS CountOftodate
FROM ESM
GROUP BY ESM.todate;

O/P

todate CountOftodate
06/05/2006 1
11/05/2006 2


I want the query such that it o/p for each date from min to max todate.
I.e

todate CountOftodate
06/05/2006 1
07/05/2006 0
08/05/2006 0
09/05/2006 0
10/05/2006 0
11/05/2006 2

[willakawill]

Hi all,

I have one table containing date and varioous other columns.
table
id run todate comment
1 fgdsg 11/05/2006 fghfdg
2 hjghjk 06/05/2006 rgrdtg
3 bhjhgjhgj 11/05/2006 gfhfghgfh

I have written a query to get total according to date. I.e

SELECT ESM.todate, Count(ESM.todat e) AS CountOftodate
FROM ESM
GROUP BY ESM.todate;

O/P

todate CountOftodate
06/05/2006 1
11/05/2006 2


I want the query such that it o/p for each date from min to max todate.
I.e

todate CountOftodate
06/05/2006 1
07/05/2006 0
08/05/2006 0
09/05/2006 0
10/05/2006 0
11/05/2006 2

Hi. Well you have done all the hard work already. You just need to add an ORDER BY clause at the end of your SQL statement.

SELECT ESM.todate, Count(ESM.todat e) AS CountOftodate
FROM ESM
GROUP BY ESM.todate
ORDER BY todate

Good luck

[NeoPa]

To do what you want (Mr cheap grass), you will need a table containing all the dates that you want. I can see no way of achieving your required results without this.
Creating such a table could be done with looping code but I think not with simple SQL.

Having the table (tblDates with field Date), you can then use SQL like :-

SELECT tblDate.Date, Count(ESM.todate) AS CountOftodate
FROM tblDates LEFT JOIN ESM ON tblDates.Date=ESM.todate
GROUP BY tblDate.Date
ORDER BY tblDate.Date

[PEB]

To insert the dates in this table use Excel and his auto increment option to increment the dates by 1

When your column with dates is ready, simply do Copy & Paste in the table with dates...

Better is to link directly this sheet as tbldates in your database

:)

If not, as said NeoPa you should write some VB coding..