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?