February 26, 2008 at 7:39 am
Room Date Qty_In_Out
101 01/22/2008 1
102 02/4/2008 1
101 02/5/2008 -1
102 02/5/2008 -1
102 02/5/2008 2
101 02/9/2008 1
101 02/10/2008 -1
102 02/20/2008 -2
102 03/5/2008 1
101 03/6/2008 1
The system stores a record each time a room is occupied or vacated. The goal of the statement is to determine how many days the room was occupied with a period. I would like to write a VIEW for use by typical report writers where the user can filter the view using a date range and a room number to determine how many days it was occupied. In the example data above, the goal would be to determine how many occupancy days (day * occupants) was room 102 occupied between Feb 1 and Feb 15, 2008. This is an easy task with a 'loop' in a store procedure, but the goal here is to write a view to do the same job. Is this possible?
February 26, 2008 at 7:55 am
msaint (2/26/2008)
Room Date Qty_In_Out101 01/22/2008 1
102 02/4/2008 1
101 02/5/2008 -1
102 02/5/2008 -1
102 02/5/2008 2
101 02/9/2008 1
101 02/10/2008 -1
102 02/20/2008 -2
102 03/5/2008 1
101 03/6/2008 1
The system stores a record each time a room is occupied or vacated. The goal of the statement is to determine how many days the room was occupied with a period. I would like to write a VIEW for use by typical report writers where the user can filter the view using a date range and a room number to determine how many days it was occupied. In the example data above, the goal would be to determine how many occupancy days (day * occupants) was room 102 occupied between Feb 1 and Feb 15, 2008. This is an easy task with a 'loop' in a store procedure, but the goal here is to write a view to do the same job. Is this possible?
Hello,
The sample data information is not clear. What you represent for the column "Qty_In_Out" when it is a minus (-) value?
Thanks
Lucky
February 26, 2008 at 7:57 am
The negative data indicates the occupant(s) vacated the room. Positive numbers indicate that the room is occupied.
February 26, 2008 at 8:44 am
Couldnt you just do a count of the room where the occupancy is > 0 ?
select Room, Count(room) as [occupancy days]
from mytable
where date between '2/1/2008' and '2/15/2008' and Qty_In_Out > 0
group by room
order by Room
February 26, 2008 at 8:46 am
The count only works if you do not have occupants checking in and out on the same day and another person checking into that room on the same day.
February 26, 2008 at 8:47 am
No
The room in this example is occupied for each day between the day it was occupied and the day it was vacated.
If the data included a record for each day, this would be possible, but it does not.
February 26, 2008 at 8:51 am
you should change the query to count disctinct dates as such:
select Room, Count(distinct date) as [occupancy days]
from mytable
where date between '2/1/2008' and '2/15/2008' and Qty_In_Out > 0
group by room
order by Room
February 26, 2008 at 8:52 am
This may not answer your question, but it may help you figure out what you need to do:
create table #RoomData (
Room smallint,
XDate datetime,
QtyInOut smallint
);
insert into #RoomData values (101,'01/22/2008',1);
insert into #RoomData values(102,'02/4/2008',1);
insert into #RoomData values(101,'02/5/2008',-1);
insert into #RoomData values(102,'02/5/2008',-1);
insert into #RoomData values(102,'02/5/2008',2);
insert into #RoomData values(101,'02/9/2008',1);
insert into #RoomData values(101,'02/10/2008',-1);
insert into #RoomData values(102,'02/20/2008',-2);
insert into #RoomData values(102,'03/5/2008',1);
insert into #RoomData values(101,'03/6/2008',1);
with CheckIns (
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
#RoomData
where
QtyInOut > 0
), CheckOuts(
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
#RoomData
where
QtyInOut < 0
)
select
CheckIns.Room,
CheckIns.XDate,
CheckIns.QtyInOut,
CheckOuts.Room,
CheckOuts.XDate,
CheckOuts.QtyInOut,
datediff(dd, CheckIns.XDate, coalesce(CheckOuts.XDate, getdate())) * CheckIns.QtyInOut as OccupancyDays
from
CheckIns CheckIns
left outer join CheckOuts CheckOuts
on (CheckIns.RowNumber = CheckOuts.RowNumber
and CheckIns.Room = CheckOuts.Room)
order by
CheckIns.Room,
CheckIns.XDate
drop table #RoomData
😎
February 26, 2008 at 8:55 am
No
The room in this example is occupied for each day between the day it was occupied and the day it was vacated.
If the data included a record for each day, this would be possible, but it does not.
I understand what you asking. I misunderstood the requirement. Let me have another look.
February 26, 2008 at 8:59 am
Wow Lynn,
For someone who is accustom to accomplishing this using VB6 or C# loops, this is really an amazing piece of code. I will have to abosorb this. Thanks so much!
February 26, 2008 at 9:07 am
Here is what I would do:
Create View room_occupancy
As
With occupancy AS
(
Select
O.room,
O.room_date as check_in,
O.in_out as occupants,
IsNull((select min(room_date) from rooms Where room = O.room and in_out O.room_date), O.room_date + 1) as check_out
From
rooms O
Where
O.in_out > 0
)
Select
IsNull(R.room, R1.room) as room,
D.date as date,
Case
When
R.room is Null then 0
Else 1
End as occupied,
IsNull(R.occupants, 0) as occupants
From
(Select distinct room from rooms) as R1 Cross Join
dates D Left Join
occupancy R On
D.date Between R.check_in and R.check_out And
R1.room = R.room
That view will give you a record for each day of the year for each room. This is what the Cross Join on dates does. You do have to create and populate the dates table, here is how I did that:
Create table dates
(
date smalldatetime
)
Declare @date smalldatetime
Set @date = '1/1/08'
While @date < '1/1/09'
Begin
Insert Into dates
Select
@date
Set @date = @date + 1
End
Jeff Moden would use a numbers table and do a set-based insert, but this will work for 1 year fine.
Now once you have the view I think you get the data you are looking for by doing this:
Select
room,
Sum(occupants) as occupants,
sum(occupied) as days_occupied
From
room_occupancy
Where
date between '2/1/08' and '2/14/08'
Group By
room
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 9:53 am
Jack,
Thanks for for creating a simple, easy to implement solution for this issue.
M. Saint
February 26, 2008 at 9:58 am
For your perusal, some more code to play with:
create table dbo.RoomData (
Room smallint,
XDate datetime,
QtyInOut smallint
);
insert into dbo.RoomData values (101,'01/22/2008',1);
insert into dbo.RoomData values(102,'02/4/2008',1);
insert into dbo.RoomData values(101,'02/5/2008',-1);
insert into dbo.RoomData values(102,'02/5/2008',-1);
insert into dbo.RoomData values(102,'02/5/2008',2);
insert into dbo.RoomData values(101,'02/9/2008',1);
insert into dbo.RoomData values(101,'02/10/2008',-1);
insert into dbo.RoomData values(102,'02/20/2008',-2);
insert into dbo.RoomData values(102,'03/5/2008',1);
insert into dbo.RoomData values(101,'03/6/2008',1);
go
create function dbo.OccupancyDays (
@StartDate datetime,
@EndDate datetime
) returns table
as return
with CheckIns (
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
dbo.RoomData
where
QtyInOut > 0
), CheckOuts(
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
dbo.RoomData
where
QtyInOut < 0
)
select
CheckIns.Room RoomIn,
CheckIns.XDate XDateIn,
CheckIns.QtyInOut QtyIn,
CheckOuts.Room RoomOut,
CheckOuts.XDate XDateOut,
CheckOuts.QtyInOut QtyOut,
@StartDate StartDate,
@EndDate EndDate,
case when CheckIns.XDate < @StartDate
then @StartDate
else CheckIns.XDate end CalcStartDate,
case when coalesce(CheckOuts.XDate, @EndDate) > @EndDate
then @EndDate
else coalesce(CheckOuts.XDate, @EndDate) end as CalcEndDate,
datediff(dd, case when CheckIns.XDate < @StartDate
then @StartDate
else CheckIns.XDate end,
case when coalesce(CheckOuts.XDate, @EndDate) > @EndDate
then @EndDate
else coalesce(CheckOuts.XDate, @EndDate) end) * CheckIns.QtyInOut as OccupancyDays
from
CheckIns CheckIns
left outer join CheckOuts CheckOuts
on (CheckIns.RowNumber = CheckOuts.RowNumber
and CheckIns.Room = CheckOuts.Room)
where
CheckIns.XDate <= @EndDate
-- and CheckOuts.XDate >= @EndDate
--order by
-- CheckIns.Room,
-- CheckIns.XDate
go
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '2007-01-01'
set @EndDate = '2008-12-31'
select * from dbo.OccupancyDays('2008-02-01', '2008-02-15');
with CheckIns (
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
dbo.RoomData
where
QtyInOut > 0
), CheckOuts(
RowNumber,
Room,
XDate,
QtyInOut
) as (
select
row_number() over(partition by Room order by Room, XDate) as RowNumber,
Room,
XDate,
QtyInOut
from
dbo.RoomData
where
QtyInOut < 0
)
select
CheckIns.Room RoomIn,
CheckIns.XDate XDateIn,
CheckIns.QtyInOut QtyIn,
CheckOuts.Room RoomOut,
CheckOuts.XDate XDateOut,
CheckOuts.QtyInOut QtyOut,
datediff(dd, CheckIns.XDate, coalesce(CheckOuts.XDate, @EndDate)) * CheckIns.QtyInOut as OccupancyDays
from
CheckIns CheckIns
left outer join CheckOuts CheckOuts
on (CheckIns.RowNumber = CheckOuts.RowNumber
and CheckIns.Room = CheckOuts.Room)
--where
-- CheckIns.XDate <= @EndDate
-- and CheckOuts.XDate >= @EndDate
order by
CheckIns.Room,
CheckIns.XDate
go
drop function dbo.OccupancyDays
go
drop table dbo.RoomData
😎
February 26, 2008 at 10:02 am
I would have gone with a solution similar to Jack's using derived tables. It is very simple and clean.
DECLARE @t TABLE
(
Room int,
Date datetime,
Qty_In_Out int
)
INSERT INTO @t
SELECT 101, '01/22/2008', 1 UNION ALL
SELECT 102, '02/4/2008', 1 UNION ALL
SELECT 101, '02/5/2008', -1 UNION ALL
SELECT 102, '02/5/2008', -1 UNION ALL
SELECT 102, '02/5/2008', 2 UNION ALL
SELECT 101, '02/9/2008', 1 UNION ALL
SELECT 101, '02/10/2008', -1 UNION ALL
SELECT 102, '02/20/2008', -2 UNION ALL
SELECT 102, '03/5/2008', 1 UNION ALL
SELECT 101, '03/6/2008', 1
DECLARE @min-2 DATETIME,
@max-2 DATETIME
SET @min-2 = '2/1/2008'
SET @max-2 = '2/15/2008'
SELECT
a.room,
SUM(CASE WHEN b.date IS NULL THEN DATEDIFF(dd,a.date,@max) ELSE DATEDIFF(dd,a.date,b.date)END)
FROM(
SELECT *, Row_Number() OVER(PARTITION BY Room ORDER BY Room, Date) AS [id]
FROM @t
WHERE Qty_In_Out > 0
) AS a
INNER JOIN
(
SELECT *, Row_Number() OVER(PARTITION BY Room ORDER BY Room, Date) AS [id]
FROM @t
WHERE Qty_In_Out < 0
) AS b
ON a.room = b.room and a.date <= b.date and a.id = b.id
WHERE (a.date between @min-2 and @max-2) OR
(b.date between @min-2 and @max-2)
GROUP BY a.room
ORDER BY a.room
February 26, 2008 at 10:22 am
Matter of preference I guess. I find cte's help me keep the code cleaner actually instead of using derived tables in the FROM clause. Keeps it more like tables there without a lot of extra code to have read through.
It also has the benefit of allowing me to build the query in steps. If I know I would find myself using a derived table, I can build the query for the derived table, quickly turn it into a cte, and continue from there.
😎
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply