Seeking guidance regarding a tSQL Script

  • 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?

  • 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;

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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

    😎

  • 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

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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.

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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.

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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?

  • 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.

    😎

  • 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.

  • 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)

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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