Calculating Void Days

forgot the syntax for you;
DATEDIFF(day, startdate, enddate)

Hi Derek,

You should have some kind of database in order to be able to query the data. Once you have well defined database structure you have virtually no limits on ways to query it. Do you have existing data structure?

Hi javaboon

I have 2 tables, one listing the properties and the other listing all tenancies linked to the properties.  DateDiff is fine(for me) when you have one tenancy showing in your date range but it is more usual to have 2 or more..  

The tables show the following relevant detail

Property Table
PropertyID        Address          
0001                123 Dane Road
0002                345 Glenn Road
0003                543 Baker Street

Tenancy Table
TenantID         PropertyID    StartDate     EndDate
000001           0001             12/12//2009 03/01/2009
000002           0001              05/01/2009 17/01/2009
000003           0001              21/01/2009  28/01/2009
000004           0002              15/12/2008  15/01/2009

So the issues I have really are when there are mulitple tenany records involved within the date range needed..

Kind regards,


Derek.

   

Following procedure counts void days, calling example:

exec sp_voidDays '2009/01/01', '2009/01/31'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.sp_voidDays 
	@DateFrom datetime, 
	@DateTo datetime
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @calendar TABLE (date datetime)
  DECLARE @currentDate datetime
  SET @currentDate = @DateFrom
  
  -- Populate calendar table
  WHILE @currentDate < @DateTo
  BEGIN
    INSERT INTO @calendar VALUES (@currentDate)
    SET @currentDate = DATEADD(day, 1, @currentDate)
  END 
  
  -- Delete occupied days
  DELETE FROM @calendar
    FROM @calendar c
   CROSS JOIN [Tenancy Table] t
   WHERE c.date BETWEEN t.startdate AND t.enddate
   
  SELECT COUNT(*) VoidDays FROM @calendar
  
END
GO
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

You would probably need it for given propertyID, so I've added one more param:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.sp_voidDays 
	@DateFrom datetime, 
	@DateTo datetime,
	@PropertyID char(4)
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @calendar TABLE (date datetime)
  DECLARE @currentDate datetime
  SET @currentDate = @DateFrom
  
  -- Populate calendar table
  WHILE @currentDate < @DateTo
  BEGIN
    INSERT INTO @calendar VALUES (@currentDate)
    SET @currentDate = DATEADD(day, 1, @currentDate)
  END 
  
  -- Delete occupied days
  DELETE FROM @calendar
    FROM @calendar c
   INNER JOIN [Tenancy Table] t ON t.PropertyID = @PropertyID
   WHERE c.date BETWEEN t.startdate AND t.enddate
   
  SELECT @PropertyID PropertyID, COUNT(*) VoidDays FROM @calendar
  
END
GO
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Sorry, @Calendar table population should have slightly different condition:

WHILE @currentDate <= @DateTo

Hi pcelba

can I assume you think I have a calendar table and to use as a temporary table to add the dates into?

Kind regards,


Derek.

No, you don't need calendar table. And I don't think you have one. SP is creating its own temporary calendar for given date range. It is necessary to allow void days counting.

Hi pcelba

I have added the procedure and created a query to retrieve some info but get an error..  attached jpg shows it..  The procedure was stored in the right database but I am not sure what to look for to correct it..  sorry but I am a novice of sorts to procedures..

I have amended the code you gave slightly to suit..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_voidDays]
      @DateFrom datetime,
      @DateTo datetime,
      @PropertyID uniqueidentifier
AS
BEGIN
  SET NOCOUNT ON;
 
  DECLARE @calendar TABLE (date datetime)
  DECLARE @currentDate datetime
  SET @currentDate = @DateFrom
 
  -- Populate calendar table
  WHILE @currentDate <= @DateTo
  BEGIN
    INSERT INTO @calendar VALUES (@currentDate)
    SET @currentDate = DATEADD(day, 1, @currentDate)
  END
 
  -- Delete occupied days
  DELETE FROM @calendar
    FROM @calendar c
   INNER JOIN [Tenancy] t ON t.PropertyFK = @PropertyID
   WHERE c.date BETWEEN t.dteOccupiedDate AND t.dteVacatingDate
   
  SELECT @PropertyID PropertyFK, COUNT(*) VoidDays FROM @calendar
 
END

If you want to use the code in a SELECT statement then you have to define it as a function (update the function name to whatever you want):

CREATE FUNCTION [dbo].[fn_voidDays] (
      @DateFrom datetime, 
      @DateTo datetime,
      @PropertyID uniqueidentifier  )
RETURNS int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @RetVal int
  DECLARE @calendar TABLE (date datetime)
  DECLARE @currentDate datetime
  SET @currentDate = @DateFrom
  
  -- Populate calendar table
  WHILE @currentDate <= @DateTo
  BEGIN
    INSERT INTO @calendar VALUES (@currentDate)
    SET @currentDate = DATEADD(day, 1, @currentDate)
  END 
  
  -- Delete occupied days
  DELETE FROM @calendar
    FROM @calendar c
   INNER JOIN [Tenancy] t ON t.PropertyFK = @PropertyID
   WHERE c.date BETWEEN t.dteOccupiedDate AND t.dteVacatingDate
   
  SET @Retval = (SELECT COUNT(*) VoidDays FROM @calendar)
  RETURN @Retval
 
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

Hi pcelba..

I get this error creating the function :(

Msg 443, Level 16, State 15, Procedure fn_voidDays, Line 8
Invalid use of side-effecting or time-dependent operator in 'SET OPTION ON' within a function.

I removed the set nocount on option and it works..  is this ok to do?

It is working a treat without it..  I will wait for your comments and accept your fab answer, and thanks for it..  very clever..

Kind regards,


Derek.

Many thanks for your help on this pcelba.. you were very quick and I have learned something valualble..

Kind regards


Derek.