Post reply

Hekp required on Where clauses

  • September 21, 2015 at 10:31 am

    #307816

    Hi Everyone,

    I am joining two tables (Loan L & History H) I want to list all the loans after 2010, where (H.EffectiveDate), were more than 30 days late from the (L.FirstDueDate). other criteria (H.TransactionCode='220' and H.TransactionAmt > '0') I have the following code so far, I am not sure about the where clause, also I only want loans where (L.FirstDueDate) is > than 2010. Any tips or advises would be highly appreciated

    SELECT DISTINCT

    L.LoanID

    ,CONVERT(VARCHAR(10), H.EffectiveDate, 101) AS [Effective Date]

    ,CONVERT(VARCHAR(10), L.FirstDueDate, 101) AS [1st PMT Due Date]

    ,DATEDIFF(day,(CONVERT(VARCHAR(10), L.FirstDueDate, 101)),(CONVERT(VARCHAR(10), H.EffectiveDate, 101))) AS [Days late]

    from loan AS L

    JOIN history AS H on L.LoanID = H.LoanID

    where DATEDIFF(day, (select min (EffectiveDate) from history

    where H.TransactionCode='220' and H.TransactionAmt > '0' and LoanID = L.loanid) , L.FirstDueDate) > 30

    AND L.FirstDueDate >= '2013'

    Order by [Days late]

  • September 21, 2015 at 12:03 pm

    #1828182

    Please post DDL and sample data to be able to test different possibilities. Be sure to post the expected results based on that sample data.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply