By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Sql sequence

P: 1
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.todate) 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
Nov 12 '06 #1
Share this Question
Share on Google+
3 Replies


100+
P: 1,646
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.todate) 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.todate) AS CountOftodate
FROM ESM
GROUP BY ESM.todate
ORDER BY todate

Good luck
Nov 12 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDate.Date, Count(ESM.todate) AS CountOftodate
  2. FROM tblDates LEFT JOIN ESM ON tblDates.Date=ESM.todate
  3. GROUP BY tblDate.Date
  4. ORDER BY tblDate.Date
Nov 12 '06 #3

PEB
Expert 100+
P: 1,418
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..
Nov 12 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.