selecting a date that doesnt exist in table

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

General SQL Server Forums

New to SQL Server Programming

selecting a date that doesnt exist in table

Author

Topic

indieman
Starting Member

12 Posts

Posted - 2006-03-04 : 21:32:47

Hi,

I did some tsql a year or so ago, it wasnt much but it allows me to do most things i need to do in my job.

I have created a new database for bookings, each booking has all the relevant details and also the date that it will occur. im trying to create a script that will look for the 10 soonest dates that there is no booking for. at first i thought it would be easy, but now once i have got round to creating it i cant seem to think of a way to make it work!

TABLE: BOOKINGS
FIELD: DATE_BOOKED

has anybody advice on how i can do this?

many thanks,
Ben

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-04 : 21:47:07

SELECT top 10 * where DATE_BOOKED < getdate()

you're going to have to give us a little bit more info for more...

Go with the flow & have fun! Else fight the flow

indieman
Starting Member

12 Posts

Posted - 2006-03-04 : 21:58:08

ok so i have a table called "BOOKING" i have a field called "DATE_BOOKED"
there are many entries in the table.

say i have these entries in there
2006-03-01
2006-03-02
2006-03-03
2006-03-06
2006-03-08
2006-03-20
2006-03-22

i need to be able to create a query that will pick the first ten days that have NO booking on, so in this case it would be
2006-03-04
2006-03-05
2006-03-07
2006-03-09
2006-03-10
2006-03-11
2006-03-12
2006-03-13
2006-03-14
2006-03-15

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-04 : 22:22:48

This will do it. It uses a number table generated by the function F_TABLE_NUMBER_RANGE to generate the next 300 dates, starting with today, and finds the first 10 that are not in the BOOKING table.


declare @BOOKING table (DATE_BOOKED datetime not null )

insert into @BOOKING
select '2006-03-01' union all
select '2006-03-02' union all
select '2006-03-03' union all
select '2006-03-06' union all
select '2006-03-08' union all
select '2006-03-20' union all
select '2006-03-22'
order by 1

select top 10
	UNBOOKED_DATE = dateadd(dd,datediff(dd,0,getdate())+a.NUMBER,0)
from
	-- Function available in Script Library forum
	dbo.F_TABLE_NUMBER_RANGE(0,300) a
where
	dateadd(dd,datediff(dd,0,getdate())+a.NUMBER,0)
	not in ( select DATE_BOOKED from @BOOKING)
order by
	NUMBER

Results:


UNBOOKED_DATE                                          
------------------------------------------------------ 
2006-03-04 00:00:00.000
2006-03-05 00:00:00.000
2006-03-07 00:00:00.000
2006-03-09 00:00:00.000
2006-03-10 00:00:00.000
2006-03-11 00:00:00.000
2006-03-12 00:00:00.000
2006-03-13 00:00:00.000
2006-03-14 00:00:00.000
2006-03-15 00:00:00.000

(10 row(s) affected)

CODO ERGO SUM

indieman
Starting Member

12 Posts

Posted - 2006-03-04 : 22:32:49

from what i can gather you are creating a temp table with all the dates in. where you have

declare @BOOKING table (DATE_BOOKED datetime not null )
insert into @BOOKING
select '2006-03-01' union all
select '2006-03-02' union all
select '2006-03-03' union all
select '2006-03-06' union all
select '2006-03-08' union all
select '2006-03-20' union all
select '2006-03-22'
order by 1

is this adding the entries into the temp table manually? because i already have the values in my original BOOKING table. if so is there a way to add the existing dates automatically?

many thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 00:19:10

"is this adding the entries into the temp table manually? because i already have the values in my original BOOKING table"
indieman, the codes below basically codes to create a table with your sample data to test and illustrate the solution.

quote:
declare @BOOKING table (DATE_BOOKED datetime not null )

insert into @BOOKING
select '2006-03-01' union all
select '2006-03-02' union all
select '2006-03-03' union all
select '2006-03-06' union all
select '2006-03-08' union all
select '2006-03-20' union all
select '2006-03-22'
order by 1

----------------------------------
'KH'

indieman
Starting Member

12 Posts

Posted - 2006-03-05 : 11:02:43

wow this works a treat!!

thanks a lot for the help guys, especially Michael Valentine Jones.

indieman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources