Create From and To dates

• jaggy99

  Ten Centuries

  Points: 1308

  More actions

  April 1, 2017 at 6:08 am

  #322595

  Hi,

  I have the @rent table as below which holds the tenant rent charged values. The table has inconsistency as there are blank tenant values something I cannot control. Also the date columns are vchar as that how its in the actual table. Again something i cannot control and for the purpose of the report to pick the max date, the date columns need to changed from char to date.

  In certain cases the rent can be updated multiple times in a day or changed in the future (notice the created date_sql). The Date_changed_SQL indicates when the rent is actually applied. So in most cases there will be a duplicate DATE_CHANGED_SQL but based on the CREATED_DATE_SQL and Time the latest TOT_DEB_Val needs to be selected.

  Sample date: The sample data table query is in the attached file sampledata.txt
  Working SQL query that needs fixing: The SQL query that gives the desired result but need fixing is attached in the file rent_queryv1.txt

  Requirement:
  I'm trying to transform the table into a view to show the rent period ( From and To) and like to create a view as below:
  

  The below two issues need to be fixed in the attached query rent_queryv1.txt

  1) R23_SQL_ID = 29461 should be on the top as that's the first rent charged but it was inserted in 2016-08-16 (this happens as teams add\update rent amount in the future )

  2) R23_SQL_ID = 34057 should be excluded as the row below R23_SQL_ID = 34189) is the most recent based on the created date

  

  Thanks
  Jag

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 1, 2017 at 7:48 am

  #1936108

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• jaggy99

  Ten Centuries

  Points: 1308

  More actions

  April 1, 2017 at 4:52 pm

  #1936131

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  HI

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  Hi,

  that did fix the two issues but for some reason missing the tow records as highlighted below

  

  Thanks
  Jag

• jaggy99

  Ten Centuries

  Points: 1308

  More actions

  April 1, 2017 at 5:03 pm

  #1936132

  jaggy99 - Saturday, April 1, 2017 4:52 PM

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  HI

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  Hi,

  that did fix the two issues but for some reason missing the tow records as highlighted below

  

  Thanks
  Jag

  Again the highlighted row below is not appearing the result set.

  

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 2, 2017 at 1:38 am

  #1936163

  jaggy99 - Saturday, April 1, 2017 5:03 PM

  jaggy99 - Saturday, April 1, 2017 4:52 PM

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  HI

  J Livingston SQL - Saturday, April 1, 2017 7:48 AM

  possibly something along these lines...??

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL    ,
            ROW_NUMBER() oVER (PARTITION BY r.PROP_CODE,    r.TENANT_NUMB,    CAST(r.DATE_CHANGED_SQL AS DATE)
            ORDER BY CAST(r.TIME_CHANGED_SQL AS TIME) DESC) rn
            
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.from_date)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL
FROM
    cte_FixData fd
    where fd.rn =1

  Hi,

  that did fix the two issues but for some reason missing the tow records as highlighted below

  

  Thanks
  Jag

  Again the highlighted row below is not appearing the result set.

  

  your original code had this in the where clause...assumed you had it there for a reason   AND r.TIME_CHANGED_SQL <> ''.........???

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• jaggy99

  Ten Centuries

  Points: 1308

  More actions

  April 2, 2017 at 7:28 am

  #1936171

  Forgot to mention that im running SQL 2008 in production and LEAD function is not supported. Is there another way to achieve the same result?

  Thanks
  Jag

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  April 4, 2017 at 10:48 am

  #1936585

  jaggy99 - Sunday, April 2, 2017 7:28 AM

  Forgot to mention that im running SQL 2008 in production and LEAD function is not supported. Is there another way to achieve the same result?

  Thanks
  Jag

  As this is a SQL 2008 section of the forum, the responder should NOT have used LEAD.   Thus no nead to worry about having to mention that.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 4, 2017 at 10:57 am

  #1936590

  sgmunson - Tuesday, April 4, 2017 10:48 AM

  jaggy99 - Sunday, April 2, 2017 7:28 AM

  Forgot to mention that im running SQL 2008 in production and LEAD function is not supported. Is there another way to achieve the same result?

  Thanks
  Jag

  As this is a SQL 2008 section of the forum, the responder should NOT have used LEAD.   Thus no nead to worry about having to mention that.

  fair enough comment....but as the responder and reviweing the query.txt files provided by the OP which contained the following:

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL,            
            CREATED_DATE_SQL = CAST(r.CREATED_DATE_SQL AS DATE)
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.R23_SQL_ID)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL,
    fd.CREATED_DATE_SQL
FROM
    cte_FixData fd

  I completely forgot it was posted in 2008 forum.....:Whistling::Whistling::Whistling:

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  April 4, 2017 at 11:35 am

  #1936606

  J Livingston SQL - Tuesday, April 4, 2017 10:57 AM

  sgmunson - Tuesday, April 4, 2017 10:48 AM

  jaggy99 - Sunday, April 2, 2017 7:28 AM

  Forgot to mention that im running SQL 2008 in production and LEAD function is not supported. Is there another way to achieve the same result?

  Thanks
  Jag

  As this is a SQL 2008 section of the forum, the responder should NOT have used LEAD.   Thus no nead to worry about having to mention that.

  fair enough comment....but as the responder and reviweing the query.txt files provided by the OP which contained the following:

  ;WITH
    cte_FixData AS (
        SELECT
            r.R23_SQL_ID,
            r.PROP_CODE,
            r.TENANT_NUMB,
            From_Date = CAST(r.DATE_CHANGED_SQL AS DATE),
            r.TOT_DEB_VAL,            
            CREATED_DATE_SQL = CAST(r.CREATED_DATE_SQL AS DATE)
        FROM
            @rent r
        WHERE
            r.TENANT_NUMB <> ''
            AND r.TIME_CHANGED_SQL <> ''
        )
SELECT
    fd.R23_SQL_ID,
    fd.PROP_CODE,
    fd.TENANT_NUMB,
    fd.From_Date,
    To_Date = ISNULL(DATEADD(dd, -1, LEAD(fd.From_Date) OVER (PARTITION BY fd.PROP_CODE ORDER BY fd.PROP_CODE, fd.R23_SQL_ID)), CURRENT_TIMESTAMP),
    fd.TOT_DEB_VAL,
    fd.CREATED_DATE_SQL
FROM
    cte_FixData fd

  I completely forgot it was posted in 2008 forum.....:Whistling::Whistling::Whistling:

  So fix it.... 😀😀😀

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 4, 2017 at 12:28 pm

  #1936626

  sgmunson - Tuesday, April 4, 2017 11:35 AM

  So fix it.... 😀😀😀

  :w00t:

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day