September 7, 2014 at 2:23 pm
Hi all,
I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occured within 15 days. If so, I need to flag them.
I'm not quite sure where to start. Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.
Any help would be appreciated...thanks!
September 7, 2014 at 2:30 pm
Not enough to really provide you with code as nothing to work with. I would use a CTE and the row_number() over (partition by ... order by ... desc) to be able to identify the latest 3 dates.
If you want more you'll need to post DDL (CREATE TABLE statement) for the table(s) involved, some sample data for the table(s) in the form os INSERT INTO statements, and the expected results based on the sample data. For assistance in all this, please read the first article I have linked below in my signature block. It will walk you through what you should post and how to do it.
September 7, 2014 at 2:44 pm
Here is an example code demonstrating a method but not a solution. For a full solution, follow Lynn's advice on supplying further information.
😎
USE tempdb;
GO
;WITH SAMPLE_DATA(ACTIVITY_DATE) AS
(SELECT ACTIVITY_DATE FROM (VALUES
('2014-02-27 00:15:45.000')
,('2014-02-28 18:52:29.000')
,('2014-02-28 21:05:56.000')
,('2014-03-01 21:45:19.000')
,('2014-03-03 02:34:01.000')
,('2014-03-04 19:47:02.000')
,('2014-03-05 12:10:45.000')
,('2014-03-14 10:43:06.000')
,('2014-03-14 12:45:34.000')
,('2014-03-15 18:24:42.000')
,('2014-03-21 17:40:39.000')
,('2014-03-31 09:18:04.000')) AS X(ACTIVITY_DATE)
)
,QUERY_BASE AS
(
SELECT
ROW_NUMBER() OVER
(
ORDER BY SD.ACTIVITY_DATE DESC
) AS AD_RID
,SD.ACTIVITY_DATE
FROM SAMPLE_DATA SD
)
SELECT
QB.AD_RID
,QB.ACTIVITY_DATE
,DATEDIFF(HOUR,QB.ACTIVITY_DATE,MAX(QB.ACTIVITY_DATE) OVER (PARTITION BY (SELECT NULL))) / 24 AS PeriodInDays
FROM QUERY_BASE QB
WHERE QB.AD_RID <= 3;
Results
AD_RID ACTIVITY_DATE PeriodInDays
------- ------------------------ ------------
1 2014-03-31 09:18:04.000 0
2 2014-03-21 17:40:39.000 9
3 2014-03-15 18:24:42.000 15
September 7, 2014 at 2:58 pm
Thanks for responding Lynn! Below is a small test case.
CREATE TABLE [dbo].[ActivityTable](
[CompanyId] [int] NULL,
[CompanyName] [varchar](20) NULL,
[ActivityDate] [date] NULL
) ON [PRIMARY]
GO
insert into ActivityTable values(1,'ABC','2014-08-01')
insert into ActivityTable values(1,'ABC','2014-08-03')
insert into ActivityTable values(1,'ABC','2014-08-10')
insert into ActivityTable values(1,'ABC','2014-07-20')
insert into ActivityTable values(2,'AJAX','2014-08-01')
insert into ActivityTable values(2,'AJAX','2014-08-10')
insert into ActivityTable values(2,'AJAX','2014-05-10')
insert into ActivityTable values(2,'AJAX','2014-05-20')
insert into ActivityTable values(3,'TURNER','2014-07-01')
insert into ActivityTable values(3,'TURNER','2014-07-31')
insert into ActivityTable values(3,'TURNER','2014-06-15')
------------------------------------------------------------------------------
I'm looking for the last three dates for each company and if those three dates are within 15 days.
In the above example, the ABC company would be returned.
Thanks!
September 7, 2014 at 3:11 pm
caoneill (9/7/2014)
Thanks for responding Lynn! Below is a small test case.CREATE TABLE [dbo].[ActivityTable](
[CompanyId] [int] NULL,
[CompanyName] [varchar](20) NULL,
[ActivityDate] [date] NULL
) ON [PRIMARY]
GO
insert into ActivityTable values(1,'ABC','2014-08-01')
insert into ActivityTable values(1,'ABC','2014-08-03')
insert into ActivityTable values(1,'ABC','2014-08-10')
insert into ActivityTable values(1,'ABC','2014-07-20')
insert into ActivityTable values(2,'AJAX','2014-08-01')
insert into ActivityTable values(2,'AJAX','2014-08-10')
insert into ActivityTable values(2,'AJAX','2014-05-10')
insert into ActivityTable values(2,'AJAX','2014-05-20')
insert into ActivityTable values(3,'TURNER','2014-07-01')
insert into ActivityTable values(3,'TURNER','2014-07-31')
insert into ActivityTable values(3,'TURNER','2014-06-15')
------------------------------------------------------------------------------
I'm looking for the last three dates for each company and if those three dates are within 15 days.
In the above example, the ABC company would be returned.
Thanks!
Just curious, what if you only have 2 activity dates for a company?
September 7, 2014 at 3:15 pm
Hi Lynn,
If there are only 2 then it's just ignored. Three is the magic number! 🙂
September 7, 2014 at 3:25 pm
Try this:
CREATE TABLE [dbo].[ActivityTable](
[CompanyId] [int] NULL,
[CompanyName] [varchar](20) NULL,
[ActivityDate] [date] NULL
) ON [PRIMARY]
GO
insert into ActivityTable values(1,'ABC','2014-08-01')
insert into ActivityTable values(1,'ABC','2014-08-03')
insert into ActivityTable values(1,'ABC','2014-08-10')
insert into ActivityTable values(1,'ABC','2014-07-20')
insert into ActivityTable values(2,'AJAX','2014-08-01')
insert into ActivityTable values(2,'AJAX','2014-08-10')
insert into ActivityTable values(2,'AJAX','2014-05-10')
insert into ActivityTable values(2,'AJAX','2014-05-20')
insert into ActivityTable values(3,'TURNER','2014-07-01')
insert into ActivityTable values(3,'TURNER','2014-07-31')
insert into ActivityTable values(3,'TURNER','2014-06-15')
insert into ActivityTable values(4,'TURNER-2','2014-07-01')
insert into ActivityTable values(4,'TURNER-2','2014-07-10')
go
with BaseData as (
select
CompanyId,
CompanyName,
ActivityDate,
rn = row_number() over (partition by CompanyId order by ActivityDate desc),
cnt = count(*) over (partition by CompanyId)
from
dbo.ActivityTable
), InterimData as (
select
CompanyId,
CompanyName,
min(ActivityDate) MinDate,
max(ActivityDate) MaxDate
from
BaseData
where
rn <= 3 and
cnt > 2
group by
CompanyId,
CompanyName
)
select
CompanyName
from
InterimData
where
datediff(day,MinDate,MaxDate) <= 15;
go
DROP TABLE [dbo].[ActivityTable];
go
September 7, 2014 at 3:39 pm
Here's a different approach.
WITH MinDates AS(
SELECT CompanyId,
DATEADD( dd, -15, MAX(ActivityDate)) MinDate
FROM ActivityTable
GROUP BY CompanyId
)
SELECT a.CompanyId,
a.CompanyName
FROM ActivityTable a
JOIN MinDates m ON a.CompanyId = m.CompanyId
AND a.ActivityDate > m.MinDate
GROUP BY a.CompanyId,
a.CompanyName
HAVING COUNT(*) >= 3;
September 7, 2014 at 3:43 pm
Lynn,
The posted solution seems to do the trick...thanks so much!
September 7, 2014 at 4:51 pm
Luis Cazares (9/7/2014)
Here's a different approach.
WITH MinDates AS(
SELECT CompanyId,
DATEADD( dd, -15, MAX(ActivityDate)) MinDate
FROM ActivityTable
GROUP BY CompanyId
)
SELECT a.CompanyId,
a.CompanyName
FROM ActivityTable a
JOIN MinDates m ON a.CompanyId = m.CompanyId
AND a.ActivityDate > m.MinDate
GROUP BY a.CompanyId,
a.CompanyName
HAVING COUNT(*) >= 3;
Where do you capture the three most recent dates?
September 7, 2014 at 4:58 pm
I don't. I just validate that there are at least 3 dates within the range. This works if the dates aren't important and you just want to know the companies.
September 7, 2014 at 5:00 pm
Luis Cazares (9/7/2014)
I don't. I just validate that there are at least 3 dates within the range. This works if the dates aren't important and you just want to know the companies.
From the OPs initial post:
I need to be able to retrieve the past 3 activity dates and see if the first and last occured within 15 days.
September 7, 2014 at 10:27 pm
For completeness, here is a complete solution to the problem using the method I posted earlier and the sample data set posted.
😎
;WITH QUERY_BASE AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.CompanyId
ORDER BY SD.ActivityDate DESC
) AS AD_RID
,SD.CompanyId
,SD.CompanyName
,SD.ActivityDate
FROM ActivityTable SD
)
,FINAL_SET AS
(
SELECT
QB.AD_RID
,QB.CompanyId
,QB.CompanyName
,QB.ActivityDate
,DATEDIFF(HOUR,QB.ActivityDate,MAX(QB.ActivityDate) OVER (PARTITION BY QB.CompanyId)) / 24 AS PeriodInDays
FROM QUERY_BASE QB
WHERE QB.AD_RID <= 3
)
SELECT
FS.AD_RID
,FS.CompanyId
,FS.CompanyName
,FS.ActivityDate
,FS.PeriodInDays
FROM FINAL_SET FS
WHERE FS.AD_RID = 3
AND FS.PeriodInDays >= 15
Results
AD_RID CompanyId CompanyName ActivityDate PeriodInDays
------- ----------- -------------------- ------------ ------------
3 2 AJAX 2014-05-20 82
3 3 TURNER 2014-06-15 46
September 8, 2014 at 6:33 am
Eirikur Eiriksson (9/7/2014)
For completeness, here is a complete solution to the problem using the method I posted earlier and the sample data set posted.😎
;WITH QUERY_BASE AS(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.CompanyId
ORDER BY SD.ActivityDate DESC
) AS AD_RID
,SD.CompanyId
,SD.CompanyName
,SD.ActivityDate
FROM ActivityTable SD
)
,FINAL_SET AS
(
SELECT
QB.AD_RID
,QB.CompanyId
,QB.CompanyName
,QB.ActivityDate
,DATEDIFF(HOUR,QB.ActivityDate,MAX(QB.ActivityDate) OVER (PARTITION BY QB.CompanyId)) / 24 AS PeriodInDays
FROM QUERY_BASE QB
WHERE QB.AD_RID <= 3
)
SELECT
FS.AD_RID
,FS.CompanyId
,FS.CompanyName
,FS.ActivityDate
,FS.PeriodInDays
FROM FINAL_SET FS
WHERE FS.AD_RID = 3
AND FS.PeriodInDays >= 15
Results
AD_RID CompanyId CompanyName ActivityDate PeriodInDays------- ----------- -------------------- ------------ ------------
3 2 AJAX 2014-05-20 82
3 3 TURNER 2014-06-15 46
Unfortunately, Eirikur, your solution does not return the correct result set as per the OPs requirement. The only company that should be returned from the sample data is ABC.
Look at the 1st and 3rd activity dates, only if the difference between these dates is 15 days or later do you return a result. If there are fewer than 3 records you also ignore that company.
September 8, 2014 at 7:12 am
Ooops, greater than is stead of less than in the last filter:pinch: Misread the post as "where three last activities are in a period greater or equal to 15 days"
😎
USE tempdb;
GO
;WITH QUERY_BASE AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.CompanyId
ORDER BY SD.ActivityDate DESC
) AS AD_RID
,SD.CompanyId
,SD.CompanyName
,SD.ActivityDate
FROM ActivityTable SD
)
,FINAL_SET AS
(
SELECT
QB.AD_RID
,QB.CompanyId
,QB.CompanyName
,QB.ActivityDate
,DATEDIFF(HOUR,QB.ActivityDate,MAX(QB.ActivityDate) OVER (PARTITION BY QB.CompanyId)) / 24 AS PeriodInDays
FROM QUERY_BASE QB
WHERE QB.AD_RID <= 3
)
SELECT
FS.AD_RID
,FS.CompanyId
,FS.CompanyName
,FS.ActivityDate
,FS.PeriodInDays
FROM FINAL_SET FS
WHERE FS.AD_RID = 3
AND FS.PeriodInDays <= 15
Results
AD_RID CompanyId CompanyName ActivityDate PeriodInDays
-------------------- ----------- -------------------- ------------ ------------
3 1 ABC 2014-08-01 9
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply