find missing dates - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

find missing dates

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2008-03-28 : 01:41:12

hai,
my table is as follows

ID Date
1 03/01/2007
2 03/02/2007
1 03/03/2008
2 03/03/2008
i want to select missing dates by employee id for a given month.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 03:00:47

There is a function named F_TABLE_DATE here at SQLTeam.

SELECT f.DATE
FROM F_TABLE_DATE('20070101', '20071231') AS f
LEFT JOIN MyTable AS mt ON mt.Date = f.Date
WHERE mt.Date IS NULL

E 12°55'05.25"
N 56°04'39.16"

cool.mugil
Starting Member

32 Posts

Posted - 2008-03-28 : 06:13:32

hai,
thanks for reply.actually how i want my result was,
ID Date
2 03/01/2007
1 03/02/2007
1 03/04/2007
2 03/04/2007 ..so on
how can i do this plz help me.

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 07:12:24

If you just want a recordset structured like that..

SELECT ID, Date FROM Table ORDER BY Date, ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources