August 14, 2015 at 10:05 am
Its trivial compared to what most on here speak of, I know, but here it goes..
I have a SELECT statement is pulling two records for an employees and I am trying to eliminate one particular record when this happens...
They are not redundant records. I am falling victim to our application and employee info (end dates etc.. ) not being utilized as effectively as they should..
See an abbreviated result set below.
EEID Name Annualizing Factor PercentFullTime
1 Marvin, Leo 2080 1
1 Marvin, Leo 1640 .60
2 Doe, Jane 2080 1
3 Wiley, Bob 2080 1
3 Wiley, Bob 1640 .60
4 Marvin, Fay 1640 .60
In each case where an employee is listed twice they have the same Annualizing Factors and Percent Full time.. When both records are listed, I am only seeking to grab the Annualizing Factor of 2080. Any suggestions?
August 14, 2015 at 10:26 am
Something like this:
declare @TestTable table(
EEID int,
Name varchar(64),
AnnualizingFactor int,
PercentFullTime decimal(3,2));
insert into @TestTable
values
(1,'Marvin, Leo',2080,1 ),
(1,'Marvin, Leo',1640,.60),
(2,'Doe, Jane ',2080,1 ),
(3,'Wiley, Bob ',2080,1 ),
(3,'Wiley, Bob ',1640,.60);
select * from @TestTable;
with basedata as (
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime,
rn = row_number() over (partition by EEID order by PercentFullTime desc)
from
@TestTable
)
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime
from
basedata
where
rn = 1;
August 14, 2015 at 10:37 am
Lynn Pettis (8/14/2015)
Something like this:
declare @TestTable table(
EEID int,
Name varchar(64),
AnnualizingFactor int,
PercentFullTime decimal(3,2));
insert into @TestTable
values
(1,'Marvin, Leo',2080,1 ),
(1,'Marvin, Leo',1640,.60),
(2,'Doe, Jane ',2080,1 ),
(3,'Wiley, Bob ',2080,1 ),
(3,'Wiley, Bob ',1640,.60);
select * from @TestTable;
with basedata as (
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime,
rn = row_number() over (partition by EEID order by PercentFullTime desc)
from
@TestTable
)
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime
from
basedata
where
rn = 1;
When there's more than 1 row, that grabs the 1 row, not the 2080 row.
If the row for PercentFullTime = 1 is always the 2080 row, then that works.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 14, 2015 at 10:53 am
Alvin Ramard (8/14/2015)
Lynn Pettis (8/14/2015)
Something like this:
declare @TestTable table(
EEID int,
Name varchar(64),
AnnualizingFactor int,
PercentFullTime decimal(3,2));
insert into @TestTable
values
(1,'Marvin, Leo',2080,1 ),
(1,'Marvin, Leo',1640,.60),
(2,'Doe, Jane ',2080,1 ),
(3,'Wiley, Bob ',2080,1 ),
(3,'Wiley, Bob ',1640,.60);
select * from @TestTable;
with basedata as (
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime,
rn = row_number() over (partition by EEID order by PercentFullTime desc)
from
@TestTable
)
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime
from
basedata
where
rn = 1;
When there's more than 1 row, that grabs the 1 row, not the 2080 row.
If the row for PercentFullTime = 1 is always the 2080 row, then that works.
Lynn's solution works fine regardless of how many rows, can you demonstrate the problem Alvin?
Obviously the solution would fail if there was an AnnualizingFactor greater than 2080
😎
August 14, 2015 at 10:57 am
Quick alteration to Lynn's good solution, handles values both higher and lower than the target value
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_TEST_SET_20150814001') IS NOT NULL DROP TABLE dbo.TBL_TEST_SET_20150814001;
CREATE TABLE dbo.TBL_TEST_SET_20150814001
(
EEID INT NOT NULL
,[Name] VARCHAR(64) NOT NULL
,AnnualizingFactor INT NOT NULL
,PercentFullTime DECIMAL(3,2) NOT NULL
);
INSERT INTO dbo.TBL_TEST_SET_20150814001(EEID,[Name],AnnualizingFactor,PercentFullTime)
VALUES
(1,'Marvin, Leo' ,2080 ,1 ),
(1,'Marvin, Leo' ,1640 ,.60),
(2,'Doe, Jane ' ,2080 ,1 ),
(3,'Wiley, Bob ' ,1024 ,1 ),
(3,'Wiley, Bob ' ,2080 ,1 ),
(3,'Wiley, Bob ' ,4080 ,1 ),
(3,'Wiley, Bob ' ,1640 ,.60)
;
;WITH BASE_DATA AS
(
SELECT
TS.EEID
,ROW_NUMBER() OVER
(
PARTITION BY TS.[Name]
ORDER BY ISNULL(NULLIF(TS.AnnualizingFactor,2080),-9999999) ASC
) AS TS_RID
,TS.[Name]
,TS.AnnualizingFactor
,TS.PercentFullTime
FROM dbo.TBL_TEST_SET_20150814001 TS
)
SELECT
BD.EEID
,BD.TS_RID
,BD.Name
,BD.AnnualizingFactor
,BD.PercentFullTime
FROM BASE_DATA BD
WHERE BD.TS_RID = 1;
Results
EEID TS_RID Name AnnualizingFactor PercentFullTime
----- ------- ------------ ----------------- ----------------
2 1 Doe, Jane 2080 1.00
1 1 Marvin, Leo 2080 1.00
3 1 Wiley, Bob 2080 1.00
August 14, 2015 at 11:01 am
Eirikur Eiriksson (8/14/2015)
Alvin Ramard (8/14/2015)
Lynn Pettis (8/14/2015)
Something like this:
declare @TestTable table(
EEID int,
Name varchar(64),
AnnualizingFactor int,
PercentFullTime decimal(3,2));
insert into @TestTable
values
(1,'Marvin, Leo',2080,1 ),
(1,'Marvin, Leo',1640,.60),
(2,'Doe, Jane ',2080,1 ),
(3,'Wiley, Bob ',2080,1 ),
(3,'Wiley, Bob ',1640,.60);
select * from @TestTable;
with basedata as (
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime,
rn = row_number() over (partition by EEID order by PercentFullTime desc)
from
@TestTable
)
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime
from
basedata
where
rn = 1;
When there's more than 1 row, that grabs the 1 row, not the 2080 row.
If the row for PercentFullTime = 1 is always the 2080 row, then that works.
Lynn's solution works fine regardless of how many rows, can you demonstrate the problem Alvin?
Obviously the solution would fail if there was an AnnualizingFactor greater than 2080
😎
When there's multiple rows, the OP said the desired row is the one with AnnualizingFactor = 2080. As long as the #1 row is the 2080 (meaning 2080 is the "full time" row) then everything is ok.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 14, 2015 at 11:22 am
Alvin Ramard (8/14/2015)
Lynn Pettis (8/14/2015)
Something like this:
declare @TestTable table(
EEID int,
Name varchar(64),
AnnualizingFactor int,
PercentFullTime decimal(3,2));
insert into @TestTable
values
(1,'Marvin, Leo',2080,1 ),
(1,'Marvin, Leo',1640,.60),
(2,'Doe, Jane ',2080,1 ),
(3,'Wiley, Bob ',2080,1 ),
(3,'Wiley, Bob ',1640,.60);
select * from @TestTable;
with basedata as (
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime,
rn = row_number() over (partition by EEID order by PercentFullTime desc)
from
@TestTable
)
select
EEID,
Name,
AnnualizingFactor,
PercentFullTime
from
basedata
where
rn = 1;
When there's more than 1 row, that grabs the 1 row, not the 2080 row.
If the row for PercentFullTime = 1 is always the 2080 row, then that works.
Let's see. Looking at the data, when PercentFullTime = 1 AnnualizingFactor = 2080. Not sure what the problem is. When I run using the data provided I get the rows where the AnnualizingFactor is 2080. Don't like my ORDER BY, change it to the AnnualingFactor. Nothing in the data suggests that this value may exceed 2080.
August 14, 2015 at 11:35 am
Lynn, as far as I can tell, your script will work as long as the row for PercentFullTime = 1 is the same rows as AnnualizingFactor = 2080. For the sample data, that is always the case, so no problem. In real life with the real data? We can't answer that.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 14, 2015 at 12:03 pm
Alvin Ramard (8/14/2015)
Lynn, as far as I can tell, your script will work as long as the row for PercentFullTime = 1 is the same rows as AnnualizingFactor = 2080. For the sample data, that is always the case, so no problem. In real life with the real data? We can't answer that.
Can only base things off what the OP provides. If the info is incomplete and solution fails in production, not our fault.
August 14, 2015 at 12:20 pm
Lynn Pettis (8/14/2015)
Alvin Ramard (8/14/2015)
Lynn, as far as I can tell, your script will work as long as the row for PercentFullTime = 1 is the same rows as AnnualizingFactor = 2080. For the sample data, that is always the case, so no problem. In real life with the real data? We can't answer that.Can only base things off what the OP provides. If the info is incomplete and solution fails in production, not our fault.
I know that and I think it's what the OP needs. I just wanted to point out there there was an assumption made, which is probably the same assumption I would have made, and works for the sample date provided.
The way the question was posted, there's many questions left unanswered, for example, what if there's more than 1 row returned and none are for 2080? I'm guessing that would not happen, but we can't tell from the facts we were given.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 14, 2015 at 12:25 pm
Hello, All,
Thank you. I am overwhelmed with the expeditious replies. This was just a sample data set to get an idea multiple records that are being retuned. I do not always know who the employees with multiple records will be and this could change throughout the year. So, I am not sure how effective declaring them up would be when they could change at any time. I am curious how this changes your approach?
August 14, 2015 at 12:30 pm
It is implied that if there are two occurrences of the same [Name] element then one will have the AnnualizingFactor of 2080. It is also implied that under no other circumstances would more than one occurrence of the same [Name] element appear. Now it is also not guaranteed that the 2080 will have the PercentFullTime value of 1. If this logic is incomplete then we just have to wait for the OP's response.
😎
August 14, 2015 at 12:30 pm
tjs8984 (8/14/2015)
Hello, All,Thank you. I am overwhelmed with the expeditious replies. This was just a sample data set to get an idea multiple records that are being retuned. I do not always know who the employees with multiple records will be and this could change throughout the year. So, I am not sure how effective declaring them up would be when they could change at any time. I am curious how this changes your approach?
Without seeing how the data may change, can't say.
August 14, 2015 at 12:30 pm
Hi, Alvin,
My apologies for a vaguely wording the initial post. Hopefully this answers your question - Of the multiple employee records returned they always have both 2080 (annualizing factor) and another row with 1640 (annualizing factor).
The remaining employees, who have one record, will always have 2080 (annualizing factor) or 1640 (annualizing factor)
August 14, 2015 at 12:34 pm
First thing I would do is run a query that shows be all the people with the multiple rows, and their data. Then, looking at the data, see if you can figure out rules for each case to determine which one record should be returned and which should be ignored.
Only after that should you think about how to right the SQL statement to only return one record per person.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply