TSQL - Return recent date

Having issues getting a dataset to return with one date per client in the query.

Requirements:

• Must have the recent date of transaction per client list for user
• Will need have the capability to run through EXEC

Current Query:

SELECT
    c.client_uno
    , c.client_code
    , c.client_name
    , c.open_date
into #AttyClnt
from hbm_client c
    join hbm_persnl p on c.resp_empl_uno = p.empl_uno
where p.login = @login
    and c.status_code = 'C'

select
    ba.payr_client_uno as client_uno
    , max(ba.tran_date) as tran_date
from blt_bill_amt ba
    left outer join #AttyClnt ac on ba.payr_client_uno = ac.client_uno
where ba.tran_type IN ('RA', 'CR')
group by ba.payr_client_uno

Currently, this query will produce at least 1 row per client with a date, the problem is that there are some clients that will have between 2 and 10 da开发者_Go百科tes associated with them bloating the return table to about 30,000 row instead of an idealistic 246 rows or less.

When i try doing max(tran_uno) to get the most recent transaction number, i get the same result, some have 1 value and others have multiple values.

The bigger picture has 4 other queries being performed doing other parts, i have only included the parts that pertain to the question.

Edit (2011-10-14 @ 1:45PM):

    select 
    ba.payr_client_uno as client_uno
    , max(ba.row_uno) as row_uno
into #Bills
from blt_bill_amt ba
    inner join hbm_matter m on ba.matter_uno = m.matter_uno
        inner join hbm_client c on m.client_uno = c.client_uno
            inner join hbm_persnl p on c.resp_empl_uno = p.empl_uno
where p.login = @login
    and c.status_code = 'C'
    and ba.tran_type in ('CR', 'RA')
group by ba.payr_client_uno
order by ba.payr_client_uno

--Obtain list of Transaction Date and Amount for the Transaction
select
    b.client_uno
    , ba.tran_date
    , ba.tc_total_amt
from blt_bill_amt ba
    inner join #Bills b on ba.row_uno = b.row_uno

Not quite sure what was going on but seems the Temp Tables were not acting right at all. Ideally i would have 246 rows of data, but with the previous query syntax it would produce from 400-5000 rows of data, obviously duplications on data.

---

I think you can use ranking to achieve what you want:

WITH ranked AS (
  SELECT
    client_uno = ba.payr_client_uno,
    ba.tran_date,
    be.tc_total_amt,
    rnk = ROW_NUMBER() OVER (
      PARTITION BY ba.payr_client_uno
      ORDER BY     ba.tran_uno DESC
    )
  FROM blt_bill_amt ba
    INNER JOIN hbm_matter m ON ba.matter_uno = m.matter_uno
    INNER JOIN hbm_client c ON m.client_uno = c.client_uno
    INNER JOIN hbm_persnl p ON c.resp_empl_uno = p.empl_uno
  WHERE p.login = @login
    AND c.status_code = 'C'
    AND ba.tran_type IN ('CR', 'RA')
)
SELECT
  client_uno,
  tran_date,
  tc_total_amt
FROM ranked
WHERE rnk = 1
ORDER BY client_uno

Useful reading:

• Ranking Functions (Transact-SQL)

  • ROW_NUMBER (Transact-SQL)

• WITH common_table_expression (Transact-SQL)

  • Using Common Table Expressions