November 5, 2010 at 10:04 am
I have the following table:
ID EmployeeNumber EmployeePhotograph EmployeeSignature Image Date
1 345 Image Image 2010-09-25 14:03:54.397
2 657 Image Image 2010-09-25 14:03:54.397
3 345 NULL Image 2010-10-27 16:23:00.397
4 231 Image Image 2010-09-25 14:03:54.397
I need to select the most current images and dates for that image for a given employeenumber. Note the most current signature image and photo images do not have to be captured at the same time. Is it possible to do this from a single query or will I need either a function or intermediate table? I have tried several things, all without success, to get the data in a single query but including the Image seems to be the limiting factor. For example in the above data, I want to select, for employee number 345 the photograph from id 1. A second query would return the signature image from row 3 for employee 345.
Thanx
November 5, 2010 at 1:48 pm
You can use CTE's in combination with Row_Number() function to achieve this.
For example:
with Image_CTE
as
(
select ...
, <row_number function>
from <table>
where image is not null
)
,
Signature_CTE
as
(
select ...
, <row_number function>
from <table>
where signature is not null
)
select ...
from Image_CTE i
join <you will have to determine which join is appropriate> Signature_CTE s
where i.<row_number field> = 1 and s.<row_number field> = 1
Hope this helps.
November 5, 2010 at 3:19 pm
For two separate queries, something like this:
CREATE TABLE #EmployeeInfo (
ID INT,
EmployeeNumber INT,
EmployeePhotograph NVARCHAR(10),
EmployeeSignature NVARCHAR(10),
ImageDate DATETIME
)
INSERT INTO #EmployeeInfo
SELECT 1,345,'Photo1','Sig1','2010-09-25 17:03:54.397' UNION ALL
SELECT 2,657,'Photo2','Sig2','2010-09-25 14:02:54.397' UNION ALL
SELECT 3,345,NULL,'Sig3','2010-09-25 16:23:00.397' UNION ALL
SELECT 4,231,'Photo3','Sig4','2010-09-25 14:03:54.397' UNION ALL
SELECT 5,231,'Photo4','Sig5','2010-09-25 15:01:54.397'
;WITH GetLatestPhoto AS (
SELECT RN = ROW_NUMBER() OVER (PARTITION BY EmployeeNumber ORDER BY ImageDate DESC)
,EmployeeNumber,EmployeePhotograph,ImageDate FROM #EmployeeInfo
WHERE EmployeePhotograph IS NOT NULL
)
SELECT * FROM GetLatestPhoto
WHERE RN = 1
;WITH GetLatestSig AS (
SELECT RN = ROW_NUMBER() OVER (PARTITION BY EmployeeNumber ORDER BY ImageDate DESC)
,EmployeeNumber,EmployeeSignature,ImageDate FROM #EmployeeInfo
WHERE EmployeeSignature IS NOT NULL
)
SELECT * FROM GetLatestSig
WHERE RN = 1
DROP TABLE #EmployeeInfo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply