Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163

    Unanswered: Return last valid record when query is outside the range

    Tough to come up with a meaningful title!
    Using MSSQL 2008 R2

    Given the following table

    Code:
    {ID, PropClass, OffSet, Amount}
    {1,    1,           1,     .30}
    {2,    1,           2,     .45}
    {3,    1,           3,     .50}
    {4,    2,           1,     .26}
    {5,    2,           2,     .15}
    If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?

    For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3

    This is a query that I am trying to work on:

    Code:
    SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
      FROM UPValue v
      INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
      INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
     WHERE v.Year = 2012 and LeaseType = 2 
     group by v.District, v.PropClass, YearAquired
     order by v.District, v.PropClass
    Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.

    Any ideas are appreciated.
    Last edited by LinksUp; 12-19-12 at 16:37.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with
        CTE_V as
        (
             SELECT 
                 v.District, 
                 v.PropClass, 
                 v.YearAquired, 
                 v.Year
                 SUM(cost * cnt) as CostCnt, 
                 SUM(v.Tax), 
                 COUNT(*)
             FROM UPValue v
             INNER JOIN UPMaster m 
                 on m.Year = v.year and m.Account = v.account
             WHERE 
                 v.Year = 2012 and LeaseType = 2 
             group by 
                 v.District, 
                 v.PropClass, 
                 v.YearAquired,
                 v.Year
        ),
    
        CTE_T as
        (
             select
                 t.PropClass,
                 t.Offset,
                 t.Amount,
                 ROW_NUMBER() OVER(PARTITION BY t.PropClass 
                                   ORDER BY t.Offset DESC) as RowNum
             from UPTable T 
             INNER JOIN CTE_V v
                 on t.PropClass = v.PropClass and 
                     t.Offset <= v.Year - v.YearAquired
        )
    
    select
        v.*,
        v.CostCnt * t.Amount
    from CTE_V v
    inner join CTE_T t
        on t.PropClass = v.PropClass and
           t.RowNum = 1
    order by 
        v.District, 
        v.PropClass
    Hope this helps.

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I see what you are trying to do. I have not been able to get the the code that you posted to run yet, but it has given me a direction and some ideas.

    Thanks!

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with
        CTE_V as
        (
             SELECT 
                 v.District, 
                 v.PropClass, 
                 v.YearAquired, 
                 v.Year - v.YearAquired as Offset
                 SUM(cost * cnt) as CostCnt, 
                 SUM(v.Tax), 
                 COUNT(*)
             FROM UPValue v
             INNER JOIN UPMaster m 
                 on m.Year = v.year and m.Account = v.account
             WHERE 
                 v.Year = 2012 and LeaseType = 2 
             group by 
                 v.District, 
                 v.PropClass, 
                 v.YearAquired,
                 v.Year - v.YearAquired
        ),
    
        CTE_T as
        (
             select
                 v.PropClass,
                 v.Offset,
                 t.Amount,
                 ROW_NUMBER() OVER(PARTITION BY v.PropClass, v.Offset 
                                   ORDER BY t.Offset DESC) as RowNum
             from CTE_V v
             INNER JOIN UPTable T 
                 on t.PropClass = v.PropClass and 
                    t.Offset <= v.Offset
        )
    
    select
        v.*,
        v.CostCnt * t.Amount
    from CTE_V v
    inner join CTE_T t
        on t.PropClass = v.PropClass and
           t.Offset = v.Offset and
           t.RowNum = 1
    order by 
        v.District, 
        v.PropClass
    Hope this helps.

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Brilliant!

    With just a couple of minor tweaks, it worked exactly as I intended.

    Thank you for taking the time and for your efforts!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •