September 16, 2011 at 9:09 am
Hello all.
Apologies if this has been asked elsewhere but I am struggling to find an exact answer to my query and I am keen to do this as efficiently as possible.
I have a table that outputs something like the following:
RowN EmpID CompletedDate
112011-06-27 15:54:48.247
212011-07-08 08:53:50.190
31NULL
41NULL
142011-08-16 16:12:27.057
242011-09-06 16:31:29.603
192011-07-08 10:30:14.773
292011-07-19 12:23:35.803
392011-09-01 12:26:46.213
49NULL
when I run this query:
SELECTTOP(10) ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY EmpID, ScheduledDate ASC) AS 'RowN',
EmpID, CompletedDate
FROM contact_frequency
What I would like to do if calculate the difference in date between rows for each individual employee ID (EmpID) and, if it is the last contact for the employee to use the difference between that and today's date.
What is the most efficient approach to take and how do I do it?
Any help is very much appreciated.
September 16, 2011 at 9:47 am
I don't think I understand if the data you included in your post is the actual data in your table or the output of the query you posted.
Can you post:
1) Sample data
2) Table scripts
3) Expected output based on sample data?
If in doubt, read the first article linked in my signature line to find out how to ask for help effectively.
I formatted your sample data so that you can understand what I mean:
WITH SampleData (RowN, EmpID, CompletedDate)
AS (
SELECT 1,1,'2011-06-27 15:54:48.247'
UNION ALL SELECT 2,1,'2011-07-08 08:53:50.190'
UNION ALL SELECT 3,1,NULL
UNION ALL SELECT 4,1,NULL
UNION ALL SELECT 1,4,'2011-08-16 16:12:27.057'
UNION ALL SELECT 2,4,'2011-09-06 16:31:29.603'
UNION ALL SELECT 1,9,'2011-07-08 10:30:14.773'
UNION ALL SELECT 2,9,'2011-07-19 12:23:35.803'
UNION ALL SELECT 3,9,'2011-09-01 12:26:46.213'
UNION ALL SELECT 4,9,NULL
)
SELECT *
FROM SampleData
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply