Click here to Skip to main content
Rate this: bad
good
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.4K
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 at 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 at 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 at 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
0 OriginalGriff 520
1 Maciej Los 290
2 Richard MacCutchan 265
3 BillWoodruff 265
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 13 Oct 2012
Copyright © CodeProject, 1999-2014
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