Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Display dates when greater than today + 1 business day

Posted on 2012-04-06
4
Medium Priority
?
381 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.

971 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