asked on

sql query conditions overwriting each other

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

ASKER CERTIFIED SOLUTION

k_rasuri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fahVB

ASKER

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 nc02monthend..loan 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,
l.ratechange,l.draccr,l.priorlinmtd,l.ratetype,m.solicit,m.bankruptcy
order by 2

fahVB

ASKER

Thanks