Date 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

General SQL Server Forums

New to SQL Server Programming

Date query Help

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-16 : 05:48:36

Hi,

Here is my scenario


create table #temp
(
ALLOTMENT_ROOM_ID int
,ROOM_ID  int
,ROOM_DATE datetime 
,LIST_ROOM_STATUS int
)

insert into #temp
select 74,1,'2011-06-16 00:00:00.000',1 union all
select 74,1,'2011-06-17 00:00:00.000',2 union all
select 74,1,'2011-06-18 00:00:00.000',1 union all
select 74,1,'2011-06-19 00:00:00.000',1 union all
select 74,1,'2011-06-20 00:00:00.000',1 union all
select 74,2,'2011-06-16 00:00:00.000',2 union all
select 74,2,'2011-06-17 00:00:00.000',1 union all
select 74,2,'2011-06-18 00:00:00.000',1 union all
select 74,2,'2011-06-19 00:00:00.000',2 union all
select 74,2,'2011-06-20 00:00:00.000',1 union all
select 74,2,'2011-06-21 00:00:00.000',1 union all
select 74,2,'2011-06-22 00:00:00.000',2

Expected Output:

ALLOTMENT_ROOM_ID	ROOM_ID	    ROOM_DATE	
74	1	16-05-2011,18-06-2011 to 20-06-2011
74	2	17-06-2011 to 18-06-2011,20-06-2011 to 21-06-2011

here LIST_ROOM_STATUS=2 means room_date is deleted
I need to get room_dates in a single field group by ALLOTMENT_ROOM_ID,ROOM_ID

Assume for ALLOTMENT_ROOM_ID=74 and ROOM_ID=1 if all LIST_ROOM_STATUS=1 then we can consider mindate to maxdate
If LIST_ROOM_STATUS=2 then we exclude that date

Thanks,

--Ranjit

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-16 : 11:23:20

[code]
select ALLOTMENT_ROOM_ID, ROOM_ID,
ROOM_DATE = stuff((select ',' + convert(varchar(10), ROOM_DATE, 121)
from #temp x
where x.ALLOTMENT_ROOM_ID = t.ALLOTMENT_ROOM_ID
and x.ROOM_ID = t.ROOM_ID
and x.LIST_ROOM_STATUS = 1
order by ROOM_DATE
for xml path('')), 1, 1, '')
from #temp t
group by ALLOTMENT_ROOM_ID, ROOM_ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-16 : 19:20:35

Your DDL is wrong. We can kludge it, but it is MUCH better to fix it. You need a check-in and check-out column pair. Your desired output shows a total violation of First Normal Form! Surely you want to be a better SQL programmer than that!

CREATE TABLE Room_Allocation
(allotment_room_id INTEGER NOT NULL,
room_nbr INTEGER NOT NULL,
start_room_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_room_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
room_status INTEGER NOT NULL
CHECK (list_room_status IN (1,2))
);

INSERT INTO Room_Allocations
VALUES (74, 1, '2011-06-16','2011-06-16', 1),
(74, 1, '2011-06-17', '2011-06-17', 2),
(74, 1, '2011-06-18', '2011-06-20', 1),
(74, 2, '2011-06-16', '2011-06-16', 2),
(74, 2, '2011-06-17', '2011-06-21', 1),
(74, 2, '2011-06-22', '2011-06-22', 2);

Now the problem is to write a procedure that will do an update or insert when you get new data.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources