Improve company productivity with a Business Account.Sign Up

x
?
Solved

Display dates when greater than today + 1 business day

Posted on 2012-04-06
4
Medium Priority
?
384 Views
Last Modified: 2012-04-06
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

0
Comment
Question by:dplowman
  • 3
4 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 2000 total points
ID: 37817322
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
 

Author Comment

by:dplowman
ID: 37817453
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
 

Author Comment

by:dplowman
ID: 37817484
never mind, i can just use >! Getting late in the day I guess:)
0
 

Author Closing Comment

by:dplowman
ID: 37817487
Thanks, works great!
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.

Join & Write a Comment

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

589 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