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

    Unanswered: Difficult Count Problem

    I have been asked to provide a report that gives some counts of different attributes for parcels of land. The attributes are grouped by the contents of the field tc233. This field tells me what line number of the report to put the count for a given district. Counting buildings is easy. The follow query does the job: (note the range of 6 to 9, this restricts it to buildings)

    Code:
    select m.district, p.tc233, COUNT(*) 
     from TRValue v 
    inner join TRProp P on 
               p.code = v.Code and 
               p.tc233 between 6 and 9 and 
               p.PropType = 'A' 
    inner join TRMaster m on 
               m.year = v.year and 
               m.parcel = v.parcel 
               m.deleted = 0
    where v.Year = 2012 and m.Deleted = 0 and m.Status = 'OK' 
    group by m.District, p.TC233 
    order by m.District, p.TC233
    Counting the land is a bit more difficult. The requirement is that if the parcel has multiple uses, ie, residential and secondary, then the count should be incremented by the part of the land that has the largest value associated with it.

    Each query I have tried to come up with is flawed. The closest I have been able to get to was:

    Code:
    Select m.District, MAX(p.tc233), MAX(v.CurrentMarket), COUNT(*)
      from trvalue v 
      inner join TRProp P on 
                   p.code = v.Code and 
                   p.tc233 between 1 and 5 and 
                   p.PropType = 'A'
      inner join TRMaster m on 
                   m.Year = v.Year and 
                   m.Parcel = v.parcel and 
                   m.Deleted = 0
     where v.Year = 2012
     group by m.district, p.tc233
     having MAX(v.CurrentMarket) > 0
     order by m.district, p.tc233
    The count returns the total number of land segments on a parcel rather than just the highest. I don't care what the highest value is, just what line number (tc233) that the land code points to.

    I can see that a sub-query will be necessary, I just don't where to put it; in the FROM, the INNER JOIN or the even the WHERE!

    Any ideas on how this can be accomplished?

    If more clarification is needed, please let me know.

    Thanks.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Here is a solution if anybody else is interested:

    Code:
    ;With SortedMkt as (
       select v.year, v.parcel, v.code, v.currentmarket,
              ROW_NUMBER() OVER 
                (PARTITION BY v.Year, v.Parcel 
                     ORDER BY v.CurrentMarket desc
                ) as rn
      from TRValue v
      inner join TRProp p on 
          p.Code = v.Code 
      where SUBSTRING(v.Code, 1,1) = 'L' and v.CurrentMarket > 0
    )
    select   m.District, p.tc233, COUNT(*)
    from
      TRMaster m
        inner join SortedMkt sm  on
            m.Year   = sm.Year and 
            m.Parcel = sm.Parcel and
            sm.rn = 1
        inner join TRProp p on
            p.code = sm.Code and
            p.proptype = 'A'  and
            p.tc233 between 1 and 5
    where m.Year = 2012 and m.Deleted = 0 and M.Status = 'OK'
    group by m.District, p.tc233
    order by m.District, p.tc233
    This solution demonstrates how to use a CTE to refine and sort the record set so that the largest child record for each parcel is ranked as the first record and then how to query that result set for the needed data.
    Last edited by LinksUp; 01-18-13 at 12:44.

Posting Permissions

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