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

fahVBAsked:
Who is Participating?
 
k_rasuriConnect With a Mentor Commented:
You cannot do this with one query..you 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
0
 
fahVBAuthor Commented:
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
0
 
fahVBAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.