Display dates when greater than today + 1 business day

All,

I am trying to create a report to show a list of customers that have an ach_effective_date > getdate() + next business day. For example if I ran the report today, I am currently gettting loans with ach effective dates of 4/9 and greater. Where as I only want to show 4/10 and greater. No loans will have an effecrive date on saturday or sunday.
Select sm.BO_CODE as 'Customer Number', 
first_name as 'Customer First Name',
last_name as 'Customer Last Name',
address_ln as 'Customer Address',
CITY as 'Customer City',
STATE_ID as 'Customer State',
POSTAL_ID as 'Customer Zip',
sm.LOAN_CODE as 'Loan Number',
LOAN_DATE as 'Loan Date',
LOAN_AMT as 'Loan Amount',
sm.DUE_DATE as 'Loan Due Date',
asa.ACH_EFFECTIVE_DATE as 'ACH Effective Date',
TOTAL_DUE as 'Amount Due'
into #cust
from USOnlineReporting.dbo.ST_LO_MASTER sm
inner join USOnlineReporting.dbo.ACH_STAGING_ACTIVE asa on asa.LOAN_CODE = sm.LOAN_CODE
inner join USOnlineReporting.dbo.BO_MASTER bm on bm.BO_CODE = sm.BO_CODE
inner join usonlinereporting.dbo.bo_address ba on ba.bo_code = bm.bo_code
inner join USOnlineReporting.dbo.BO_IN_APP_QUEUE bia on bia.APP_NO = sm.LOAN_CODE
inner join USOnlineReporting.dbo.ST_COLLECTION_MASTER scm on scm.LOAN_CODE = sm.LOAN_CODE
where CSLD_ELIGIBILITY_FLAG = 'y'
and LOAN_STATUS_ID = 'opn'
and CHECK_STATUS_ID = 'hld'
and TRAN_ID = 'dp'
and asa.ACH_EFFECTIVE_DATE > GETDATE()
and asa.IS_HOLD = 'n'
and CONTACT_STATUS_ID <> '6'
and 
(
	(STATE_ID = 'al' and sm.ROLL_OVER_COUNT = 1)
	or (STATE_ID = 'ca')
	or (STATE_ID = 'de' and sm.ROLL_OVER_COUNT = 4)
	or (STATE_ID = 'id' and sm.ROLL_OVER_COUNT = 7)
	or (STATE_ID = 'mo' and sm.ROLL_OVER_COUNT = 2)
	or (STATE_ID = 'oh' and sm.ROLL_OVER_COUNT = 7)
	or (STATE_ID = 'sd' and sm.ROLL_OVER_COUNT = 4)
	or (STATE_ID = 'ut' and sm.ROLL_OVER_COUNT = 3)
)
and sm.bo_code not in
	(
	Select BO_CODE
	from USOnlineReporting.dbo.BO_APP_LOAN_CUST_FLAGS balcf
	inner join USOnlineReporting.dbo.LT_IN_APPLICATION_LOAN_FLAGS lialf on lialf.FLAG_ID = balcf.flag_id
	where LOAN_FLAG_DESCRIPTION in ('bad cp','bad hp','bad wp','dnc-home phone','dnc-work phone','dnc-courtesy call')
	)
order by asa.ACH_EFFECTIVE_DATE asc

Select BO_code, Phone_ID, Phone_Num
into #Home
from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number]
Where Phone_ID ='H'

Select BO_code, Phone_ID, Phone_Num
into #Cell
from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number]
Where Phone_ID ='C'

Select BO_code, Phone_ID, Phone_Num
into #Work
from usonlinereporting.dbo.bo_phone inner join #cust on bo_phone.bo_code = #cust.[Customer Number]
Where Phone_ID ='W'

Select [Customer Number], 
[Customer First Name], 
[Customer Last Name], 
[Customer Address], 
[Customer City], 
[Customer State], 
[Customer Zip],
'Home Phone' as 'Customer Phone Type1', 
#home.Phone_Num as 'Customer Phone Number1', 
'Cell Phone' as 'Customer Phone Type2',
#Cell.Phone_num as 'Customer Phone Number2', 
'Work Phone' as 'Customer Phone Type3', 
#work.phone_Num as 'Customer Phone Number3', 
[Loan Number],
[Loan Date],
[Loan Amount] ,
[Loan Due Date],
[ACH Effective Date],
[Amount Due]
from #cust left join #Home on #cust.[Customer Number] = #home.BO_Code 
left join #cell on #cust.[Customer Number] = #cell.BO_Code
left join #Work on #cust.[Customer Number] = #work.BO_Code

Open in new window

dplowmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
These types of queries are much easier if you use a calendar table.  See this article about how to create a calendar table.  Once you have a table, you can do something like this:

DECLARE @NextBusinessDay datetime = (SELECT MIN(CalendarDate) FROM Calendar WHERE CalendarDate > getdate() AND BusinessDay = 1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dplowmanAuthor Commented:
Thanks, that will get me 4/9/12, i am actually wanting the second business day, 4/10/12. How would I go about updating the query above to get that?
0
dplowmanAuthor Commented:
never mind, i can just use >! Getting late in the day I guess:)
0
dplowmanAuthor Commented:
Thanks, works great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.