Problem with Selecting datas in sql

Question

0.00/5 (No votes)

See more:

Hi,

One of my table has following fields

roombooked

SQL

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 21:51pm

devausha

Updated 12-Oct-12 21:52pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bhushan Shah1988 · Accepted Answer · 2012-10-12T22:46:00

try following t-sql code block :

SQL

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.

OriginalGriff · Accepted Answer · 2012-10-12T22:35:00

Solution 1

Returning them as a table is easy:

SQL

SELECT roomno FROM myTable WHERE deptdate BETWEEN '2012-10-16' AND '2012-10-19'

will do that.
As comma separated values is harder:

SQL

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]

Posted 12-Oct-12 22:35pm

OriginalGriff

Updated 12-Oct-12 22:43pm

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.