Link to home
Start Free TrialLog in
Avatar of tanj1035
tanj1035

asked on

SQL, add where clause

If I want to add "where  tblreportingtransactions.TransactionDate <'2017-03-01" in the query below, where it should be? Thank you.


select loanid, openprincipalbalance
from (
    select openprincipalbalance, row_number() over(partition by loanid order by transactiontype_flag, transactiondate DESC) as row_num
    from tblReportingTransactions
    cross apply (
        select case when [transactiontypid] in (1,2,3,11,23) then 0 else 1 end as transactiontype_flag
    ) as ca1
) as rpt
where row_num = 1

Open in new window

Avatar of _agx_
_agx_
Flag of United States of America image

Try adding it before the CROSS APPLY. That will restrict the results to only records earlier than '2017-03-01'

select loanid, openprincipalbalance
from (
    select openprincipalbalance, row_number() over(partition by loanid order by transactiontype_flag, transactiondate DESC) as row_num
    from tblReportingTransactions
    where  transactionDate < convert(date, '20170301', 112)
    cross apply (
        select case when [transactiontypid] in (1,2,3,11,23) then 0 else 1 end as transactiontype_flag
    ) as ca1
) as rpt
where row_num = 1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tanj1035
tanj1035

ASKER

Thank you, Scott
Thank you for your reply, Agx. I tried it out, unfortunately, it did not work. Thanks.
@tanj1035 - No worries, my syntax was wrong :)  

( Beaten by Scott Pletcher and his darned CROSS APPLY .. again! ;-)