Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Display dates when greater than today + 1 business day

Posted on 2012-04-06
4
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

704 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