May 27, 2015 at 3:36 pm
Hi,
My data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015. Ideas & suggestions?
Thanks,
Alan
May 27, 2015 at 6:53 pm
The following should give you what you're looking for.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
TN CHAR(4),
CallDate DATE
)
INSERT #temp (TN, CallDate) VALUES
('1111','2014-01-01'),
('1111','2014-02-01'),
('1111','2014-03-01'),
('1111','2014-06-01'),
('1111','2014-07-01'),
('1111','2014-10-01'),
('1111','2014-11-01'),
('1111','2014-12-01'),
('1111','2015-01-01'),
('1111','2015-02-01'),
('1111','2015-03-01'),
('1111','2015-04-01'),
('1111','2015-08-01'),
('1111','2015-09-01'),
('1111','2015-10-01'),
('1111','2015-11-01'),
('1111','2015-12-01');
SELECT
t1.TN,
t1.CallDate,
cc.CallCount
FROM
#temp t1
CROSS APPLY (
SELECT COUNT(*) AS CallCount
FROM #temp t2
WHERE t1.TN = t2.TN
AND t2.CallDate >= DATEADD(dd, -180, t1.CallDate)
AND t2.CallDate <= t1.CallDate
) cc
Results...
TN CallDate CallCount
---- ---------- -----------
1111 2014-01-01 1
1111 2014-02-01 2
1111 2014-03-01 3
1111 2014-06-01 4
1111 2014-07-01 4
1111 2014-10-01 3
1111 2014-11-01 4
1111 2014-12-01 4
1111 2015-01-01 4
1111 2015-02-01 5
1111 2015-03-01 6
1111 2015-04-01 6
1111 2015-08-01 3
1111 2015-09-01 3
1111 2015-10-01 3
1111 2015-11-01 4
May 28, 2015 at 2:10 pm
Thanks Jason, this should be exactly what I need!
May 28, 2015 at 2:22 pm
Are you aware of the difference between 6 months and 180 days? It's a small change, but it can make a difference if you have the requirement wrong.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply