Sql query help
-
If i have the following table
CREATE TABLE [dbo].[Visits](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NOT NULL,
[StaffId] [int] NOT NULL,
[ClientId] [int] NOT NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and the following example subset data
insert into Visits [Date], StaffId, ClientId
SELECT '2011-01-01','1','1' UNION ALL
SELECT '2011-02-01','1','1' UNION ALL
SELECT '2011-02-08','1','1' UNION ALL
SELECT '2011-02-09','1','1' UNION ALL
SELECT '2011-02-13','1','2' UNION ALL
SELECT '2011-02-14','1','3' UNION ALL
SELECT '2011-02-19','1','2' UNION ALL
SELECT '2011-02-23','1','1' UNION ALL
SELECT '2011-03-01','1','3' UNION ALL
SELECT '2011-04-01','1','1' UNION ALL
SELECT '2011-04-02','1','3' UNION ALL
SELECT '2011-04-10','1','1' UNION ALL
SELECT '2011-05-01','1','1' UNION ALL
SELECT '2011-05-03','1','2' UNION ALL
SELECT '2011-05-06','1','1' UNION ALL
SELECT '2011-05-15','1','1' UNION ALL
SELECT '2011-05-21','1','1'
How can i determine if a staff member has worked at the same client for longer than a 12 week period, given that they can have a 6 week break in between visits and it still count?
-
Could you use results returned from the following?
SELECT
StaffId
,ClientId
,MIN(Date) AS FirstDate
,MAX(Date) AS LastDate
,DATEDIFF(ww ,MIN(VisDate) ,MAX(VisDate)) as Weeks
FROM [dbo].[Visits]
GROUP BY StaffId ,ClientId
-
What are your expected results?
See if this helps
WITH CTE AS (SELECT [Date],StaffId,ClientId,
ROW_NUMBER() OVER(PARTITION BY StaffId,ClientId ORDER BY [Date]) AS rn
FROM dbo.Visits)
SELECT a.StaffId,a.ClientId
FROM CTE a
WHERE NOT EXISTS (SELECT * FROM CTE b
WHERE b.StaffId=a.StaffId
AND b.ClientId=a.ClientId
AND b.rn=a.rn+1
AND DATEDIFF(week,a.[Date],b.[Date])>6) -- check consecutive gaps not greater than 6 weeks
GROUP BY a.StaffId,a.ClientId
HAVING DATEDIFF(week,MIN(a.[Date]),MAX(a.[Date]))>12;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply