Join Two Tables - Compare Amounts (Rent)

Hi All,

I have two tables TenantPayment and PropertyRentReview and like to Join them to show the total rent the tenant is paying per month and the actual property rent per month. I'm getting stuck on how to reflect the new property rent everytime the property rent changes. i.e how to use the new rent amount in a month, when the rent was updated for a given property.

So basically the tenant rent is the sum of the weeklyrent per month from the TenantPayment table and the propertyrent column should pick up the PropertyRent Amount from the PropertyRentReview table.

Below are the columns required after the Join and like to show the property rent per month and the tenant payments per month.

PROP_CODE, PropertyRent, WeeklyRent, Month, Year

PropertyRent - The PropertyRentReview table holds the new Rent, rent start Date and end date for each property. The Rent value in the table is weekly rent.  To calculate the property rent monthly use the formula: (PropertyRent/7)*365.25/12

TenantPayment - The TenantPayment holds the weekly rent paid by the tenant for the property.

Below are the two tables and sample data. Please feel free to change the datatypes as I have just put something to match the data.

DECLARE @PropertyRentReview TABLE 
  ( Prop_Code INT
  ,NewRentStartDate DATEtime
  ,RentEndDate DATEtime
  ,PropertyRent INT)


INSERT INTO @PropertyRentReview (PROP_CODE,NewRentStartDate,RentEndDate,PropertyRentRent) VALUES
(1008,29/11/2012,21/07/2013,400)
(1008,22/07/2013,24/11/2013,400)
(1008,25/11/2013,14/06/2014,400)
(1008,15/06/2014,30/11/2014,425)
(1008,1/12/2014,26/07/2015,425)
(1008,27/07/2015,1/01/2050,440)
(1030,29/11/2012,21/07/2013,470)
(1030,22/07/2013,24/11/2013,530)
(1030,25/11/2013,14/06/2014,530)
(1030,15/06/2014,30/11/2014,530)
(1030,1/12/2014,26/07/2015,530)
(1030,27/07/2015,1/01/2050,550)

DECLARE @TenantPayment TABLE 
  ( Prop_Code INT
  ,WeeklyRent INT
  ,DebitDate DATEtime
  ,Month varchar(10)
  ,Year DATEtime)

INSERT INTO @table (PROP_CODE,WeeklyRent,DebitDate,Month,Year) VALUES
(1008,202.53,2/06/2014,June,2014)
(1008,202.53,9/06/2014,June,2014)
(1008,206.38,16/06/2014,June,2014)
(1008,206.38,23/06/2014,June,2014)
(1008,206.38,30/06/2014,June,2014)
(1008,206.38,7/07/2014,July,2014)
(1008,206.38,14/07/2014,July,2014)
(1008,206.38,21/07/2014,July,2014)
(1008,206.38,28/07/2014,July,2014)
(1008,206.38,4/08/2014,August,2014)
(1008,206.38,11/08/2014,August,2014)
(1008,206.38,18/08/2014,August,2014)
(1008,206.38,1/09/2014,September,2014
(1008,206.38,8/09/2014,September,2014)
(1008,206.38,15/09/2014,September,2014
(1008,206.38,22/09/2014,September,2014)
(1008,206.38,29/09/2014,September,2014)
(1008,206.38,7/10/2014,October,2014)
(1008,206.38,13/10/2014,October,2014)
(1008,206.38,20/10/2014,October,2014)
(1008,206.38,27/10/2014,October,2014)
(1008,206.38,3/11/2014,November,2014)
(1008,206.38,10/11/2014,November,2014)
(1008,206.38,17/11/2014,November,2014)
(1008,206.38,24/11/2014,November,2014)
(1008,206.38,1/12/2014,December,2014)
(1008,206.38,8/12/2014,December,2014)
(1008,206.38,15/12/2014,December,2014)
(1008,209.04,22/12/2014,December,2014
(1008,209.04,29/12/2014,December,2014
(1008,206.38,25/08/2014,August,2014)
(1008,209.04,23/02/2015,February,2015)
(1008,209.04,26/01/2015,January,2015)
(1008,209.04,2/02/2015,February,2015)
(1008,209.04,9/02/2015,February,2015)
(1008,209.04,16/02/2015,February,2015)
(1008,209.04,5/01/2015,January,2015)
(1008,209.04,12/01/2015,January,2015)
(1008,209.04,19/01/2015,January,2015)
(1030,458.09,29/12/2014,December,2014)
(1030,353.41,22/12/2014,December,2014)
(1030,353.41,15/12/2014,December,2014)
(1030,353.41,8/12/2014,December,2014)
(1030,353.41,1/12/2014,December,2014)
(1030,353.41,24/11/2014,November,2014)
(1030,353.41,17/11/2014,November,2014)
(1030,353.41,10/11/2014,November,2014)
(1030,353.41,3/11/2014,November,2014)
(1030,353.41,27/10/2014,October,2014)
(1030,353.41,20/10/2014,October,2014)
(1030,353.41,13/10/2014,October,2014)
(1030,353.41,7/10/2014,October,2014)
(1030,353.41,29/09/2014,September,2014)
(1030,353.41,22/09/2014,September,2014)
(1030,353.41,15/09/2014,September,2014)
(1030,353.41,8/09/2014,September,2014)
(1030,353.41,1/09/2014,September,2014)
(1030,353.41,18/08/2014,August,2014)
(1030,353.41,11/08/2014,August,2014)
(1030,353.41,4/08/2014,August,2014)
(1030,353.41,28/07/2014,July,2014)
(1030,353.41,21/07/2014,July,2014)
(1030,353.41,14/07/2014,July,2014)
(1030,353.41,7/07/2014,July,2014)
(1030,353.41,30/06/2014,June,2014)
(1030,353.41,23/06/2014,June,2014)
(1030,254.02,16/06/2014,June,2014)
(1030,254.02,9/06/2014,June,2014)
(1030,254.02,2/06/2014,June,2014)
(1030,353.41,25/08/2014,August,2014)
(1030,458.09,23/02/2015,February,2015)
(1030,458.09,16/02/2015,February,2015)
(1030,458.09,9/02/2015,February,2015)
(1030,458.09,2/02/2015,February,2015)
(1030,458.09,26/01/2015,January,2015)
(1030,458.09,5/01/2015,January,2015)
(1030,458.09,19/01/2015,January,2015)
(1030,458.09,12/01/2015,January,2015)

Thanks

Singh


  • Edited by jaggy99 Friday, June 19, 2015 3:48 PM more info
June 19th, 2015 3:42pm

It's great that you post scripts with CREATE TABLE and INSERT statements, but, please, next time, test them before you post. There were a number of errors in your script. I have some household work to do at home, so I only came as far as cleaning up your script. (Had the scripts been correct from the start, I could have spent that time on solving the problem.)

My version of the scripts is below, in case someone else want to give this a go:

DECLARE @vw_temp_tenant_balance_final TABLE
    (    Prop_Code                                INT
     ,TransactionDate                        DATEtime
     ,TransactionDescription         VarChar(20)
     ,RentCharge                            decimal(8,2)
        ,PaymentReceived                    decimal(8,2)
        ,NL_DATE_SQL                        DATEtime
        ,NL_MONTH                                tinyint
        ,NL_YEAR                             int
)

INSERT INTO @vw_temp_tenant_balance_final (Prop_Code,TransactionDate,TransactionDescription,RentCharge,PaymentReceived,NL_DATE_SQL,NL_MONTH,NL_YEAR) VALUES
(1008, '04/02/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 2, 2013 ),
(1008, '11/02/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 2, 2013 ),
(1008, '07/02/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 2, 2013 ),
(1008, '07/02/2013',    'Direct EFT', NULL, 279, '21/06/2013', 2, 2013 ),
(1008, '18/02/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 2, 2013 ),
(1008, '22/02/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 2, 2013 ),
(1008, '25/02/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 2, 2013 ),
(1008, '04/03/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 3, 2013 ),
(1008, '07/03/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 3, 2013 ),
(1008, '11/03/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 3, 2013 ),
(1008, '18/03/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 3, 2013 ),
(1008, '21/03/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 3, 2013 ),
(1008, '25/03/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 3, 2013 ),
(1008, '01/04/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 4, 2013 ),
(1008, '04/04/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 4, 2013 ),
(1008, '08/04/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 4, 2013 ),
(1008, '15/04/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 4, 2013 ),
(1008, '18/04/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 4, 2013 ),
(1008, '18/04/2013',    'Direct EFT', NULL, 185.3, '21/06/2013', 4, 2013 ),
(1008, '22/04/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 4, 2013 ),
(1008, '29/04/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 4, 2013 ),
(1008, '02/05/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 5, 2013 ),
(1008, '06/05/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 5, 2013 ),
(1008, '13/05/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 5, 2013 ),
(1008, '16/05/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 5, 2013 ),
(1008, '20/05/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 5, 2013 ),
(1008, '27/05/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 5, 2013 ),
(1008, '30/05/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 5, 2013 ),
(1008, '03/06/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 6, 2013 ),
(1008, '10/06/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 6, 2013 ),
(1008, '13/06/2013',    'Direct EFT', NULL, 382.15, '21/06/2013', 6, 2013 ),
(1008, '17/06/2013',    'Rent Debit', 191.07, NULL, '21/06/2013', 6, 2013 ),
(1008, '24/06/2013',    'Rent Debit', 191.07, NULL, '30/08/2013', 6, 2013 ),
(1008, '27/06/2013',    'Direct EFT', NULL, 382.15, '30/08/2013', 6, 2013 ),
(1008, '01/07/2013',    'Rent Debit', 191.07, NULL, '09/08/2013', 7, 2013 ),
(1008, '08/07/2013',    'Rent Debit', 191.07, NULL, '09/08/2013', 7, 2013 ),
(1008, '11/07/2013',    'Direct EFT', NULL, 382.15, '09/08/2013', 7, 2013 ),
(1008, '15/07/2013',    'Rent Debit', 191.07, NULL, '09/08/2013', 7, 2013 ),
(1008, '22/07/2013',    'Rent Debit', 191.07, NULL, '09/08/2013', 7, 2013 ),
(1008, '25/07/2013',    'Direct EFT', NULL, 382.15, '09/08/2013', 7, 2013 ),
(1008, '25/07/2013',    'Direct EFT', NULL, 234.7, '09/08/2013', 7, 2013 ),
(1008, '29/07/2013',    'Rent Debit', 191.07, NULL, '09/08/2013', 7, 2013 ),
(1008, '05/08/2013',    'Rent Debit', 191.07, NULL, '30/08/2013', 8, 2013 ),
(1008, '08/08/2013',    'Direct EFT', NULL, 382.15, '30/08/2013', 8, 2013 ),
(1008, '12/08/2013',    'Rent Debit', 202.33, NULL, '30/08/2013', 8, 2013 ),
(1008, '19/08/2013',    'Rent Debit', 202.33, NULL, '30/08/2013', 8, 2013 ),
(1008, '22/08/2013',    'Direct EFT', NULL, 382.15, '30/08/2013', 8, 2013 ),
(1008, '26/08/2013',    'Rent Debit', 202.33, NULL, '30/08/2013', 8, 2013 ),
(1008, '02/09/2013',    'Rent Debit', 202.33, NULL, '02/09/2013', 9, 2013 ),
(1008, '05/09/2013',    'Direct EFT', NULL, 382.15, '06/09/2013', 9, 2013 ),
(1008, '09/09/2013',    'Rent Debit', 202.33, NULL, '16/09/2013', 9, 2013 ),
(1008, '16/09/2013',    'Rent Debit', 202.33, NULL, '16/09/2013', 9, 2013 ),
(1008, '19/09/2013',    'Direct EFT', NULL, 382.15, '20/09/2013', 9, 2013 ),
(1008, '23/09/2013',    'Rent Debit', 202.33, NULL, '23/09/2013', 9, 2013 ),
(1008, '30/09/2013',    'Rent Debit', 202.33, NULL, '30/09/2013', 9, 2013 ),
(1008, '03/10/2013',    'Direct EFT', NULL, 382.15, '08/10/2013', 10, 2013 ),
(1008, '07/10/2013',    'Rent Debit', 202.33, NULL, '08/10/2013', 10, 2013 ),
(1008, '14/10/2013',    'Rent Debit', 202.33, NULL, '14/10/2013', 10, 2013 ),
(1008, '17/10/2013',    'Direct EFT', NULL, 382.15, '18/10/2013', 10, 2013 ),
(1008, '21/10/2013',    'Rent Debit', 202.33, NULL, '21/10/2013', 10, 2013 ),
(1008, '28/10/2013',    'Rent Debit', 202.33, NULL, '28/10/2013', 10, 2013 ),
(1008, '31/10/2013',    'Direct EFT', NULL, 382.15, '01/11/2013', 11, 2013 ),
(1008, '04/11/2013',    'Rent Debit', 202.33, NULL, '04/11/2013', 11, 2013 ),
(1008, '11/11/2013',    'Rent Debit', 202.33, NULL, '11/11/2013', 11, 2013 ),
(1008, '14/11/2013',    'Direct EFT', NULL, 382.15, '15/11/2013', 11, 2013 ),
(1008, '18/11/2013',    'Rent Debit', 202.33, NULL, '18/11/2013', 11, 2013 ),
(1008, '25/11/2013',    'Rent Debit', 202.53, NULL, '25/11/2013', 11, 2013 ),
(1008, '28/11/2013',    'Direct EFT', NULL, 418.44, '29/11/2013', 11, 2013 ),
(1008, '28/11/2013',    'Direct EFT', NULL, 405.05, '29/11/2013', 11, 2013 ),
(1008, '28/11/2013',    'Direct EFT', NULL, 253, '29/11/2013', 11, 2013 ),
(1008, '02/12/2013',    'Rent Debit', 202.53, NULL, '02/12/2013', 12, 2013 ),
(1008, '09/12/2013',    'Rent Debit', 202.53, NULL, '09/12/2013', 12, 2013 ),
(1008, '12/12/2013',    'Direct EFT', NULL, 405.05, '13/12/2013', 12, 2013 ),
(1008, '16/12/2013',    'Rent Debit', 202.53, NULL, '20/12/2013', 12, 2013 ),
(1008, '23/12/2013',    'Rent Debit', 202.53, NULL, '23/12/2013', 12, 2013 ),
(1008, '30/12/2013',    'Rent Debit', 202.53, NULL, '30/12/2013', 12, 2013 ),
(1008, '27/12/2013',    'Direct EFT', NULL, 405.05, '02/01/2014', 1, 2014 ),
(1008, '06/01/2014',    'Rent Debit', 202.53, NULL, '06/01/2014', 1, 2014 ),
(1008, '09/01/2014',    'Direct EFT', NULL, 405.05, '10/01/2014', 1, 2014 ),
(1008, '13/01/2014',    'Rent Debit', 202.53, NULL, '13/01/2014', 1, 2014 ),
(1008, '20/01/2014',    'Rent Debit', 202.53, NULL, '20/01/2014', 1, 2014 ),
(1008, '23/01/2014',    'Direct EFT', NULL, 405.05, '28/01/2014', 1, 2014 ),
(1008, '27/01/2014',    'Rent Debit', 202.53, NULL, '28/01/2014', 1, 2014 ),
(1008, '03/02/2014',    'Rent Debit', 202.53, NULL, '03/02/2014', 2, 2014 ),
(1008, '06/02/2014',    'Direct EFT', NULL, 405.05, '07/02/2014', 2, 2014 ),
(1008, '06/02/2014',    'Direct EFT', NULL, 248.8, '07/02/2014', 2, 2014 ),
(1008, '10/02/2014',    'Rent Debit', 202.53, NULL, '10/02/2014', 2, 2014 ),
(1008, '17/02/2014',    'Rent Debit', 202.53, NULL, '17/02/2014', 2, 2014 ),
(1008, '20/02/2014',    'Rent Debit Adjust', NULL, NULL, '20/02/2014', 2, 2014 ),
(1008, '20/02/2014',    'Direct EFT', NULL, 405.05, '21/02/2014', 2, 2014 ),
(1008, '24/02/2014',    'Rent Debit', 202.53, NULL, '24/02/2014', 2, 2014 ),
(1008, '03/03/2014',    'Rent Debit', 202.53, NULL, '03/03/2014', 3, 2014 ),
(1008, '10/03/2014',    'Rent Debit', 202.53, NULL, '10/03/2014', 3, 2014 ),
(1008, '13/03/2014',    'Direct EFT', NULL, 405.05, '17/03/2014', 3, 2014 ),
(1008, '17/03/2014',    'Rent Debit', 202.53, NULL, '17/03/2014', 3, 2014 ),
(1008, '24/03/2014',    'Rent Debit', 202.53, NULL, '24/03/2014', 3, 2014 ),
(1008, '27/03/2014',    'Direct EFT', NULL, 405.05, '28/03/2014', 3, 2014 ),
(1008, '31/03/2014',    'Rent Debit', 202.53, NULL, '31/03/2014', 3, 2014 ),
(1008, '07/04/2014',    'Rent Debit', 202.53, NULL, '07/04/2014', 4, 2014 ),
(1008, '10/04/2014',    'Direct EFT', NULL, 405.05, '14/04/2014', 4, 2014 ),
(1008, '14/04/2014',    'Rent Debit', 202.53, NULL, '14/04/2014', 4, 2014 ),
(1008, '21/04/2014',    'Rent Debit', 202.53, NULL, '21/04/2014', 4, 2014 ),
(1008, '28/04/2014',    'Rent Debit', 202.53, NULL, '28/04/2014', 4, 2014 ),
(1008, '24/04/2014',    'Direct EFT', NULL, 405.05, '28/04/2014', 4, 2014 ),
(1008, '05/05/2014',    'Rent Debit', 202.53, NULL, '05/05/2014', 5, 2014 ),
(1008, '08/05/2014',    'Direct EFT', NULL, 405.05, '14/05/2014', 5, 2014 ),
(1008, '12/05/2014',    'Rent Debit', 202.53, NULL, '12/05/2014', 5, 2014 ),
(1008, '19/05/2014',    'Rent Debit', 202.53, NULL, '19/05/2014', 5, 2014 ),
(1008, '22/05/2014',    'Direct EFT', NULL, 405.05, '23/05/2014', 5, 2014 ),
(1008, '26/05/2014',    'Rent Debit', 202.53, NULL, '26/05/2014', 5, 2014 ),
(1008, '02/06/2014',    'Rent Debit', 202.53, NULL, '02/06/2014', 6, 2014 ),
(1008, '05/06/2014',    'Direct EFT', NULL, 405.05, '06/06/2014', 6, 2014 ),
(1008, '05/06/2014',    'Direct EFT', NULL, 214.6, '06/06/2014', 6, 2014 ),
(1008, '09/06/2014',    'Rent Debit', 202.53, NULL, '09/06/2014', 6, 2014 ),
(1008, '16/06/2014',    'Rent Debit', 206.38, NULL, '16/06/2014', 6, 2014 ),
(1008, '19/06/2014',    'Direct EFT', NULL, 412.76, '23/06/2014', 6, 2014 ),
(1008, '23/06/2014',    'Rent Debit', 206.38, NULL, '23/06/2014', 6, 2014 ),
(1008, '30/06/2014',    'Rent Debit', 206.38, NULL, '30/06/2014', 6, 2014 ),
(1008, '03/07/2014',    'Direct EFT', NULL, 412.76, '04/07/2014', 7, 2014 ),
(1008, '07/07/2014',    'Rent Debit', 206.38, NULL, '07/07/2014', 7, 2014 ),
(1008, '14/07/2014',    'Rent Debit', 206.38, NULL, '14/07/2014', 7, 2014 ),
(1008, '17/07/2014',    'Direct EFT', NULL, 412.75, '18/07/2014', 7, 2014 ),
(1008, '21/07/2014',    'Rent Debit', 206.38, NULL, '21/07/2014', 7, 2014 ),
(1008, '28/07/2014',    'Rent Debit', 206.38, NULL, '28/07/2014', 7, 2014 ),
(1008, '31/07/2014',    'Direct EFT', NULL, 412.75, '01/08/2014', 7, 2014 ),
(1008, '31/07/2014',    'Direct EFT', NULL, 221.7, '01/08/2014', 7, 2014 ),
(1008, '04/08/2014',    'Rent Debit', 206.38, NULL, '04/08/2014', 8, 2014 ),
(1008, '11/08/2014',    'Rent Debit', 206.38, NULL, '11/08/2014', 8, 2014 ),
(1008, '14/08/2014',    'Direct EFT', NULL, 412.75, '15/08/2014', 8, 2014 ),
(1008, '18/08/2014',    'Rent Debit', 206.38, NULL, '18/08/2014', 8, 2014 ),
(1008, '25/08/2014',    'Rent Debit', 206.38, NULL, '25/08/2014', 8, 2014 ),
(1008, '28/08/2014',    'Direct EFT', NULL, 412.75, '29/08/2014', 8, 2014 ),
(1008, '01/09/2014',    'Rent Debit', 206.38, NULL, '01/09/2014', 9, 2014 ),
(1008, '08/09/2014',    'Rent Debit', 206.38, NULL, '08/09/2014', 9, 2014 ),
(1008, '11/09/2014',    'Direct EFT', NULL, 412.75, '12/09/2014', 9, 2014 ),
(1008, '15/09/2014',    'Rent Debit', 206.38, NULL, '15/09/2014', 9, 2014 ),
(1008, '22/09/2014',    'Rent Debit', 206.38, NULL, '22/09/2014', 9, 2014 ),
(1008, '25/09/2014',    'Direct EFT', NULL, 412.75, '26/09/2014', 9, 2014 ),
(1008, '29/09/2014',    'Rent Debit', 206.38, NULL, '29/09/2014', 9, 2014 ),
(1008, '06/10/2014',    'Rent Debit', 206.38, NULL, '07/10/2014', 10, 2014 ),
(1008, '09/10/2014',    'Direct EFT', NULL, 412.75, '10/10/2014', 10, 2014 ),
(1008, '13/10/2014',    'Rent Debit', 206.38, NULL, '13/10/2014', 10, 2014 ),
(1008, '20/10/2014',    'Rent Debit', 206.38, NULL, '20/10/2014', 10, 2014 ),
(1008, '23/10/2014',    'Direct EFT', NULL, 412.75, '24/10/2014', 10, 2014 ),
(1008, '27/10/2014',    'Rent Debit', 206.38, NULL, '27/10/2014', 10, 2014 ),
(1008, '03/11/2014',    'Rent Debit', 206.38, NULL, '03/11/2014', 11, 2014 ),
(1008, '10/11/2014',    'Rent Debit', 206.38, NULL, '10/11/2014', 11, 2014 ),
(1008, '07/11/2014',    'Direct EFT', NULL, 412.75, '10/11/2014', 11, 2014 ),
(1008, '17/11/2014',    'Rent Debit', 206.38, NULL, '17/11/2014', 11, 2014 ),
(1008, '20/11/2014',    'Direct EFT', NULL, 412.75, '21/11/2014', 11, 2014 ),
(1008, '24/11/2014',    'Rent Debit', 206.38, NULL, '24/11/2014', 11, 2014 ),
(1008, '01/12/2014',    'Rent Debit', 206.38, NULL, '01/12/2014', 12, 2014 ),
(1008, '08/12/2014',    'Rent Debit', 206.38, NULL, '08/12/2014', 12, 2014 ),
(1008, '04/12/2014',    'Direct EFT', NULL, 412.75, '08/12/2014', 12, 2014 ),
(1008, '15/12/2014',    'Rent Debit', 206.38, NULL, '15/12/2014', 12, 2014 ),
(1008, '18/12/2014',    'Direct EFT', NULL, 418.1, '19/12/2014', 12, 2014 ),
(1008, '22/12/2014',    'Rent Debit', 209.04, NULL, '22/12/2014', 12, 2014 ),
(1008, '29/12/2014',    'Rent Debit', 209.04, NULL, '29/12/2014', 12, 2014 ),
(1008, '05/01/2015',    'Rent Debit', 209.04, NULL, '05/01/2015', 1, 2015 ),
(1008, '02/01/2015',    'Direct EFT', NULL, 418.1, '05/01/2015', 1, 2015 ),
(1008, '12/01/2015',    'Rent Debit', 209.04, NULL, '12/01/2015', 1, 2015 ),
(1008, '13/01/2015',    'Eftpos',         NULL, -436.29, '13/01/2015', 1, 2015 ),
(1008, '13/01/2015',    'Eftpos',         NULL, 0, '01/01/1900', '    ', '        ' ),
(1008, '16/01/2015',    'Direct EFT', NULL, 418.1, '16/01/2015', 1, 2015 ),
(1008, '19/01/2015',    'Rent Debit', 209.04, NULL, '19/01/2015', 1, 2015 ),
(1008, '26/01/2015',    'Rent Debit', 209.04, NULL, '26/01/2015', 1, 2015 ),
(1008, '29/01/2015',    'Direct EFT', NULL, 418.1, '30/01/2015', 1, 2015 ),
(1008, '02/02/2015',    'Rent Debit', 209.04, NULL, '02/02/2015', 2, 2015 ),
(1008, '09/02/2015',    'Rent Debit', 209.04, NULL, '09/02/2015', 2, 2015 ),
(1008, '12/02/2015',    'Direct EFT', NULL, 418.1, '13/02/2015', 2, 2015 ),
(1008, '16/02/2015',    'Rent Debit', 209.04, NULL, '16/02/2015', 2, 2015 ),
(1008, '23/02/2015',    'Rent Debit', 209.04, NULL, '23/02/2015', 2, 2015 ),
(1008, '24/02/2015',    'Direct EFT', NULL, 214.3, '25/02/2015', 2, 2015 ),
(1008, '02/03/2015',    'Rent Debit', 209.04, NULL, '02/03/2015', 3, 2015 ),
(1008, '27/02/2015',    'Direct EFT', NULL, 418.1, '02/03/2015', 3, 2015 ),
(1008, '09/03/2015',    'Rent Debit', 209.04, NULL, '09/03/2015', 3, 2015 ),
(1008, '16/03/2015',    'Rent Debit', 209.04, NULL, '16/03/2015', 3, 2015 ),
(1008, '13/03/2015',    'Direct EFT', NULL, 418.1, '16/03/2015', 3, 2015 ),
(1008, '23/03/2015',    'Rent Debit', 209.04, NULL, '23/03/2015', 3, 2015 ),
(1008, '26/03/2015',    'Direct EFT', NULL, 418.1, '27/03/2015', 3, 2015 ),
(1008, '30/03/2015',    'Rent Debit', 209.04, NULL, '30/03/2015', 3, 2015 ),
(1008, '06/04/2015',    'Rent Debit', 209.04, NULL, '06/04/2015', 4, 2015 ),
(1008, '09/04/2015',    'Direct EFT', NULL, 418.1, '10/04/2015', 4, 2015 ),
(1008, '13/04/2015',    'Rent Debit', 209.04, NULL, '13/04/2015', 4, 2015 ),
(1008, '20/04/2015',    'Rent Debit', 209.04, NULL, '20/04/2015', 4, 2015 ),
(1008, '23/04/2015',    'Direct EFT', NULL, 418.1, '24/04/2015', 4, 2015 ),
(1008, '27/04/2015',    'Rent Debit', 209.04, NULL, '27/04/2015', 4, 2015 ),
(1008, '04/05/2015',    'Rent Debit', 209.04, NULL, '04/05/2015', 5, 2015 ),
(1008, '07/05/2015',    'Direct EFT', NULL, 418.1, '08/05/2015', 5, 2015 ),
(1008, '11/05/2015',    'Rent Debit', 209.04, NULL, '11/05/2015', 5, 2015 ),
(1008, '14/05/2015',    'Direct EFT', NULL, 209.8, '15/05/2015', 5, 2015 ),
(1008, '18/05/2015',    'Rent Debit', 209.04, NULL, '18/05/2015', 5, 2015 ),
(1008, '21/05/2015',    'Direct EFT', NULL, 418.1, '22/05/2015', 5, 2015 ),
(1008, '25/05/2015',    'Rent Debit', 209.04, NULL, '25/05/2015', 5, 2015 ),
(1008, '01/06/2015',    'Rent Debit', 209.04, NULL, '01/06/2015', 6, 2015 ),
(1008, '04/06/2015',    'Direct EFT', NULL, 418.1, '05/06/2015', 6, 2015 ),
(1008, '08/06/2015',    'Rent Debit', 209.04, NULL, '08/06/2015', 6, 2015 ),
(1008, '15/06/2015',    'Rent Debit', 209.04, NULL, '15/06/2015', 6, 2015 ),
(1008, '18/06/2015',    'Direct EFT', NULL, 418.1, '19/06/2015', 6, 2015 )

DECLARE @R110 TABLE
    ( Prop_Code             INT
    ,DATE_FROM_SQL        DATEtime
    ,DATE_TO_SQL             DATEtime
    ,PropertyRent        INT
    )

INSERT INTO @R110 (Prop_Code,DATE_FROM_SQL,DATE_TO_SQL,PropertyRent) VALUES
(1008, '29/11/2012',    '21/07/2013', '400' ),
(1008, '22/07/2013',    '24/11/2013', '400' ),
(1008, '25/11/2013',    '14/06/2014', '400' ) ,
(1008, '15/06/2014',    '30/11/2014', '425' ),
(1008, '01/12/2014',    '26/07/2015', '425' ),
(1008, '27/07/2015',    '01/01/2050', '440' )
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 7:56am

Thanks Erland. Was just in hurry to get the scrip up. Well noted. :)

Thanks.

June 21st, 2015 8:53am

Jaggy,

thanks for the scripts, despite they took some fixes it's easier to have them rather than to import everything from csv files.

Here is the solution: 


SELECT   TransactionDate,
         TransactionDescription,
         RentCharge,
         PaymentReceived,
         NL_DATE_SQL,
         tb.[NL_MONTH],
         tb.[NL_YEAR],
         CASE WHEN fd.[MIN_TransactionDate] IS NOT NULL THEN revw.[PropertyRent] ELSE NULL END as [ReviewedRent]
FROM     @vw_temp_tenant_balance_final AS tb
LEFT JOIN	(SELECT		[MIN_TransactionDate] = MIN(TransactionDate),
						[NL_MONTH],
						[NL_YEAR],
						[Prop_Code]
			FROM		@vw_temp_tenant_balance_final
			WHERE		[TransactionDescription] = 'Rent Debit'
			GROUP BY	[NL_MONTH], [NL_YEAR], [Prop_Code]) AS fd -- first dates of the months, dates when Rent Review value should appear
ON			fd.[MIN_TransactionDate] = tb.TransactionDate AND fd.[Prop_Code] = tb.[Prop_Code]
LEFT JOIN	@PropertyRentReview AS revw
ON			tb.TransactionDate BETWEEN revw.[NewRentStartDate] AND revw.[RentEndDate]  
AND			revw.[Prop_Code] = tb.[Prop_Code]
ORDER BY TransactionDate;

Erland, if to straighten the task description it's possible to see that it's a classical task that we all had many times in our practice :

  1. detect "first in each group" (in a month in this particular case),
  2. display only for it a value from the second table that matches it's transaction date by a criterion.

And that's all, more takes the data review and the task understanding. 

TS, let us know is the solution doesn't match the task.

Artm.


Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 8:57am

Another tip: in the posting window, there is a funny button with arrows pointing from each other. This button permits you to insert code into the post without the posting UI mangling the code.

If I understand you correctly, what you need is this small modification to your query:

SELECT tb.Prop_Code                --Property Code
,tb.[TransactionDate]        --The date the Rent Debit was run which is Monday of every week
,tb.[TransactionDescription] --Shows where the transactions is a rebt debit or a payment
,CASE WHEN tb.TransactionDescription = 'Rent Debit'
     THEN R110.PropertyRent         
END AS MARKET_RENT    
 -- Market Rent of the property coming from table R110
,tb.[RentCharge]             -- The rent amount the tenant should actually should be paying
,tb.[PaymentReceived]        -- The payment the tenant made
,tb.NL_DATE_SQL              -- The date the transactions hit the finance nominal table
,tb.NL_MONTH
,tb.NL_YEAR
FROM @vw_temp_tenant_balance_final tb
LEFT OUTER JOIN @R110 R110 ON tb.Prop_Code = R110.Prop_Code
WHERE tb.Prop_Code = '1008'

That is, use CASE to make sure that there is only a value in the MARKET_RENT column when the transaction is Rent Debit.

June 21st, 2015 3:15pm

Here is the final version:

SET DATEFORMAT dmy; 

DECLARE @vw_temp_tenant_balance_final TABLE (
    [Prop_Code]				INT            ,
    TransactionDate			DATETIME       ,
    TransactionDescription	VARCHAR (MAX)  ,
    RentCharge				DECIMAL (18, 4),
    PaymentReceived			DECIMAL (18, 4),
    NL_DATE_SQL__RAW		VARCHAR (MAX)  , -- temporary ("cargo") field
    NL_DATE_SQL				DATETIME       , -- the field is not in use
    [NL_MONTH]              VARCHAR (MAX)  ,
    [NL_YEAR]               VARCHAR (MAX)  );

INSERT  INTO @vw_temp_tenant_balance_final ([Prop_Code], TransactionDate, TransactionDescription, RentCharge, PaymentReceived, NL_DATE_SQL__RAW, [NL_MONTH], [NL_YEAR])
VALUES                                    
(1008, '04/02/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '2', '2013'),
(1008, '11/02/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '2', '2013'),
(1008, '07/02/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '2', '2013'),
(1008, '07/02/2013', 'Direct EFT', NULL, '279', '21/06/2013', '2', '2013'),
(1008, '18/02/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '2', '2013'),
(1008, '22/02/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '2', '2013'),
(1008, '25/02/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '2', '2013'),
(1008, '04/03/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '3', '2013'),
(1008, '07/03/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '3', '2013'),
(1008, '11/03/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '3', '2013'),
(1008, '18/03/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '3', '2013'),
(1008, '21/03/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '3', '2013'),
(1008, '25/03/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '3', '2013'),
(1008, '01/04/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '4', '2013'),
(1008, '04/04/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '4', '2013'),
(1008, '08/04/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '4', '2013'),
(1008, '15/04/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '4', '2013'),
(1008, '18/04/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '4', '2013'),
(1008, '18/04/2013', 'Direct EFT', NULL, '185.3', '21/06/2013', '4', '2013'),
(1008, '22/04/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '4', '2013'),
(1008, '29/04/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '4', '2013'),
(1008, '02/05/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '5', '2013'),
(1008, '06/05/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '5', '2013'),
(1008, '13/05/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '5', '2013'),
(1008, '16/05/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '5', '2013'),
(1008, '20/05/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '5', '2013'),
(1008, '27/05/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '5', '2013'),
(1008, '30/05/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '5', '2013'),
(1008, '03/06/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '6', '2013'),
(1008, '10/06/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '6', '2013'),
(1008, '13/06/2013', 'Direct EFT', NULL, '382.15', '21/06/2013', '6', '2013'),
(1008, '17/06/2013', 'Rent Debit', '191.07', NULL, '21/06/2013', '6', '2013'),
(1008, '24/06/2013', 'Rent Debit', '191.07', NULL, '30/08/2013', '6', '2013'),
(1008, '27/06/2013', 'Direct EFT', NULL, '382.15', '30/08/2013', '6', '2013'),
(1008, '01/07/2013', 'Rent Debit', '191.07', NULL, '09/08/2013', '7', '2013'),
(1008, '08/07/2013', 'Rent Debit', '191.07', NULL, '09/08/2013', '7', '2013'),
(1008, '11/07/2013', 'Direct EFT', NULL, '382.15', '09/08/2013', '7', '2013'),
(1008, '15/07/2013', 'Rent Debit', '191.07', NULL, '09/08/2013', '7', '2013'),
(1008, '22/07/2013', 'Rent Debit', '191.07', NULL, '09/08/2013', '7', '2013'),
(1008, '25/07/2013', 'Direct EFT', NULL, '382.15', '09/08/2013', '7', '2013'),
(1008, '25/07/2013', 'Direct EFT', NULL, '234.7', '09/08/2013', '7', '2013'),
(1008, '29/07/2013', 'Rent Debit', '191.07', NULL, '09/08/2013', '7', '2013'),
(1008, '05/08/2013', 'Rent Debit', '191.07', NULL, '30/08/2013', '8', '2013'),
(1008, '08/08/2013', 'Direct EFT', NULL, '382.15', '30/08/2013', '8', '2013'),
(1008, '12/08/2013', 'Rent Debit', '202.33', NULL, '30/08/2013', '8', '2013'),
(1008, '19/08/2013', 'Rent Debit', '202.33', NULL, '30/08/2013', '8', '2013'),
(1008, '22/08/2013', 'Direct EFT', NULL, '382.15', '30/08/2013', '8', '2013'),
(1008, '26/08/2013', 'Rent Debit', '202.33', NULL, '30/08/2013', '8', '2013'),
(1008, '02/09/2013', 'Rent Debit', '202.33', NULL, '02/09/2013', '9', '2013'),
(1008, '05/09/2013', 'Direct EFT', NULL, '382.15', '06/09/2013', '9', '2013'),
(1008, '09/09/2013', 'Rent Debit', '202.33', NULL, '16/09/2013', '9', '2013'),
(1008, '16/09/2013', 'Rent Debit', '202.33', NULL, '16/09/2013', '9', '2013'),
(1008, '19/09/2013', 'Direct EFT', NULL, '382.15', '20/09/2013', '9', '2013'),
(1008, '23/09/2013', 'Rent Debit', '202.33', NULL, '23/09/2013', '9', '2013'),
(1008, '30/09/2013', 'Rent Debit', '202.33', NULL, '30/09/2013', '9', '2013'),
(1008, '03/10/2013', 'Direct EFT', NULL, '382.15', '08/10/2013', '10', '2013'),
(1008, '07/10/2013', 'Rent Debit', '202.33', NULL, '08/10/2013', '10', '2013'),
(1008, '14/10/2013', 'Rent Debit', '202.33', NULL, '14/10/2013', '10', '2013'),
(1008, '17/10/2013', 'Direct EFT', NULL, '382.15', '18/10/2013', '10', '2013'),
(1008, '21/10/2013', 'Rent Debit', '202.33', NULL, '21/10/2013', '10', '2013'),
(1008, '28/10/2013', 'Rent Debit', '202.33', NULL, '28/10/2013', '10', '2013'),
(1008, '31/10/2013', 'Direct EFT', NULL, '382.15', '01/11/2013', '11', '2013'),
(1008, '04/11/2013', 'Rent Debit', '202.33', NULL, '04/11/2013', '11', '2013'),
(1008, '11/11/2013', 'Rent Debit', '202.33', NULL, '11/11/2013', '11', '2013'),
(1008, '14/11/2013', 'Direct EFT', NULL, '382.15', '15/11/2013', '11', '2013'),
(1008, '18/11/2013', 'Rent Debit', '202.33', NULL, '18/11/2013', '11', '2013'),
(1008, '25/11/2013', 'Rent Debit', '202.53', NULL, '25/11/2013', '11', '2013'),
(1008, '28/11/2013', 'Direct EFT', NULL, '418.44', '29/11/2013', '11', '2013'),
(1008, '28/11/2013', 'Direct EFT', NULL, '405.05', '29/11/2013', '11', '2013'),
(1008, '28/11/2013', 'Direct EFT', NULL, '253', '29/11/2013', '11', '2013'),
(1008, '02/12/2013', 'Rent Debit', '202.53', NULL, '02/12/2013', '12', '2013'),
(1008, '09/12/2013', 'Rent Debit', '202.53', NULL, '09/12/2013', '12', '2013'),
(1008, '12/12/2013', 'Direct EFT', NULL, '405.05', '13/12/2013', '12', '2013'),
(1008, '16/12/2013', 'Rent Debit', '202.53', NULL, '20/12/2013', '12', '2013'),
(1008, '23/12/2013', 'Rent Debit', '202.53', NULL, '23/12/2013', '12', '2013'),
(1008, '30/12/2013', 'Rent Debit', '202.53', NULL, '30/12/2013', '12', '2013'),
(1008, '27/12/2013', 'Direct EFT', NULL, '405.05', '02/01/2014', '1', '2014'),
(1008, '06/01/2014', 'Rent Debit', '202.53', NULL, '06/01/2014', '1', '2014'),
(1008, '09/01/2014', 'Direct EFT', NULL, '405.05', '10/01/2014', '1', '2014'),
(1008, '13/01/2014', 'Rent Debit', '202.53', NULL, '13/01/2014', '1', '2014'),
(1008, '20/01/2014', 'Rent Debit', '202.53', NULL, '20/01/2014', '1', '2014'),
(1008, '23/01/2014', 'Direct EFT', NULL, '405.05', '28/01/2014', '1', '2014'),
(1008, '27/01/2014', 'Rent Debit', '202.53', NULL, '28/01/2014', '1', '2014'),
(1008, '03/02/2014', 'Rent Debit', '202.53', NULL, '03/02/2014', '2', '2014'),
(1008, '06/02/2014', 'Direct EFT', NULL, '405.05', '07/02/2014', '2', '2014'),
(1008, '06/02/2014', 'Direct EFT', NULL, '248.8', '07/02/2014', '2', '2014'),
(1008, '10/02/2014', 'Rent Debit', '202.53', NULL, '10/02/2014', '2', '2014'),
(1008, '17/02/2014', 'Rent Debit', '202.53', NULL, '17/02/2014', '2', '2014'),
(1008, '20/02/2014', 'Rent Debit Adjust', NULL, NULL, '20/02/2014', '2', '2014'),
(1008, '20/02/2014', 'Direct EFT', NULL, '405.05', '21/02/2014', '2', '2014'),
(1008, '24/02/2014', 'Rent Debit', '202.53', NULL, '24/02/2014', '2', '2014'),
(1008, '03/03/2014', 'Rent Debit', '202.53', NULL, '03/03/2014', '3', '2014'),
(1008, '10/03/2014', 'Rent Debit', '202.53', NULL, '10/03/2014', '3', '2014'),
(1008, '13/03/2014', 'Direct EFT', NULL, '405.05', '17/03/2014', '3', '2014'),
(1008, '17/03/2014', 'Rent Debit', '202.53', NULL, '17/03/2014', '3', '2014'),
(1008, '24/03/2014', 'Rent Debit', '202.53', NULL, '24/03/2014', '3', '2014'),
(1008, '27/03/2014', 'Direct EFT', NULL, '405.05', '28/03/2014', '3', '2014'),
(1008, '31/03/2014', 'Rent Debit', '202.53', NULL, '31/03/2014', '3', '2014'),
(1008, '07/04/2014', 'Rent Debit', '202.53', NULL, '07/04/2014', '4', '2014'),
(1008, '10/04/2014', 'Direct EFT', NULL, '405.05', '14/04/2014', '4', '2014'),
(1008, '14/04/2014', 'Rent Debit', '202.53', NULL, '14/04/2014', '4', '2014'),
(1008, '21/04/2014', 'Rent Debit', '202.53', NULL, '21/04/2014', '4', '2014'),
(1008, '28/04/2014', 'Rent Debit', '202.53', NULL, '28/04/2014', '4', '2014'),
(1008, '24/04/2014', 'Direct EFT', NULL, '405.05', '28/04/2014', '4', '2014'),
(1008, '05/05/2014', 'Rent Debit', '202.53', NULL, '05/05/2014', '5', '2014'),
(1008, '08/05/2014', 'Direct EFT', NULL, '405.05', '14/05/2014', '5', '2014'),
(1008, '12/05/2014', 'Rent Debit', '202.53', NULL, '12/05/2014', '5', '2014'),
(1008, '19/05/2014', 'Rent Debit', '202.53', NULL, '19/05/2014', '5', '2014'),
(1008, '22/05/2014', 'Direct EFT', NULL, '405.05', '23/05/2014', '5', '2014'),
(1008, '26/05/2014', 'Rent Debit', '202.53', NULL, '26/05/2014', '5', '2014'),
(1008, '02/06/2014', 'Rent Debit', '202.53', NULL, '02/06/2014', '6', '2014'),
(1008, '05/06/2014', 'Direct EFT', NULL, '405.05', '06/06/2014', '6', '2014'),
(1008, '05/06/2014', 'Direct EFT', NULL, '214.6', '06/06/2014', '6', '2014'),
(1008, '09/06/2014', 'Rent Debit', '202.53', NULL, '09/06/2014', '6', '2014'),
(1008, '16/06/2014', 'Rent Debit', '206.38', NULL, '16/06/2014', '6', '2014'),
(1008, '19/06/2014', 'Direct EFT', NULL, '412.76', '23/06/2014', '6', '2014'),
(1008, '23/06/2014', 'Rent Debit', '206.38', NULL, '23/06/2014', '6', '2014'),
(1008, '30/06/2014', 'Rent Debit', '206.38', NULL, '30/06/2014', '6', '2014'),
(1008, '03/07/2014', 'Direct EFT', NULL, '412.76', '04/07/2014', '7', '2014'),
(1008, '07/07/2014', 'Rent Debit', '206.38', NULL, '07/07/2014', '7', '2014'),
(1008, '14/07/2014', 'Rent Debit', '206.38', NULL, '14/07/2014', '7', '2014'),
(1008, '17/07/2014', 'Direct EFT', NULL, '412.75', '18/07/2014', '7', '2014'),
(1008, '21/07/2014', 'Rent Debit', '206.38', NULL, '21/07/2014', '7', '2014'),
(1008, '28/07/2014', 'Rent Debit', '206.38', NULL, '28/07/2014', '7', '2014'),
(1008, '31/07/2014', 'Direct EFT', NULL, '412.75', '01/08/2014', '7', '2014'),
(1008, '31/07/2014', 'Direct EFT', NULL, '221.7', '01/08/2014', '7', '2014'),
(1008, '04/08/2014', 'Rent Debit', '206.38', NULL, '04/08/2014', '8', '2014'),
(1008, '11/08/2014', 'Rent Debit', '206.38', NULL, '11/08/2014', '8', '2014'),
(1008, '14/08/2014', 'Direct EFT', NULL, '412.75', '15/08/2014', '8', '2014'),
(1008, '18/08/2014', 'Rent Debit', '206.38', NULL, '18/08/2014', '8', '2014'),
(1008, '25/08/2014', 'Rent Debit', '206.38', NULL, '25/08/2014', '8', '2014'),
(1008, '28/08/2014', 'Direct EFT', NULL, '412.75', '29/08/2014', '8', '2014'),
(1008, '01/09/2014', 'Rent Debit', '206.38', NULL, '01/09/2014', '9', '2014'),
(1008, '08/09/2014', 'Rent Debit', '206.38', NULL, '08/09/2014', '9', '2014'),
(1008, '11/09/2014', 'Direct EFT', NULL, '412.75', '12/09/2014', '9', '2014'),
(1008, '15/09/2014', 'Rent Debit', '206.38', NULL, '15/09/2014', '9', '2014'),
(1008, '22/09/2014', 'Rent Debit', '206.38', NULL, '22/09/2014', '9', '2014'),
(1008, '25/09/2014', 'Direct EFT', NULL, '412.75', '26/09/2014', '9', '2014'),
(1008, '29/09/2014', 'Rent Debit', '206.38', NULL, '29/09/2014', '9', '2014'),
(1008, '06/10/2014', 'Rent Debit', '206.38', NULL, '07/10/2014', '10', '2014'),
(1008, '09/10/2014', 'Direct EFT', NULL, '412.75', '10/10/2014', '10', '2014'),
(1008, '13/10/2014', 'Rent Debit', '206.38', NULL, '13/10/2014', '10', '2014'),
(1008, '20/10/2014', 'Rent Debit', '206.38', NULL, '20/10/2014', '10', '2014'),
(1008, '23/10/2014', 'Direct EFT', NULL, '412.75', '24/10/2014', '10', '2014'),
(1008, '27/10/2014', 'Rent Debit', '206.38', NULL, '27/10/2014', '10', '2014'),
(1008, '03/11/2014', 'Rent Debit', '206.38', NULL, '03/11/2014', '11', '2014'),
(1008, '10/11/2014', 'Rent Debit', '206.38', NULL, '10/11/2014', '11', '2014'),
(1008, '07/11/2014', 'Direct EFT', NULL, '412.75', '10/11/2014', '11', '2014'),
(1008, '17/11/2014', 'Rent Debit', '206.38', NULL, '17/11/2014', '11', '2014'),
(1008, '20/11/2014', 'Direct EFT', NULL, '412.75', '21/11/2014', '11', '2014'),
(1008, '24/11/2014', 'Rent Debit', '206.38', NULL, '24/11/2014', '11', '2014'),
(1008, '01/12/2014', 'Rent Debit', '206.38', NULL, '01/12/2014', '12', '2014'),
(1008, '08/12/2014', 'Rent Debit', '206.38', NULL, '08/12/2014', '12', '2014'),
(1008, '04/12/2014', 'Direct EFT', NULL, '412.75', '08/12/2014', '12', '2014'),
(1008, '15/12/2014', 'Rent Debit', '206.38', NULL, '15/12/2014', '12', '2014'),
(1008, '18/12/2014', 'Direct EFT', NULL, '418.1', '19/12/2014', '12', '2014'),
(1008, '22/12/2014', 'Rent Debit', '209.04', NULL, '22/12/2014', '12', '2014'),
(1008, '29/12/2014', 'Rent Debit', '209.04', NULL, '29/12/2014', '12', '2014'),
(1008, '05/01/2015', 'Rent Debit', '209.04', NULL, '05/01/2015', '1', '2015'),
(1008, '02/01/2015', 'Direct EFT', NULL, '418.1', '05/01/2015', '1', '2015'),
(1008, '12/01/2015', 'Rent Debit', '209.04', NULL, '12/01/2015', '1', '2015'),
(1008, '13/01/2015', 'Eftpos', NULL, '-436.29', '13/01/2015', '1', '2015'),
(1008, '13/01/2015', 'Eftpos', NULL, '0', '01/01/1900', '  ', '    '), -- '00/01/1900' replaced with '01/01/1900'
(1008, '16/01/2015', 'Direct EFT', NULL, '418.1', '16/01/2015', '1', '2015'),
(1008, '19/01/2015', 'Rent Debit', '209.04', NULL, '19/01/2015', '1', '2015'),
(1008, '26/01/2015', 'Rent Debit', '209.04', NULL, '26/01/2015', '1', '2015'),
(1008, '29/01/2015', 'Direct EFT', NULL, '418.1', '30/01/2015', '1', '2015'),
(1008, '02/02/2015', 'Rent Debit', '209.04', NULL, '02/02/2015', '2', '2015'),
(1008, '09/02/2015', 'Rent Debit', '209.04', NULL, '09/02/2015', '2', '2015'),
(1008, '12/02/2015', 'Direct EFT', NULL, '418.1', '13/02/2015', '2', '2015'),
(1008, '16/02/2015', 'Rent Debit', '209.04', NULL, '16/02/2015', '2', '2015'),
(1008, '23/02/2015', 'Rent Debit', '209.04', NULL, '23/02/2015', '2', '2015'),
(1008, '24/02/2015', 'Direct EFT', NULL, '214.3', '25/02/2015', '2', '2015'),
(1008, '02/03/2015', 'Rent Debit', '209.04', NULL, '02/03/2015', '3', '2015'),
(1008, '27/02/2015', 'Direct EFT', NULL, '418.1', '02/03/2015', '3', '2015'),
(1008, '09/03/2015', 'Rent Debit', '209.04', NULL, '09/03/2015', '3', '2015'),
(1008, '16/03/2015', 'Rent Debit', '209.04', NULL, '16/03/2015', '3', '2015'),
(1008, '13/03/2015', 'Direct EFT', NULL, '418.1', '16/03/2015', '3', '2015'),
(1008, '23/03/2015', 'Rent Debit', '209.04', NULL, '23/03/2015', '3', '2015'),
(1008, '26/03/2015', 'Direct EFT', NULL, '418.1', '27/03/2015', '3', '2015'),
(1008, '30/03/2015', 'Rent Debit', '209.04', NULL, '30/03/2015', '3', '2015'),
(1008, '06/04/2015', 'Rent Debit', '209.04', NULL, '06/04/2015', '4', '2015'),
(1008, '09/04/2015', 'Direct EFT', NULL, '418.1', '10/04/2015', '4', '2015'),
(1008, '13/04/2015', 'Rent Debit', '209.04', NULL, '13/04/2015', '4', '2015'),
(1008, '20/04/2015', 'Rent Debit', '209.04', NULL, '20/04/2015', '4', '2015'),
(1008, '23/04/2015', 'Direct EFT', NULL, '418.1', '24/04/2015', '4', '2015'),
(1008, '27/04/2015', 'Rent Debit', '209.04', NULL, '27/04/2015', '4', '2015'),
(1008, '04/05/2015', 'Rent Debit', '209.04', NULL, '04/05/2015', '5', '2015'),
(1008, '07/05/2015', 'Direct EFT', NULL, '418.1', '08/05/2015', '5', '2015'),
(1008, '11/05/2015', 'Rent Debit', '209.04', NULL, '11/05/2015', '5', '2015'),
(1008, '14/05/2015', 'Direct EFT', NULL, '209.8', '15/05/2015', '5', '2015'),
(1008, '18/05/2015', 'Rent Debit', '209.04', NULL, '18/05/2015', '5', '2015'),
(1008, '21/05/2015', 'Direct EFT', NULL, '418.1', '22/05/2015', '5', '2015'),
(1008, '25/05/2015', 'Rent Debit', '209.04', NULL, '25/05/2015', '5', '2015'),
(1008, '01/06/2015', 'Rent Debit', '209.04', NULL, '01/06/2015', '6', '2015'),
(1008, '04/06/2015', 'Direct EFT', NULL, '418.1', '05/06/2015', '6', '2015'),
(1008, '08/06/2015', 'Rent Debit', '209.04', NULL, '08/06/2015', '6', '2015'),
(1008, '15/06/2015', 'Rent Debit', '209.04', NULL, '15/06/2015', '6', '2015'),
(1008, '18/06/2015', 'Direct EFT', NULL, '418.1', '19/06/2015', '6', '2015');

UPDATE  @vw_temp_tenant_balance_final
SET		NL_DATE_SQL = CONVERT (DATETIME, NL_DATE_SQL__RAW, 103);

/*
-- demo script showing how the subquery works:

SELECT   TransactionDate,
         TransactionDescription,
         RentCharge,
         PaymentReceived,
         NL_DATE_SQL,
         tb.[NL_MONTH],
         tb.[NL_YEAR],
         CASE WHEN fd.[MIN_TransactionDate] IS NOT NULL THEN 'Here should appear a sum' ELSE '' END
FROM     @vw_temp_tenant_balance_final AS tb
         LEFT OUTER JOIN
         (SELECT   MIN(TransactionDate) AS [MIN_TransactionDate],
                   [NL_MONTH],
                   [NL_YEAR]
          FROM     @vw_temp_tenant_balance_final
          WHERE    [TransactionDescription] = 'Rent Debit'
          GROUP BY [NL_MONTH], [NL_YEAR]) AS fd -- first dates of the months, dates when Rent Review value should appear
         ON fd.[MIN_TransactionDate] = tb.TransactionDate
ORDER BY TransactionDate;
*/

DECLARE @PropertyRentReview TABLE (	[Prop_Code]			INT     ,
									[NewRentStartDate]	DATETIME,
									[RentEndDate]		DATETIME,
									[PropertyRent]		INT     );

INSERT INTO @PropertyRentReview ([Prop_Code], [NewRentStartDate], [RentEndDate], [PropertyRent])
VALUES                         
		(1008, CONVERT (DATETIME, '29/11/2012', 103), CONVERT (DATETIME, '21/07/2013', 103), 400),
		(1008, CONVERT (DATETIME, '22/07/2013', 103), CONVERT (DATETIME, '24/11/2013', 103), 400),
		(1008, CONVERT (DATETIME, '25/11/2013', 103), CONVERT (DATETIME, '14/06/2014', 103), 400),
		(1008, CONVERT (DATETIME, '15/06/2014', 103), CONVERT (DATETIME, '30/11/2014', 103), 425),
		(1008, CONVERT (DATETIME, '1/12/2014', 103), CONVERT (DATETIME, '26/07/2015', 103), 425),
		(1008, CONVERT (DATETIME, '27/07/2015', 103), CONVERT (DATETIME, '1/01/2050', 103), 440);

-- debug :
-- SELECT [NewRentStartDate],[RentEndDate],[PropertyRent] FROM @PropertyRentReview ORDER BY [NewRentStartDate];

/* v1 of the solution
SELECT   TransactionDate,
         TransactionDescription,
         RentCharge,
         PaymentReceived,
         NL_DATE_SQL,
         tb.[NL_MONTH],
         tb.[NL_YEAR],
         CASE WHEN fd.[MIN_TransactionDate] IS NOT NULL THEN revw.[PropertyRent] ELSE NULL END as [ReviewedRent]
FROM     @vw_temp_tenant_balance_final AS tb
LEFT JOIN	(SELECT		[MIN_TransactionDate] = MIN(TransactionDate),
						[NL_MONTH],
						[NL_YEAR],
						[Prop_Code]
			FROM		@vw_temp_tenant_balance_final
			WHERE		[TransactionDescription] = 'Rent Debit'
			GROUP BY	[NL_MONTH], [NL_YEAR], [Prop_Code]) AS fd -- first dates of the months, dates when Rent Review value should appear
ON			fd.[MIN_TransactionDate] = tb.TransactionDate AND fd.[Prop_Code] = tb.[Prop_Code]
LEFT JOIN	@PropertyRentReview AS revw
ON			tb.TransactionDate BETWEEN revw.[NewRentStartDate] AND revw.[RentEndDate]  
AND			revw.[Prop_Code] = tb.[Prop_Code]
ORDER BY TransactionDate;
*/

-- v2:
SELECT		TransactionDate,
			TransactionDescription,
			RentCharge,
			PaymentReceived,
			NL_DATE_SQL,
			tb.[NL_MONTH],
			tb.[NL_YEAR],
			CASE WHEN [TransactionDescription] = 'Rent Debit' THEN revw.[PropertyRent] ELSE NULL END as [PropertyRent]
FROM		@vw_temp_tenant_balance_final AS tb
LEFT JOIN	@PropertyRentReview AS revw
ON			tb.TransactionDate BETWEEN revw.[NewRentStartDate] AND revw.[RentEndDate]  
AND			revw.[Prop_Code] = tb.[Prop_Code]
ORDER BY TransactionDate;
P.S. Formatted with tsqltidy.com.

Artm.

Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 6:31pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics