Counting a consecutive condition
-
I'm hoping that someone can guide me in the right direction. I have a table with various fields. The fields that I am intrested in are the empId, date and met fields. The empId field holds the employee id, the date field of course is the date and the met filed is a boolean field that displays whether an employee met or did not meet their goals for the day. I need to produce a report that will list all employees that have met their goals at least 5 consecutive days in a row. In other words 5 consecutive mets. I really have no idea where to start on this one. Any help would be appreciated.
Thanks in advance.
-
If you store the 'met' field for each day, then could you rephrase what you're looking for as:
"Find an employee where there doesn't exist a 'not met' row between any particular day and 5 days afterwards"
If you want to make sure that there is a start-record and last-record, then you could actually join on your table twice, once for the first case, and once for the last case.
If you want to be sure there are actually 3 records between the first and last (rather than, say, a weekend)... then you could put a "(select count(*) from mytable as InBetween...) = 3"... all kinds of options.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Hi JR,
Did a bit of work on this a while ago and have a working example - not the prettiest code in the world but hopefully it will help. Should be enough comments in it to allow you to work out what its doing and why, if not just give me a shout.
I have changed it slightly to look for 5 consecutive days (my original looked for 3...) but this should just copy and paste into QA (and hopefully just run unless i've missed something.......):
--------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DateTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DateTest]
GO
CREATE TABLE [dbo].[DateTest] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO DateTest
(1)
VALUES (CAST('03-17-2004' AS DATETIME(8)))
INSERT INTO DateTest
(2)
VALUES (CAST('03-18-2004' AS DATETIME(8)))
INSERT INTO DateTest
(3)
VALUES (CAST('03-19-2004' AS DATETIME(8)))
INSERT INTO DateTest
(4)
VALUES (CAST('03-20-2004' AS DATETIME(8)))
INSERT INTO DateTest
(5)
VALUES (CAST('03-21-2004' AS DATETIME(8)))
INSERT INTO DateTest
(6)
VALUES (CAST('03-27-2004' AS DATETIME(8)))
INSERT INTO DateTest
(7)
VALUES (CAST('03-29-2004' AS DATETIME(8)))
INSERT INTO DateTest
(8)
VALUES (CAST('03-30-2004' AS DATETIME(8)))
INSERT INTO DateTest
(9)
VALUES (CAST('04-03-2004' AS DATETIME(8)))
INSERT INTO DateTest
(10)
VALUES (CAST('04-04-2004' AS DATETIME(8)))
INSERT INTO DateTest
(11)
VALUES (CAST('04-05-2004' AS DATETIME(8)))
INSERT INTO DateTest
(12)
VALUES (CAST('04-08-2004' AS DATETIME(8)))
INSERT INTO DateTest
(13)
VALUES (CAST('04-09-2004' AS DATETIME(8)))
INSERT INTO DateTest
(14)
VALUES (CAST('04-14-2004' AS DATETIME(8)))
INSERT INTO DateTest
(15)
VALUES (CAST('04-15-2004' AS DATETIME(8)))
INSERT INTO DateTest
(16)
VALUES (CAST('04-16-2004' AS DATETIME(8)))
INSERT INTO DateTest
(17)
VALUES (CAST('04-17-2004' AS DATETIME(8)))
INSERT INTO DateTest
(18)
VALUES (CAST('04-18-2004' AS DATETIME(8)))
INSERT INTO DateTest
(19)
VALUES (CAST('04-25-2004' AS DATETIME(8)))
INSERT INTO DateTest
(20)
VALUES (CAST('04-27-2004' AS DATETIME(8)))
INSERT INTO DateTest
(21)
VALUES (CAST('04-28-2004' AS DATETIME(8)))
INSERT INTO DateTest
(22)
VALUES (CAST('04-29-2004' AS DATETIME(8)))
DECLARE @DateStart as datetime,
@DateEnd as datetime,
@MinPK as int,
@MaxPK as int,
@BaseDate as datetime,
@Date2 as datetime,
@Date3 as datetime,
@Date4 as datetime,
@Date5 as datetime
--Give yourself a date range
SET @DateStart = CAST('03/01/2004' as datetime)
SET @DateEnd = CAST('04/30/2004' as datetime)
--Copy the Key Value and the date values you want to evalute into a hash table
--This will need doing for each person
select PK, OrderDate INTO #TEMPOrders from DateTest
WHERE OrderDate BETWEEN @DateStart AND @DateEnd
--Get a minimum and maximum key value range to loop through
SET @MinPK = (SELECT MIN(PK) FROM #TEMPOrders WHERE OrderDate >= @DateStart AND OrderDate <= @DateEnd)
SET @MaxPK = (SELECT MAX(PK) FROM #TEMPOrders WHERE OrderDate >= @DateStart AND OrderDate <= @DateEnd)
--Cursorless loop, from the minimum to the maximum
WHILE @MinPK <= @MaxPK
BEGIN
--Set the date variables
SET @BaseDate = (SELECT OrderDate FROM #TEMPOrders WHERE PK = @MinPK)
SET @Date2 = (SELECT top 1 OrderDate FROM #TEMPOrders WHERE OrderDate > @BaseDate)
SET @Date3 = (SELECT top 1 OrderDate FROM #TEMPOrders WHERE OrderDate > @Date2)
SET @Date4 = (SELECT top 1 OrderDate FROM #TEMPOrders WHERE OrderDate > @Date3)
SET @Date5 = (SELECT top 1 OrderDate FROM #TEMPOrders WHERE OrderDate > @Date4)
--See if Date 1 and date 2 have a difference of 1 day AND date 2 and date 3 have a difference of 1 day etc
--IF they have we have 3 consecutive days
IF DATEDIFF(dd, @BaseDate, @Date2) = 1 AND DATEDIFF(dd, @Date2, @Date3) = 1
AND DATEDIFF(dd, @Date3, @Date4) = 1 AND DATEDIFF(dd, @Date4, @Date5) = 1
BEGIN
--Do whatever you do when you have 3 consecutive days
PRINT 'Five consecutive dates - ' + CAST(@BaseDate as varchar(20)) + ' to ' + CAST(@Date5 as varchar(20))
--Increment to the key value for the 3rd date so we dont hit a consecutive 3 day patch
--eg - 3,4,5 and 6 would hit twice 3 to 5 and 4 to 6
--May not be necessary, depends on your needs.
SET @MinPK = (SELECT MIN(PK) FROM #TEMPOrders WHERE PK > @MinPK AND OrderDate = @Date2)
END
--Increment the end point
SET @MinPK = (SELECT MIN(PK) FROM #TEMPOrders WHERE PK > @MinPK)
END
--Dump the temp table
DROP TABLE #TEMPOrders
---------------------------------------------------------------------------
And no dynamic sql......
Have fun
Steve
We need men who can dream of things that never were.
-
or this
SELECT DISTINCT a.empId
FROM [yourtable] a
LEFT OUTER JOIN [yourtable] b ON b.empId = a.empId AND b.[date] = a.[date]+1 AND b.met =1
LEFT OUTER JOIN [yourtable] c ON c.empId = a.empId AND c.[date] = a.[date]+2 AND c.met =1
LEFT OUTER JOIN [yourtable] d ON d.empId = a.empId AND d.[date] = a.[date]+3 AND d.met =1
LEFT OUTER JOIN [yourtable] e ON e.empId = a.empId AND e.[date] = a.[date]+4 AND e.met =1
WHERE a.met = 1
AND ISNULL(b.met,0) = 1
AND ISNULL(c.met,0) = 1
AND ISNULL(d.met,0) = 1
AND ISNULL(e.met,0) = 1
but performance may be a problem
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
That looks shorter and prettier.....
Have fun
Steve
We need men who can dream of things that never were.
-
Here's a simpler one:
select distinct e1.emp_id
from #emp e1 INNER JOIN #emp e2
on e1.emp_id = e2.emp_id and datediff(d,e1.date,e2.date) between 0 and 4
Where e2.date>=e1.date
group by e1.emp_id,e1.date
having sum(Case e2.met When 1 Then 1 Else 0 End) >=5
order by 1
Does anyone need a detailed explanation?
Basically I try to sum up the 'met' records for consecutive dates, if the sum is >= 5 then it means the employee has met the requirement.
-
nice
slight change to reduce data for aggregates
select distinct e1.emp_id
from #emp e1
INNER JOIN #emp e2
on e1.emp_id = e2.emp_id
and e2.met = 1
and e2.date >= e1.date
and datediff(d,e1.date,e2.date) between 0 and 4
group by e1.emp_id,e1.date
having count(*) >=5
order by 1
Far away is close at hand in the images of elsewhere.
Anon. -
By the way if you are going to use above query just make sure you set up a constraint on EmpID and Date (To avoid the asumtion that the query is making about not having duplicates and guarantee data quality as well )
* Noel -
This solution may be easier to understand and handles those cases where there are gaps in the employee objective dates.
select EmployeeObjectiveStatus.EmployeeID
fromdbo.EmployeeObjectiveStatus
where EmployeeObjectiveStatus.ObjectiveDate= '2005-01-06'
andEmployeeObjectiveStatus.MetInd= 'Y'
and4 = -- All 4 prior days
(select count(*)
fromdbo.EmployeeObjectiveStatus as EOS_Prior
whereEOS_Prior.EmployeeID = EmployeeObjectiveStatus.EmployeeID
andEOS_Prior.MetInd= 'Y'
andEOS_Prior.ObjectiveDate = EmployeeObjectiveStatus.ObjectiveDate - 4
)
go
-- Below is the DDL and test data:
create table EmployeeObjectiveStatus
(EmployeeIDint not null
,ObjectiveDatedatetime not null
,MetIndchar(1)not null
, constraint EmployeeObjectiveStatus_C CHECK (MetInd = 'Y' or MetInd = 'N' )
, constraint EmployeeObjectiveStatus_p primary key (EmployeeID,ObjectiveDate)
)
go
insert into EmployeeObjectiveStatus
( EmployeeID, ObjectiveDate, MetInd )
-- EmployeeId = 4 has met for only one day and has no rows for the other days.
select 43, '2005-01-06', 'Y' union all
-- EmployeeId = 1 has met for 10 consecutive days
select 1, '2005-01-01', 'Y' union all
select 1, '2005-01-02', 'Y' union all
select 1, '2005-01-03', 'Y' union all
select 1, '2005-01-04', 'Y' union all
select 1, '2005-01-05', 'Y' union all
select 1, '2005-01-06', 'Y' union all
select 1, '2005-01-07', 'Y' union all
select 1, '2005-01-08', 'Y' union all
select 1, '2005-01-09', 'Y' union all
select 1, '2005-01-10', 'Y' union all
-- EmployeeId = 1 has met for 6 consecutive days
select 2, '2005-01-01', 'Y' union all
select 2, '2005-01-02', 'Y' union all
select 2, '2005-01-03', 'Y' union all
select 2, '2005-01-04', 'Y' union all
select 2, '2005-01-05', 'Y' union all
select 2, '2005-01-06', 'Y' union all
select 2, '2005-01-07', 'N' union all
select 2, '2005-01-08', 'Y' union all
select 2, '2005-01-09', 'Y' union all
select 2, '2005-01-10', 'Y' union all
-- EmployeeId = 3 has met for 4 consecutive days
select 3, '2005-01-01', 'Y' union all
select 3, '2005-01-02', 'N' union all
select 3, '2005-01-03', 'Y' union all
select 3, '2005-01-04', 'Y' union all
select 3, '2005-01-05', 'Y' union all
select 3, '2005-01-06', 'Y' union all
select 3, '2005-01-07', 'N' union all
select 3, '2005-01-08', 'Y' union all
select 3, '2005-01-09', 'Y' union all
select 3, '2005-01-10', 'Y'
SQL = Scarcely Qualifies as a Language
-
Wow! Thanks for all the responses. It looks like I have a lot of different avenues to take. I'll take a look at all options and i'll post back with what worked the best for me.
Thanks again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply