Solved

Display dates when greater than today + 1 business day

Posted on 2012-04-06
4
373 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 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now