sql query conditions overwriting each other

Posted on 2009-04-29
Medium Priority
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
  • 2

Accepted Solution

k_rasuri earned 1500 total points
ID: 24265207
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

Author Comment

ID: 24269590
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,
order by 2

Author Closing Comment

ID: 31575943

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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