Query Help

Question

Query Help

yogi123

SSCrazy

Points: 2932
More actions
January 27, 2014 at 8:38 am

#288818

Hello Everyone
I have one task in that i need to pull Code from EFFDT.
The requirement is, i need to pull Code where EFFDT as of '2011-06-15'.
Please see below for DDL
CREATE TABLE #X1
(
EMPLID VARCHAR(09),
EFFDTDATETIME,
CODE INT
)
INSERT INTO #X1 VALUES ('000015957','2011-02-17 00:00:00.000',2301)
INSERT INTO #X1 VALUES ('000015957','2011-11-28 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000015957','2011-11-28 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000015957','2012-06-18 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000015957','2012-09-21 00:00:00.000',2301)
INSERT INTO #X1 VALUES ('000015957','2012-10-31 00:00:00.000',2301)
INSERT INTO #X1 VALUES ('000015957','2012-11-01 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000015957','2012-11-15 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000015957','2013-06-18 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000017857','2010-06-18 00:00:00.000',2301)
INSERT INTO #X1 VALUES ('000017857','2010-08-22 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000017857','2011-03-18 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000017857','2011-06-18 00:00:00.000',2301)
INSERT INTO #X1 VALUES ('000017857','2012-03-14 00:00:00.000',2148)
INSERT INTO #X1 VALUES ('000017857','2012-06-18 00:00:00.000',2148)
so for EmpID = '000015957' The Code will be 2148
and EmpID = '000017857' The Code will be 2148.
so please help me to build the logic.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in the future, make your post title something other than "query help"; you'll get better responses if ti was something more like "empid wherethe max date?" or something descriptive.

there's a bad habit out there right now, with a dozen posts a day with the same title of "Query Help"

great job on posting the DDL; i think in this case, i'd see if row_number() will give you what you are after:

/*

RW EMPLID EFFDT CODE

-------------------- --------- ----------------------- -----------

1 000015957 2011-02-17 00:00:00.000 2301

1 000017857 2011-03-18 00:00:00.000 2148

*/

SELECT * FROM

(SELECT ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EFFDT DESC) AS RW , *

FROM #X1

WHERE EFFDT < '2011-06-15') MyAlias

WHERE RW = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogi123 SSCrazy Points: 2932 More actions · Answer 2

Lowell (1/27/2014)
in the future, make your post title something other than "query help"; you'll get better responses if ti was something more like "empid wherethe max date?" or something descriptive.
there's a bad habit out there right now, with a dozen posts a day with the same title of "Query Help"
great job on posting the DDL; i think in this case, i'd see if row_number() will give you what you are after:
/*
RW EMPLID EFFDT CODE
-------------------- --------- ----------------------- -----------
1 000015957 2013-06-18 00:00:00.000 2148
1 000017857 2012-06-18 00:00:00.000 2148
*/
SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EFFDT DESC) AS RW , *
FROM #X1) MyAlias
WHERE RW = 1

Lowell

Thanks for Your Quick response.

Actully the requirement is need to find Code as of EFFDT is '2011-06-15'

meaning EmplID = '000015957 ' code is different between '2011-02-17 00:00:00.000' and '2011-11-28 00:00:00.000'. but need to pull code as of '2011-06-15' so it will be code from '2011-11-28 00:00:00.000'.

I know it sounds confusing .

so i need to find whats Employee Code status on '2011-06-15', not their most recent Code.

Lowell SSC Guru Points: 323518 More actions · Answer 3

you are too quick! i edited almost right away, but you grabbed my first verison.

i updated my post, i think it's filtering based on your date requirement now

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!