sql query conditions overwriting each other

Posted on 2009-04-29
Last Modified: 2012-06-21
in my query, I have these following two conditions which is messing it up, i am running this against my month-end database

the field "closed" in my loan table is used in both of these conditions..

first, i am using it to include loans which were closed between the 1st and last day of the month etc. 3/1(@StartDate) to 3/31(@EndDate)

sum(case when source in ('lpo','tlpo') and (L.closed between @StartDate and @EndDate) then 1 else 0 end) as ANUMPO,

Second, in this where clause i am excluding "closed" loans..

where L.status not in ('closed','writeoff','cancelled')

Here's what i need to do
is there a way i can modify where stmt to exclude "closed" loans prior to this running month, etc exclude closed loans before Feb28th...

due to this second condition "closed" loans are not being included and due to this my first condition is not giving me accurate results, loans which were closed in March monthend are not being included, coz of second stmt

Any ideas, or suggestions...

First condition"

sum(case when source in ('lpo','tlpo') and (L.closed between @StartDate and @EndDate) then 1 else 0 end) as ANUMPO,



Second Condition:

where L.status not in ('closed','writeoff','cancelled')

Open in new window

Question by:fahVB
    LVL 8

    Accepted Solution

    You cannot do this with one might need two UPDATE statements or somekind of SUBQUERY. can you paste some sample data from your source table and also the results you are expecting

    Author Comment

    here is my query, now it is not picking up closed loans beacuse of following stmt

    where l.status not in ('closed','writeoff','cancelled')

    Declare @StartDate datetime
    Declare @EndDate datetime
    Declare @CurrentDate datetime
    Set @CurrentDate = CONVERT(datetime, CONVERT(varchar, getdate(), 110))
    Set @startdate = dateadd(m,-1,dateadd(dd,1 - datepart(dd, @CurrentDate), @CurrentDate) )
    Set @enddate = dateadd(d,-1,dateadd(dd,1 - datepart(dd, @CurrentDate), @CurrentDate))
    Select m.acct as AACTNUM,
    sum(case when h.effective between @StartDate and @EndDate then abs(h.drint) else 0 end) as AINTINC,
    case when l.apr = 0 then 0 else abs((l.priorlinmtd * 1.0)/((l.apr * 1.0)/365.0 * (365.0/1200))) end as AAVGBALA,
    sum(case when source in ('lpo','tlpo') and (L.closed between @StartDate and @EndDate) then 1 else 0 end) as ANUMPO
    from memb m
    INNER JOIN l on m.rowno  = l.rowno_membloan_memb
    INNER JOIN nc02monthend..lohi h on l.rowno = h.rowno_loanlohi_loan
    where l.status not in ('closed','writeoff','cancelled')
    group by m.acct,m.branch,l.sub,l.amount,l.apr,l.latefee,l.balance,l.deldate,l.status,l.code,l.term,l.opened,
    order by 2

    Author Closing Comment


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now