August 10, 2016 at 12:35 pm
I have a table PS_JOB with the following records.....
emplid effdt effseqjobcode
00001068 3/27/20050 0241
00001068 3/27/20051 0244
I am trying to pull any changes to the jobcode field. In this case, I want to return the following...
emplid effdteffseqjobcodeprev_jobcode
00001068 3/27/200510244 0241
I normally use this logic when it comes to effective dating...
select j.emplid,j.effdt,j.effseq,j.jobcode,pp.jobcode as prev_jc
from ps_job j
inner join ps_job pp on pp.emplid = j.emplid -- get previous ft/pt
and pp.effdt = (select max(effdt) from ps_job
where emplid = pp.emplid
and jobcode <> j.jobcode
and effdt <= j.effdt)
and pp.effseq = (select max(effseq) from ps_job
where emplid = pp.emplid
and jobcode <> j.jobcode
and effdt = pp.effdt)
However, in this instance it returns 2 records because both records have the same effdt but different effseq.
emplid effdt effseqjobcodeprev_jc
00001068 3/27/200500241 0244
00001068 3/27/200510244 0241
I need help in modifying the code to return just the single record above
August 10, 2016 at 12:56 pm
is250sp (8/10/2016)
I have a table with the following records.....
emplid effdt effseqjobcode00001068 3/27/200500241
00001068 3/27/20051 0244
I am trying to pull any changes to the jobcode field. In this case, I want to return the following...
emplideffdteffseqjobcodeprev_jobcode
00001068 2005-03-2710244 0241
First, try to include easily consumable sample data like so:
DECLARE @table
TABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
What are you ordering by? Perhaps this will do the trick:
DECLARE @table
TABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
SELECT b1.emplid, b1.effdt, b1.effseq, prev = b1.jobcode, b2.effseq
FROM base b1
JOIN base b2 ON b1.emplid = b2.emplid AND b1.effdt = b2.effdt AND b1.rn = b2.rn+1;
-- Itzik Ben-Gan 2001
August 10, 2016 at 1:00 pm
Does this work for you?
DECLARE @myTable TABLE (empID INT, effdt DATETIME, effseq BIT, jobcode INT)
INSERT INTO @myTable
VALUES (00001068, '20050327', 0, 0241), (00001068, '20050327', 1, 0244), (00001070, '20050327', 0, 0241), (00001070, '20050330', 1, 0241)
;WITH myRank AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY empID ORDER BY effdt DESC, effseq DESC) AS RankNum,
*
FROM @myTable
)
SELECT * FROM myRank WHERE RankNum = 1
EDIT: Doh, looks like Alan beat me to a very similar answer. Just keep in mind that unless you posted in the wrong forum SQL 2005 doesn't support the 'DATE' data type so you'll have to use DATETIME otherwise.
August 10, 2016 at 1:22 pm
Alan.B (8/10/2016)
is250sp (8/10/2016)
I have a table with the following records.....
emplid effdt effseqjobcode00001068 3/27/200500241
00001068 3/27/20051 0244
I am trying to pull any changes to the jobcode field. In this case, I want to return the following...
emplideffdteffseqjobcodeprev_jobcode
00001068 2005-03-2710244 0241
First, try to include easily consumable sample data like so:
DECLARE @table
TABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
What are you ordering by? Perhaps this will do the trick:
DECLARE @tableTABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
SELECT b1.emplid, b1.effdt, b1.effseq, prev = b1.jobcode, b2.effseq
FROM base b1
JOIN base b2 ON b1.emplid = b2.emplid AND b1.effdt = b2.effdt AND b1.rn = b2.rn+1;
I had to make some changes to your script in order to run it, but still getting this error..
DECLARE @table TABLE (emplid varchar(10), effdt datetime, effseq int, jobcode varchar(4))
INSERT into @table VALUES ('00001068','3/27/2005', 0,'0241')
INSERT into @table VALUES ('00001068','3/27/2005',1,'0244')
WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
SELECT b1.emplid, b1.effdt, b1.effseq, prev = b1.jobcode, b2.effseq
FROM base b1
JOIN base b2 ON b1.emplid = b2.emplid AND b1.effdt = b2.effdt AND b1.rn = b2.rn+1;
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
August 10, 2016 at 1:39 pm
is250sp (8/10/2016)
Alan.B (8/10/2016)
is250sp (8/10/2016)
I have a table with the following records.....
emplid effdt effseqjobcode00001068 3/27/200500241
00001068 3/27/20051 0244
I am trying to pull any changes to the jobcode field. In this case, I want to return the following...
emplideffdteffseqjobcodeprev_jobcode
00001068 2005-03-2710244 0241
First, try to include easily consumable sample data like so:
DECLARE @table
TABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
What are you ordering by? Perhaps this will do the trick:
DECLARE @tableTABLE (emplid varchar(10), effdt date, effseq varchar(10), jobcode varchar(10));
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244');
WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
SELECT b1.emplid, b1.effdt, b1.effseq, prev = b1.jobcode, b2.effseq
FROM base b1
JOIN base b2 ON b1.emplid = b2.emplid AND b1.effdt = b2.effdt AND b1.rn = b2.rn+1;
I had to make some changes to your script in order to run it, but still getting this error..
DECLARE @table TABLE (emplid varchar(10), effdt datetime, effseq int, jobcode varchar(4))INSERT into @table VALUES ('00001068','3/27/2005', 0,'0241')
INSERT into @table VALUES ('00001068','3/27/2005',1,'0244')
WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
SELECT b1.emplid, b1.effdt, b1.effseq, prev = b1.jobcode, b2.effseq
FROM base b1
JOIN base b2 ON b1.emplid = b2.emplid AND b1.effdt = b2.effdt AND b1.rn = b2.rn+1;
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
That's because you have to terminate any previous statements prior to using a CTE. Just add a semicolon and it should work.
[highlight="#ffff11"];[/highlight]WITH base AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY emplid, effdt ORDER BY (SELECT NULL)) ,*
FROM @table
)
Notice in Alan's example he does it here which is probably better practice but it's functionally the same.
INSERT @table VALUES
('00001068','3/27/2005', '00241',''),
('00001068','3/27/2005','1','0244')[highlight="#ffff11"];[/highlight]
August 10, 2016 at 3:33 pm
Thank you everyone! I was able to use the Rank function to modify my script to account for all of the other records where the jobcode changes occurred not only on the same effdt, but also when the effdt where different.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply