count days diff

B

bamf

Hi,

I need to be able to calculate the number of days that a bedspace was empty
in any given period. We currently calculate this taking the EndDate of the
periodand back counitng to the MovedOut date. This does not however work
when there has been more than one residency in the period.

Each bedspace has a unique ID and the ID's, MoveIn and MoveOut dates are
stored in tbl_residencies.

Any ideas for functions that coudl help would be much appreciated!

Many Thanks
 
K

KARL DEWEY

Try this --
SELECT tbl_residencies.ID, Sum(DateDiff("d",[MoveIn],IIf([MoveOut] Is
Null,Date(),[MoveOut]))) AS Expr1
FROM tbl_residencies
GROUP BY tbl_residencies.ID;
 
B

bamf

Hi Karl, many thanks for your reply.

Your solution works well and returns the number of days since the end of the
tenancy and the end date of the period. However, what we are struggling with
is that a bedspace may be occupied by more than one person in the period, and
we need to be able to calculate the days between the end of one tenancy and
the beginning of another. Each bedspace has its unique ID as well
 
M

Michael Gramelspacher

[email protected]>, [email protected]
says...
Hi Karl, many thanks for your reply.

Your solution works well and returns the number of days since the end of the
tenancy and the end date of the period. However, what we are struggling with
is that a bedspace may be occupied by more than one person in the period, and
we need to be able to calculate the days between the end of one tenancy and
the beginning of another. Each bedspace has its unique ID as well
Click to expand...
Expanding on what Karl has done, I came up with this. You can
see if works for you. Queries such as this are difficult for
me.

CREATE TABLE BedspaceOccupancies
(bedspace_nbr VARCHAR(4) NOT NULL PRIMARY KEY,
MoveIn_date DATETIME NOT NULL,
MoveOut_date DATETIME NULL)

Table: BedspaceOccupancies
bedspace_nbr movein_date moveout_date
1 1/18/2007 2/10/2007
1 2/14/2007 3/2/2007
2 1/18/2007 2/8/2007
2 2/10/2007 2/16/2007
3 1/27/2007
4 1/5/2007 1/25/2007
4 1/27/2007 2/22/2007

Query: Query1

SELECT b.bedspace_nbr,
b.movein_date,
b.moveout_date,
SUM((DATEDIFF("d",IIF([movein_date] < [Enter start
date:],[Enter start date:],
[movein_date]),IIF([moveout_date] IS NULL
OR [moveout_date] > [Enter end
date:],DATEADD("d",1,[Enter end date:]),
[moveout_date])))) AS [Days Occupied]
FROM BedspaceOccupancies AS b
WHERE (((b.movein_date) <= [Enter end date:])
AND ((NZ(b.moveout_date,[Enter end date:])) >= [Enter
start date:]))
GROUP BY b.bedspace_nbr,b.movein_date,b.moveout_date;

Query: Query2

SELECT Query1.bedspace_nbr,
SUM(Query1.[Days Occupied]) AS Occupied,
(DATEDIFF("d",[Enter start date:],DATEADD("d",1,[Enter
end date:])) - SUM(Query1.[Days Occupied])) AS Unoccupied,
SUM(Query1.[Days Occupied]) / DATEDIFF("d",[Enter
start date:],DATEADD("d",1,[Enter end date:])) AS [Occupancy
rate]
FROM Query1
GROUP BY Query1.bedspace_nbr;

bedspace_nbr Occupied Unoccupied Occupancy rate
1 24 4 85.71%
2 13 15 46.43%
3 28 0 100.00%
4 21 7 75.00%
 
Top