September 15, 2015 at 8:58 am
I posted a similar problem yesterday where I had to display only the result of -21 day from today for example only the results of 08/24/15, but unfortunately I didn't get the answer, I am using the following query but my result set is empty, any advises please
CREATE VIEW V_Decline_Letters_TEST
AS
SELECT
AP.first_name AS [First Name]
,AP.last_name AS [Last Name]
,CA.house_num + ' ' + CA.street_name + ' ' + CA.apt_num AS [Mailing Address]
,CA.city AS [City]
,CA.state AS [State]
,CA.zip_code AS [Zip Code]
,P.product_name AS [Product Name]
,DL.decline_desc AS [Decline Reason]
,CAP.decision_date AS [Decision Date]
,CBI.name AS [Credit Bureau]
FROM applicant AS AP
JOIN credit_application AS CAP
ON AP.application_id = CAP.application_id
JOIN cust_address AS CA
ON AP.cust_id = CA.cust_id
JOIN product AS P
On P.product_id = CAP.product_id
JOIN dealer_vendor_product AS DP
ON P.product_id = DP.product_id
JOIN app_decline_code AS DC
ON AP.application_id = DC.application_id
JOIN app_decline_list AS DL
ON DC.decline_id = DL.decline_id
JOIN cb_rpt_ownership AS CR
ON AP.cust_id = CR.cust_id
JOIN cb_report AS CBR
ON CR.cb_rpt_id = CBR.cb_rpt_id
JOIN credit_bureau AS CBI
ON CBR.cb_id = CBI.cb_id
Where decision_date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 25, 0)
AND decision_date < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
September 15, 2015 at 9:11 am
Earlier in the select statement I also tried to convert the date in varchar, but I know then I would not be able to use the date functions.
,CONVERT(VARCHAR(10), CAP.decision_date, 101) AS [Decision Date]
September 15, 2015 at 9:27 am
You just need to adjust the numbers in the calc below until it returns the date you want. I put my best guess in, but adjust as needed.
--To stand-alone see the dates being used:
--select DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 22, 0),DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)
Where decision_date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 22, 0)
AND decision_date < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply