Selecting specific installment row 1

[makeitwork09]

I am using MS SQL Server 2005

It is possible for each loan to have several installment rows, some of which are future installments. We want to extract the most current installment row. The current piece of code in a view uses the following code, which gets us to where we want to be, but the users would like us to also include the last installment row even if that last row's effective THRU date has passed.

What we currently have is code that reads as:

The effective FROM date is = to the minimum effective FROM date where the effective THRU date is > the prior month end date

AND

The effective THRU date >  the prior month end date

Here is a data sample to help make this make sense (based on the prior month end date being 05/31/2011)
loan         eff_from_dte eff_thru_dte
8886357  2006-01-20  2011-01-15   <<<--users want to pick up this row
8886357  2004-07-20  2005-12-20
8886357  1999-07-20  2004-06-20
8886357  1994-07-20  1999-06-20
8880300    2011-01-04  2011-03-04
8880300    2011-04-04  2011-06-04   <<<--users want to pick up this row
8880300    2011-07-04  2012-11-04
8880305    2011-04-10  2012-03-10   <<<--users want to pick up this row
8880305    2012-04-10  2018-03-10

Notice that for 8886357 the last installment ended on 1/15/2011. The user wants to see this row, but the code mentioned above does not include that row. How can I modify the view to pick up that installment row?

Thanks

 

[PWise]

i would add a union

select xxx
from tablename
where your old code
union
select xxxx
from tablename
having max(eff_thru_dte) <=the prior month end date

not tested

 

[Qik3Coder]

Declare @_tbl as Table(
loan varchar(25),
eff_from_dte datetime,
eff_thru_dte datetime)
INSERT INTO @_tbl
SELECT '8886357','2006-01-20','2011-01-15'UNION ALL --users want to pick up this row
SELECT '8886357','2004-07-20','2005-12-20'UNION ALL
SELECT '8886357','1999-07-20','2004-06-20'UNION ALL
SELECT '8886357','1994-07-20','1999-06-20'UNION ALL
SELECT '8880300','2011-01-04','2011-03-04'UNION ALL
SELECT '8880300','2011-04-04','2011-06-04'UNION ALL--users want to pick up this row
SELECT '8880300','2011-07-04','2012-11-04'UNION ALL
SELECT '8880305','2011-04-10','2012-03-10'UNION ALL--users want to pick up this row
SELECT '8880305','2012-04-10','2018-03-10'

Declare @today datetime
select @today = GetDate()

SELECT * FROM (
Select ROW_NUMBER() OVER(PARTITION BY Loan 
	ORDER BY CASE WHEN @today between eff_from_dte and eff_thru_dte THEN 1 ELSE 2 END, 
	eff_from_dte desc) RowNum, * 
from @_tbl) as a
Where a.rowNum = 1
ORDER BY Loan

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[makeitwork09]

Thanks for the quick responses.

HTH, I had done something similar, but using dense_rank(). I get the same issue as what the code you provided is doing. Take a look at the results for loan 8880300, for example. Row number 1 is not the row I want to extract. The row with the effective from date of 2011-04-04 and effective thru dat of 2011-06-04 is what should be displaying.

Thanks again

prb

 

[makeitwork09]

HTH, I take that back. You used getdate(). I'm using the previous month date. Let me check mine again.

 

[makeitwork09]

Ok I'm good to go. I had to add a where clause to the select that uses the row_number function.

where effective from date <= prior month end date

Thanks so much for the help. I was close, but it was the between dates that I needed to make it work.

prb

 

[makeitwork09]

I have a new issue related to this after using the code suggested here. New loans that were added after the prior month end will start with a future effective from date. Using the very useful code here, the following types of records do not appear. These were appearing with my original code, but of course, as mentioned above old records were not appearing. Any ideas?

LOAN        EFF_FROM_DTE    EFF_THRU_DTE
8880906    1/1/2020        1/1/2020
8880311    7/10/2011        6/10/2021
8880312    7/10/2011        6/10/2023


Thanks

 

[Qik3Coder]

Let me see exactly what you have now. You should have been able to have them show with the code I originally gave you.
It prioritized the current ones first, then ordered the rest in descending order.

A lack of experience doesn't prevent you from doing a good job.

 

[makeitwork09]

Here is the code.

The reason your code would not fully work is because some older loans have future installs that have an effective from date thats after the current accounting period (06/30/2011). An example is

Loan eff_from_dte eff_thru_dte
8880297 2011-07-10 2014-01-10
8880297 2011-06-10 2011-06-10

With the code you provided the first line would appear, but it is the second line that I need.

Your code would address, my last post, but what I just showed above would occur.

FYI when I really do this the dates will use a field from a different table that has the current and prior month end dates.

select i.loan,i.eff_from_dte,i.eff_thru_dte,i.interest_type
from (select s.loan,s.eff_from_dte,s.eff_thru_dte,s.interest_type,
        row_number() over (partition by s.loan order by
                           case when '2011-05-31' between s.eff_from_dte
and s.eff_thru_dte then 1 else 2 end, s.eff_from_dte desc) as [rownum]
        from instschd s
        where s.eff_from_dte <= '2011-06-30') as i
where i.rownum = 1
order by i.loan,i.eff_from_dte,i.eff_thru_dte

 

[Qik3Coder]

Your requirements aren't really clear, so I'm going to re-state to ensure I understand.

Priority:
Row with period that contains the date passed in
Row with the next FORWARD period
Row with the newest PAST period

Is this correct? Because you can just change the case in the Row_number function to elevate the priority on rows forward.

A lack of experience doesn't prevent you from doing a good job.

 

[makeitwork09]

Oh and the

where s.eff_from_dte <= '2011-06-30'

Is part of the problem. I'm using to address one issue, but it causes another.

 

[makeitwork09]

Which one of my date examples is thought of as
Row with the newest PAST period?

Yes, I know it's confusing. It's tough to explain in writing. Blah! Sorry about that. You're code is very helpful.

 

[Qik3Coder]

The very first row is:
8886357 2006-01-20 2011-01-15 <<<--users want to pick up this row


A lack of experience doesn't prevent you from doing a good job.

 

[makeitwork09]

Ok, I wanted to be sure. Then, yes you understood the craziness correctly.

 

[Qik3Coder]

Try this for the Row_Number Function:

row_number() over (partition by s.loan 	order by 
		case when '2011-05-31' between s.eff_from_dte and s.eff_thru_dte then 1 
			 when '2011-05-31' < s.eff_from_dte then 2
		else 3 end, s.eff_from_dte desc) as [rownum]

A lack of experience doesn't prevent you from doing a good job.

 

[makeitwork09]

Thanks VERY much Qik3Coder.

One thing I had to do was get a better understanding of what specifically the case statement was doing and what the row_number function does. Once I did that and looked at the code you provided, I was able to get the code to hit the various scenarios. At least I hope I hit them all.

select i.loan,i.eff_from_dte,i.eff_thru_dte,i.interest_type
from (select s.loan,s.eff_from_dte,s.eff_thru_dte,s.interest_type,
            --the case statement sorts the result set
            row_number() over (partition by s.loan order by
               -- the first case is the majority of records; where the prior month is in the
               -- from to thru range takes top priority
          case when '2011-05-31' between s.eff_from_dte and s.eff_thru_dte then 1
              --this line helps with new loans where the first installment month is the month after the prior month
             when dateadd(dd,day(eff_from_dte),'2011-05-31') between s.eff_from_dte and s.eff_thru_dte then 2
            --this line helps with new loans where the first installment month is the month after the current month
              when dateadd(dd,day(eff_from_dte),'2011-06-30') between s.eff_from_dte and s.eff_thru_dte then 3
              when '2011-05-31' > s.eff_thru_dte then 4
     else 5 end, s.eff_from_dte desc) as [rownum]
from instschd s) as i
where i.rownum = 1