Query Help - 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 2012 Forums

Transact-SQL (2012)

Query Help

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2013-06-12 : 02:37:38

Hi All.

I need help with a query. I have two tables:

Rooms:
-ID
-RoomName
-RoomDesc

Bookings:
-ID
-BookingDate
-RoomID

I need a query that will show what rooms are free on every day of the month. If they have no record in the bookings room they are free.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 02:45:28

You would need a calendar table for that. do you've one? if yes, you can use it

otherwise use this to get all rooms without any booking for a month


DECLARE @Date datetime, -- pass any date for a month
@MonthStart datetime,
@MonthEnd datetime

SELECT @MonthStart = DATEADD(mm,DATEDIFF(mm,0,@date),0),
@MonthEnd = DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)-1

SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
(SELECT 1
FROM dbo.CalendarTable (@MonthStart,@MonthEnd,0,0) c
INNER JOIN Bookings b
ON b.BookingDate = c.[Date]
WHERE b.RoomID = r.RoomID
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 02:46:38

dbo.CalendarTable can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 02:56:38

[code]-- Without using separate UDF for Calendar... You can do as follows
DECLARE @date DATE = GETDATE() -- You can put specific date
;with Calendar(Dates) as
(SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0)
UNION ALL
SELECT DATEADD ( DD, 1, Dates)
FROM Calendar
WHERE Dates < DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)))
)
SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
(SELECT 1
FROM Calendar c
INNER JOIN Bookings b
ON b.BookingDate = c.[Dates]
WHERE b.RoomID = r.RoomID)[/code]
--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 03:05:20

quote:

Originally posted by bandi

-- Without using separate UDF for Calendar... You can do as follows
DECLARE @date DATE = GETDATE()  -- You can put specific date
;with Calendar(Dates) as
(SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0)
 UNION ALL
 SELECT DATEADD ( DD, 1, Dates)
 FROM Calendar
 WHERE Dates <= DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)))
 )
SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
	(SELECT 1
	FROM Calendar c
	INNER JOIN Bookings b
	ON b.BookingDate = c.[Dates]
	WHERE b.RoomID = r.RoomID)

--
Chandu

Small tweak to include last day of the month as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 03:20:16

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

-- Without using separate UDF for Calendar... You can do as follows
DECLARE @date DATE = GETDATE() -- You can put specific date
;with Calendar(Dates) as
(SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0)
UNION ALL
SELECT DATEADD ( DD, 1, Dates)
FROM Calendar
WHERE Dates <= DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)))
)
SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
(SELECT 1
FROM Calendar c
INNER JOIN Bookings b
ON b.BookingDate = c.[Dates]
WHERE b.RoomID = r.RoomID)

--
Chandu

Small tweak to include last day of the month as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh, in recursive queries it should be < symbol for the last loop....
with the above condition it will include 1st july date also

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 03:47:03

quote:
Originally posted by bandi

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

-- Without using separate UDF for Calendar... You can do as follows
DECLARE @date DATE = GETDATE() -- You can put specific date
;with Calendar(Dates) as
(SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0)
UNION ALL
SELECT DATEADD ( DD, 1, Dates)
FROM Calendar
WHERE Dates <= DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)))
)
SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
(SELECT 1
FROM Calendar c
INNER JOIN Bookings b
ON b.BookingDate = c.[Dates]
WHERE b.RoomID = r.RoomID)

--
Chandu

Small tweak to include last day of the month as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh, in recursive queries it should be < symbol for the last loop....
with the above condition it will include 1st july date also

--
Chandu

Hmm..Where did you get that restriction from?

Nope. it wont

unless you make it = it wont include last day of the month as you've it as
DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)))

if you want to use < it should be this


DECLARE @date DATE = GETDATE()  -- You can put specific date
;with Calendar(Dates) as
(SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0)
 UNION ALL
 SELECT DATEADD ( DD, 1, Dates)
 FROM Calendar
 WHERE Dates < DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)
 )
SELECT r.*
FROM Rooms r
WHERE NOT EXISTS
	(SELECT 1
	FROM Calendar c
	INNER JOIN Bookings b
	ON b.BookingDate = c.[Dates]
	WHERE b.RoomID = r.RoomID)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 04:05:34

visakh,
I checked the above query in SQL Box.. It returns 1st July also

We can do as follows:
1) DATEADD(DD, 1, Dates) <= DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0))) -- Your case
2) Dates < DATEADD(DD, -1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0))) -- Mine

2nd case is mine.... both are same....

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources