SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 06/12/2013 :  02:37:38  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/12/2013 :  02:45:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  02:46:38  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/12/2013 :  02:56:38  Show Profile  Reply with Quote
-- 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

Edited by - bandi on 06/12/2013 02:57:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  03:05:20  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/12/2013 :  03:20:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  03:47:03  Show Profile  Reply with Quote
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)

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/12/2013 :  04:05:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000