We help IT Professionals succeed at work.

SQL, add where clause

tanj1035
tanj1035 asked
on
126 Views
Last Modified: 2017-03-10
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you, Scott

Author

Commented:
Thank you for your reply, Agx. I tried it out, unfortunately, it did not work. Thanks.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
@tanj1035 - No worries, my syntax was wrong :)  

( Beaten by Scott Pletcher and his darned CROSS APPLY .. again! ;-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.