August 30, 2017 at 3:14 am
Hi Friends,
desc tab1
locn varchar(10)
cust_no varchar(10)
address varchar(10)
desc tab2
cust_no varchar(10)
bill_date datetime
my expecting output is
custno locn _no
The condition is
i want to to take customer details which is not billed from current date into last 3years at the same time the customer would not repeated from last two years and one years
August 30, 2017 at 3:45 am
Sample data and expected outcome?
SELECT * FROM TAB1 t1 INNER JOIN TAB2 t2 ON t1.cust_no = t2.cust_no WHERE t2.bill_date < DATEADD(year,-3,getdate())
August 31, 2017 at 4:39 am
Hi Friend,
My Expecting Reply is
cust no loc 3yearsNotbilled 2years not billed 1year no billed
August 31, 2017 at 4:42 am
Data and based on that data your output?
Read the first link in my signature
August 31, 2017 at 2:41 pm
You need to provide data, script and output expected.
Looks like you need case statement.
SELECT t1.cust_no
,Case when t2.bill_date < DATEADD(year,-2,getdate()) Then 1 else 0 end yearsNotbilled3 --bill date older than 2 years
,Case when t2.bill_date < DATEADD(year,-1,getdate()) and t2.bill_date > DATEADD(year,-2,getdate()) Then 1 else 0 end yearsNotbilled2 -- billdate between 1-2 year
,Case when t2.bill_date > DATEADD(year,-1,getdate()) Then 1 else 0 end yearsNotbilled1 --billdate greater than past one year
FROM TAB1 t1 INNER JOIN TAB2 t2 ON t1.cust_no = t2.cust_no WHERE t2.bill_date < DATEADD(year,-3,getdate()) --take only records where billdate is past 3 years
August 31, 2017 at 3:06 pm
Avi1 - Thursday, August 31, 2017 2:41 PMYou need to provide data, script and output expected.
Looks like you need case statement.
SELECT t1.cust_no
,Case when t2.bill_date < DATEADD(year,-2,getdate()) Then 1 else 0 end yearsNotbilled3 --bill date older than 2 years
,Case when t2.bill_date < DATEADD(year,-1,getdate()) and t2.bill_date > DATEADD(year,-2,getdate()) Then 1 else 0 end yearsNotbilled2 -- billdate between 1-2 year
,Case when t2.bill_date > DATEADD(year,-1,getdate()) Then 1 else 0 end yearsNotbilled1 --billdate greater than past one year
FROM TAB1 t1 INNER JOIN TAB2 t2 ON t1.cust_no = t2.cust_no WHERE t2.bill_date < DATEADD(year,-3,getdate()) --take only records where billdate is past 3 years
That's overly complicated. There are two solutions: one with a CTE/MAX the other with a CROSS APPLY/TOP(1). Which performs better will depend on the density of T2 with respect to T1. I'll give the CROSS APPLY approach.
SELECT T1.cust_no, DATEDIFF(YEAR, T2.bill_date, GETDATE()) -- may need to be tweaked if you want full years instead of partial
FROM T1
CROSS APPLY (
SELECT TOP(1) bill_date
FROM T2
WHERE T1.cust_no = T2.cust_no
AND t2.bill_date >= DATEADD(YEAR, -3, GETDATE())
ORDER BY T2.bill_date DESC
) T2
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply