Click here to Skip to main content
12,070,942 members (65,401 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
Hi,

One of my table has following fields

roombooked
rid roomno  cusid  arrdate     deptdate    status
1    102     3     10/13/2012  10/17/2012   grnt
2    103     5     10/14/2012  10/19/2012   grnt
3    105     1     10/11/2012  10/12/2012   grnt

If i give from date todate, it gives roomno which is booked for example

If i give 10/16/2012 and 19/10/2012 it displays
roomno 102,103

How to do it?
Posted 12-Oct-12 22:51pm
devausha1.5K
Edited 12-Oct-12 22:52pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Returning them as a table is easy:
SELECT roomno FROM myTable WHERE deptdate BETWEEN '2012-10-16' AND '2012-10-19'
will do that.
As comma separated values is harder:
SELECT SUBSTRING(
   (SELECT ',' + CONVERT(varchar, roomno) FROM myTable WHERE deptdate BETWEEN '2012-10-16' AND '2012-10-19'
   FOR XML PATH('')),2,100000) AS CSV
should do it.

[edit]Corrected CONVERT function call - OriginalGriff[/edit]
  Permalink  
v2
Comments
devausha 13-Oct-12 4:45am
   
I want get the value is depending upon arrival date and departure date which is in the fromdate and todate
OriginalGriff 13-Oct-12 4:52am
   
So change the "WHERE" clause to reference them both.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

try following t-sql code block :

CREATE TABLE #TEMP
(
	RID		INT,
	ROOMNO		INT, 
	CUSID		INT,  
	ARRDATE		DATETIME,
	DEPTDATE        DATETIME,
	STATUS		VARCHAR(50)
)
 
INSERT INTO #TEMP VALUES(1,102,3,'10/13/2012','10/17/2012','GRNT')
INSERT INTO #TEMP VALUES(2,103,5,'10/14/2012','10/19/2012','GRNT')
INSERT INTO #TEMP VALUES(3,105,1,'10/11/2012','10/12/2012','GRNT')
 
DECLARE @RoomNo  VARCHAR(max)
 
SELECT	@RoomNo= ISNULL(@RoomNo + ',', '') + cast(ROOMNO as varchar(5))  
FROM	#TEMP
WHERE	(ARRDATE BETWEEN '10-16-2012' AND '10-19-2012'
		OR	DEPTDATE BETWEEN '10-16-2012' AND '10-19-2012')
		
SELECT	@RoomNo
 
DROP TABLE #TEMP

it will work for you.
  Permalink  
v2
Comments
devausha 13-Oct-12 4:56am
   
Thank you It works fine

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160208.1 | Last Updated 13 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100