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
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Difference between Dates

Author  Topic 

nokiauk
Starting Member

9 Posts

Posted - 2013-02-15 : 10:08:53
I have the following SQL statement:

SELECT DISTINCT

Loc.Scheme,
Loc.AccessGroup as "Area Office",
Loc.ArrearsOfficeLookup as "Arrears Officer",
TenanT.TenancyRef,
Tenant.TenancyStart,
Tenant.TenancyEnd
-- 'Void_Days' = DATEDIFF(DAY, WHAT GOES HERE)

FROM
[FusionRS_V3].dbo.vwTenancyCurrent AS TenanT
JOIN
[FusionRS_V3].dbo.VwLocationCurrent AS Loc ON TenanT.PlaceRef = Loc.PlaceRef
JOIN
[FusionRS_V3].dbo.vwLocationHistoryCurrent AS LH ON LH.PlaceRef = Loc.PlaceRef
LEFT JOIN
[FusionRS_V3].dbo.vwTenancyAttributesCurrent AS TenAtt ON TenanT.TenancySystemRef = TenAtt.TenancySystemRef
LEFT JOIN
[FusionRS_V3].dbo.vwLocationAttributesCurrent AS LocAtt ON TenanT.PlaceRef = LocAtt.PlaceRef

WHERE

TenanT.TenancyStart BETWEEN '04/01/2011' and '03/31/2012' AND
TenanT.TenancyCat <> 'OCC' AND
LH.LocationStatus = 'V' AND
LH.EndDate >= '04/01/2011' AND
LocAtt.AttributeCode = 'DEM' AND
Loc.PlaceRef = '187AIL006'

ORDER BY

Loc.Scheme

This produces the following:



I need to work out the voids days by taking number of days from the 2nd row Tenancy Start from the 1st row Tenancy End. Is this possible?
   

- Advertisement -