none
Get amount value per vacany

    Question

  • Hi All,

    I have two tables as below

    Void: When a property is vacant.

    RentCharge: The rent customer paid for the property and rent change period

    Void Start Date = when property got vacant,

    Let Date = When property was let


    Requirement: To get the rent charged amount when the property was vacant based on the void start date. i.e when ever the property is void\vacant what was the rent charged

    I have got the below query but getting duplicates. For example the yellow highlighted line is the desired result. the second row is duplicate as there is no record for property 3441 with tenant 51976 and void start date 2015-10-07

    ------rent charge table---------------------
    
    Declare @rentcharge TABLE 
        ([rownum] int, [Prop Code] int, [Tenant Code] int, [From Date] date, [Actual To Date] date, [Rent Charge] int)
    ;
        
    INSERT INTO @rentcharge
        (rownum, [Prop Code], [Tenant Code], [From Date], [Actual To Date], [Rent Charge])
    VALUES
         (1, 3441, 42385, '2012-06-04', '2012-08-14', 160.00),
        (47, 3441, 42485, '2012-07-16', '2014-05-12', 200.00),
        (2, 3441, 42558, '2012-08-14', '2012-09-14', 240.00),
        (3, 3441, 42650, '2012-09-14', '2012-12-06', 270.00),
        (38, 3441, 42764, '2012-10-30', '2014-11-06', 220.00),
        (4, 3441, 42850, '2012-12-06', '2012-12-21', 95.00),
        (5, 3441, 42899, '2012-12-21', '2013-05-06', 210.00),
        (41, 3441, 50169, '2013-05-03', '2013-05-05', 210.00),
        (6, 3441, 50169, '2013-05-06', '2013-07-22', 210.00),
        (49, 3441, 50305, '2013-07-05', '2013-07-05', 270.00),
        (39, 3441, 50305, '2013-07-06', '2012-07-16', 270.00),
        (42, 3441, 50335, '2013-07-19', '2013-07-21', 240.00),
        (7, 3441, 50335, '2013-07-22', '2013-10-08', 240.00),
        (13, 3441, 50389, '2013-08-20', '2014-03-13', 240.00),
        (8, 3441, 50499, '2013-10-08', '2013-10-13', 490.00),
        (40, 3441, 50499, '2013-10-14', '2014-04-30', 440.00),
        (9, 3441, 50606, '2013-12-03', '2013-12-12', 108.00),
        (10, 3441, 50624, '2013-12-12', '2014-01-23', 155.00),
        (11, 3441, 50694, '2014-01-23', '2014-05-13', 215.00),
        (14, 3441, 50796, '2014-03-13', '2014-06-04', 215.00),
        (48, 3441, 50885, '2014-04-30', '2013-05-03', 210.00),
        (43, 3441, 50910, '2014-05-12', '2014-05-12', 375.00),
        (12, 3441, 50910, '2014-05-13', '2013-08-20', 375.00),
        (50, 3441, 50959, '2014-06-03', '2014-06-03', 270.00),
        (15, 3441, 50959, '2014-06-04', '2014-07-02', 270.00),
        (16, 3441, 51011, '2014-07-02', '2014-07-29', 288.00),
        (17, 3441, 51073, '2014-07-29', '2014-09-05', 210.00),
        (18, 3441, 51179, '2014-09-05', '2014-10-14', 155.00),
        (19, 3441, 51280, '2014-10-14', '2014-10-17', 240.00),
        (20, 3441, 51287, '2014-10-17', '2014-11-25', 216.00),
        (44, 3441, 51346, '2014-11-06', '2015-08-20', 180.00),
        (21, 3441, 51391, '2014-11-25', '2014-11-28', 180.00),
        (22, 3441, 51398, '2014-11-28', '2014-12-16', 180.00),
        (23, 3441, 51446, '2014-12-16', '2015-02-10', 190.00),
        (24, 3441, 51560, '2015-02-10', '2015-02-24', 210.00),
        (25, 3441, 51602, '2015-02-24', '2015-05-08', 173.00),
        (26, 3441, 51787, '2015-05-08', '2015-05-22', 276.00),
        (27, 3441, 51824, '2015-05-22', '2015-07-01', 233.00),
        (45, 3441, 51857, '2015-06-05', '2012-10-30', 120.00),
        (29, 3441, 51931, '2015-06-30', '2015-06-30', 270.00),
        (28, 3441, 51931, '2015-07-01', '2015-06-30', 270.00),
        (30, 3441, 51976, '2015-07-17', '2015-10-07', 125.00),
        (46, 3441, 52086, '2015-08-19', '2015-08-19', 150.00),
        (36, 3441, 52086, '2015-08-20', '2015-08-19', 150.00),
        (31, 3441, 52185, '2015-10-07', '2015-10-13', 240.00),
        (32, 3441, 52207, '2015-10-13', '2015-11-20', 108.00),
        (33, 3441, 52292, '2015-11-20', '2015-12-22', 210.00),
        (37, 3441, 52264, '2015-12-21', '2015-12-21', 210.00),
        (34, 3441, 52264, '2015-12-22', '2016-01-20', 125.00),
        (35, 3441, 52422, '2016-01-20', '2016-02-03', 155.00)
    ;
    
    Declare @void TABLE 
        ([Void Ref] int, [Prop Code] int, [Previous Tenant] int, [Void Start Date] date, [Let Date] date)
    ;
    
    -------------Void Table------------------------------------
        
    INSERT INTO @void
        ([Void Ref], [Prop Code], [Previous Tenant], [Void Start Date], [Let Date])
    VALUES
        (275, 3441, 42850, '2013-04-27', '2013-05-02'),
        (449, 3441, 50305, '2013-07-12', '2013-07-18'),
        (495, 3441, 50335, '2013-08-07', '2013-08-19'),
        (624, 3441, 50389, '2013-10-02', '2013-10-07'),
        (748, 3441, 50499, '2013-11-29', '2013-12-02'),
        (758, 3441, 50606, '2013-12-05', '2013-12-11'),
        (848, 3441, 50624, '2014-01-20', '2014-01-22'),
        (966, 3441, 50694, '2014-03-10', '2014-03-12'),
        (1070, 3441, NULL, '2014-04-28', '2014-04-29'),
        (1129, 3441, 50885, '2014-05-09', '2014-05-11'),
        (1169, 3441, 50910, '2014-05-28', '2014-06-02'),
        (1234, 3441, 50977, '2014-06-30', '2014-07-01'),
        (1297, 3441, 51011, '2014-07-25', '2014-07-28'),
        (1417, 3441, 51073, '2014-09-01', '2014-09-04'),
        (1530, 3441, 51179, '2014-10-13', '2014-10-13'),
        (1541, 3441, 51280, '2014-10-15', '2014-10-16'),
        (1619, 3441, 51287, '2014-10-31', '2014-11-05'),
        (1670, 3441, 51346, '2014-11-18', '2014-11-20'),
        (1691, 3441, 51377, '2014-11-24', '2014-11-24'),
        (1696, 3441, 51391, '2014-11-27', '2014-11-27'),
        (1757, 3441, 10271, '2014-12-13', '2014-12-15'),
        (1889, 3441, 51446, '2015-02-06', '2015-02-09'),
        (1939, 3441, 51560, '2015-02-20', '2015-02-23'),
        (2171, 3441, 51602, '2015-04-28', '2015-05-07'),
        (2231, 3441, 51787, '2015-05-15', '2015-05-21'),
        (2262, 3441, 51824, '2015-05-25', '2015-05-28'),
        (2290, 3441, 51839, '2015-06-01', '2015-06-04'),
        (2360, 3441, 51857, '2015-06-25', '2015-06-29'),
        (2413, 3441, 51931, '2015-07-07', '2015-07-16'),
        (2548, 3441, 51976, '2015-08-11', '2015-08-18'),
        (2729, 3441, 52086, '2015-10-07', '2015-10-06'),
        (2732, 3441, 52185, '2015-10-09', '2015-10-12'),
        (2826, 3441, 52207, '2015-11-17', '2015-11-19'),
        (2913, 3441, 52292, '2015-12-17', '2015-12-20'),
        (2956, 3441, 52264, '2016-01-09', '2016-01-19'),
        (3041, 3441, 52422, '2016-02-04', '2016-02-05')
    ;
    
    ---------------------Query-------------------------------------------------------------------------------------------------------
    
    Select   vd.[Void Ref]
    		,vd.[Prop Code]
    		,rc.[Tenant Code]
    		,vd.[Void Start Date]
    		,vd.[Let Date]
    		,rc.[From Date]
    		,rc.[Actual To Date]
    		,rc.[Rent Charge]
    
    from @void vd
    LEFT OUTER JOIN @rentcharge rc ON vd.[Prop Code] = rc.[Prop Code] AND vd.[Void Start Date] BETWEEN rc.[From Date] AND rc.[Actual To Date]

    Thanks

    Jag




    • Edited by jaggy99 Monday, March 20, 2017 11:50 AM
    Monday, March 20, 2017 11:09 AM

All replies

  • What is desired output?  If you add 

    , SUM(rc.[Rent Charge]) OVER (PARTITION BY vd.[Void Ref]) sm

    that value you need to be displayed? 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 20, 2017 11:19 AM
    Answerer
  • Hi Uri,

    I have uploaded the screenshot with the desired result above. The query should return the same rows as in the void table + the rentcharge amount. We are not adding the rent charge and instead want to pick up the correct rent charge value when the property was vacant.

    A customer rent is updated multiple times which is held in rent charged table and when the property is vacant (void table) we need to show what was the rent charged.

    Regards,

    Jag


    • Edited by jaggy99 Monday, March 20, 2017 11:55 AM
    Monday, March 20, 2017 11:52 AM
  • Select   vd.[Void Ref]
    ,vd.[Prop Code]
    ,rc.[Tenant Code]
    ,vd.[Void Start Date]
    ,vd.[Let Date]
    ,rc.[From Date]
    ,rc.[Actual To Date]
    ,rc.[Rent Charge]

    from @void vd
    JOIN @rentcharge rc ON vd.[Prop Code] = rc.[Prop Code] 
    WHERE  vd.[Void Start Date]   BETWEEN rc.[From Date] AND rc.[Actual To Date]

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 20, 2017 12:16 PM
    Answerer
  • Nope, still getting duplicates. There cannot be more than 36 rows.

    Regards,

    Jag

    Monday, March 20, 2017 8:53 PM
  • Your rentCharge table has overlapping intervals (see, for example, VoidRef 275).

    What do these overlapping intervals represent?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 20, 2017 9:32 PM
    Moderator
  • What the rent was the last time it wasn't vacant? 

    Select   vd.[Void Ref]
    		,vd.[Prop Code]
    		,rc.[Tenant Code]
    		,vd.[Void Start Date]
    		,vd.[Let Date]
    		,rc.[From Date]
    		,rc.[Actual To Date]
    		,rc.[Rent Charge]
    
    from @void vd
    outer apply (select top 1 * from @rentcharge rc where vd.[Prop Code] = rc.[Prop Code] AND vd.[Void Start Date] >= rc.[Actual To Date] order by [Actual To Date] desc) rc
    

    or purely on the most recent [Actual To Date] where there's an overlap?

    Select   vd.[Void Ref]
    		,vd.[Prop Code]
    		,rc.[Tenant Code]
    		,vd.[Void Start Date]
    		,vd.[Let Date]
    		,rc.[From Date]
    		,rc.[Actual To Date]
    		,rc.[Rent Charge]
    
    from @void vd
    outer apply (select top 1 * from @rentcharge rc where vd.[Prop Code] = rc.[Prop Code] AND vd.[Void Start Date] BETWEEN rc.[From Date] AND rc.[Actual To Date] order by [Actual To Date] desc) rc

    Monday, March 20, 2017 9:48 PM
  • The rentcharge table holds the rent charged to the tenant. Every 3-6 months the rent is reviewed and updated. The From Date and To date identify the rent the customer paid during the period. Also when  new tenants come in the property  the new rent is applied.

    Regards,
    Jag

    Monday, March 20, 2017 10:38 PM
  • We get the rent charge but if the property is vacant, there isn't really a charge so the question is which "rentcharge" are you attempting to pick up on a vacant property?
    Monday, March 20, 2017 10:52 PM
  • Hi Ryan,

    The query works with the sample data provided but not in production for some reason. The query keeps running and have to end it.

    Is it possible to take the last rent update per tenant per property and then join with void table. For example for tenant 50305 the last rent was $ 240

      (49, 3441, 50305, '2013-07-05', '2013-07-05', 270.00),
        (39, 3441, 50305, '2013-07-06', '2012-07-16', 270.00),
        (42, 3441, 50335, '2013-07-19', '2013-07-21', 240.00),

    Regards,
    Jag

    Monday, March 20, 2017 11:01 PM
  • Correct, so best to pick up the last rent charge before the property got vacant. The idea is to calculate how much money we are losing when the property is vacant.

    Regards,

    Jag

    Monday, March 20, 2017 11:03 PM
  • Hi Ryan,

    What about the below. Would this be the correct logic?

    Select   vd.[Void Ref]
    		,vd.[Prop Code]
    		,vd.[Previous Tenant]
    		,rc.[Tenant Code]
    		,vd.[Void Start Date]
    		,vd.[Let Date]
    		,rc.[From Date]
    		,rc.[Actual To Date]
    		,rc.[Rent Charge]
    
    from @void vd
    LEFT OUTER JOIN 
    (Select rc.[Prop Code], rc.[Tenant Code], rc.[Rent Charge], rc.[From Date], rc.[Actual To Date] from @rentcharge rc
    	Join ( select  [Tenant Code],[Prop Code],max([From Date]) 'Last Date'
    		   from @rentcharge 
    			Group BY [Tenant Code], [Prop Code]) mrc ON rc.[Prop Code] = mrc.[Prop Code] AND mrc.[Last Date] = rc.[From Date] ) rc ON rc.[Prop Code] = vd.[Prop Code] 
    			AND vd.[Previous Tenant] = rc.[Tenant Code]

    Regards,

    Jag

    Monday, March 20, 2017 11:59 PM
  • You'd be better placed to answer that.

    Points that comes to mind

    - what if a previous tenant returns, you'd be picking up those newer details for any older vacancy

    - what about where there is no previous tenant

    As for going slow on production system, what does the execution plan say? Any suggested indexes and can you have those applied? 

    Tuesday, March 21, 2017 1:20 AM
  • Usually tenants move to another property. Even if they return, the rent is calculated based on their income so shouldnt be a big difference. For new properties which didn't have a tenant, we have another table (Market Rent) and will pull rent from it.

    Regards,

    Jag

    Tuesday, March 21, 2017 2:03 AM
  • To get last Rent Update per tenant you would use

    ;with cte as (select *, row_number() over (partition by PropCode, TenantCode order by [Actual To Date] DESC) as Rn from @RentCharge)

    select * from cte where Rn = 1 -- this will give you latest charge per property per tenant

    You then may join it with the vacancy table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 21, 2017 2:52 AM
    Moderator