July 16, 2013 at 11:53 am
I am trying to find the top 750 balances on each day from a history table of accounts on file in a consumer credit portfolio. Clearly this is easy if I did each day by itself, but I don't know how to find it for each day in a single query. I will need to further confine my results by the days past due of each record each day.
Data Table:
ASOFDATE (the effective date of the record in the history table)
ACCOUNT
BALANCE
DAYSPD
There are more data elements in the table but these are the only ones I need.
Thoughts?
July 16, 2013 at 12:04 pm
Not much to go on, but probably something like this:
SELECT ...
FROM (
SELECT
...,
ROW_NUMBER() OVER (PARTITION BY <date_column> ORDER BY <your_order_criteria>) AS row_num
FROM dbo.tablename
WHERE ASOFDATE <= ... --Edit: added this line
) AS derived
WHERE
row_num <= 750 --or whatever number per partition
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 16, 2013 at 3:35 pm
Here is what I ended up with after thinking this through... it makes sense to me now, but I am not certain I wrote it right for my application.
select CAST(right(asofdate,6) as date), COUNT (account), SUM (ttl_bal)
from (
select asofdate, ACCOUNT, DAYSPD, ttl_bal,
ROW_NUMBER() over (partition by cast(right(asofdate,6) as date) order by ttl_bal desc) as ROW_NBR
from dbo.ACCT_MASTER_HISTORY
where dayspd between 1 and 14 and PMT_MD = 0 and CLASS_CD not bewteen '90' and '99'
) as derived
where ROW_NBR <= 750
group by CAST(right(asofdate,6) as date)
order by CAST(right(asofdate,6) as date)
I am double checking the output... but it looks good to me so far.
Thank you so much!
July 16, 2013 at 6:20 pm
How should ties in the balance amounts be handled?
Consider using RANK() or DENSE_RANK() instead of ROW_NUMBER().
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply