Post reply

Hepling with query getting the most recent record

  • June 16, 2015 at 1:42 pm

    #318215

    I've been trying various ways to accomplish this and I think I've reached a mental brick wall. If anyone is up for it, please consider helping me find clarity/sanity again.

    Here's the scenario. Consider the following sample data.

    CREATE TABLE #MyTest

    (

    CustomerNumber INT,

    Division CHAR,

    SalesRepType INT,

    SalesRepNumber INT,

    EnterDate DATE

    )

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,200, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,200, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,400, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,400, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,600, '02/02/2015')

    Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.

    CustomerNumberDivisionSalesRepTypeSalesRepNumberAssignedDate

    10000A11002/1/2015

    10000A23001/28/2015

    10000B14002/1/2015

    10000B26002/2/2015

    I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Thoughts on how I can go about this? Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and SalesRepType 1.

    WITH

    cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)

    AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType ORDER BY MIN(EnterDate) DESC) AS RowNum,

    CustomerNumber, Division, SalesRepType, SalesRepNumber, MIN(EnterDate) AS BeginDate

    FROM #MyTest

    GROUP BY CustomerNumber, Division, SalesRepType, SalesRepNumber

    )

    SELECT CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate

    FROM cteCust

    WHERE RowNum = 1

  • June 16, 2015 at 3:06 pm

    #1805905

    This can be solved by adding one more ROW_NUMBER() function to create groups based on the SalesRep.

    WITH

    cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)

    AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType ORDER BY EnterDate DESC)

    - ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType, SalesRepNumber ORDER BY EnterDate DESC) AS RowNum,

    CustomerNumber, Division, SalesRepType, SalesRepNumber, EnterDate AS BeginDate

    FROM #MyTest

    )

    SELECT CustomerNumber, Division, SalesRepType, SalesRepNumber, MIN( BeginDate) AS StartDate

    FROM cteCust

    WHERE RowNum = 0

    GROUP BY CustomerNumber, Division, SalesRepType, SalesRepNumber;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 16, 2015 at 3:06 pm

    #1805906

    LeeFAR (6/16/2015)

    I've been trying various ways to accomplish this and I think I've reached a mental brick wall. If anyone is up for it, please consider helping me find clarity/sanity again.

    Here's the scenario. Consider the following sample data.

    CREATE TABLE #MyTest

    (

    CustomerNumber INT,

    Division CHAR,

    SalesRepType INT,

    SalesRepNumber INT,

    EnterDate DATE

    )

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,200, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,200, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'A',1,100, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'A',2,300, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,500, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,400, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'B',1,400, '02/02/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/28/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/29/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/30/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '01/31/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,700, '02/01/2015')

    INSERT INTO #MyTest VALUES (10000,'B',2,600, '02/02/2015')

    Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.

    CustomerNumberDivisionSalesRepTypeSalesRepNumberAssignedDate

    10000A11002/1/2015

    10000A23001/28/2015

    10000B14002/1/2015

    10000B26002/2/2015

    I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Thoughts on how I can go about this? Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and SalesRepType 1.

    WITH

    cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)

    AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType ORDER BY MIN(EnterDate) DESC) AS RowNum,

    CustomerNumber, Division, SalesRepType, SalesRepNumber, MIN(EnterDate) AS BeginDate

    FROM #MyTest

    GROUP BY CustomerNumber, Division, SalesRepType, SalesRepNumber

    )

    SELECT CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate

    FROM cteCust

    WHERE RowNum = 1

    Using the sample data and expected results above, the following works:

    with BaseData as (

    select

    *,

    DateBatch = dateadd(day,row_number() over (partition by CustomerNumber, Division, SalesRepType, SalesRepNumber order by EnterDate desc),EnterDate)

    from

    #MyTest

    ), WorkingData as (

    select

    *,

    rn = row_number() over (partition by CustomerNumber, Division, SalesRepType order by DateBatch desc, EnterDate asc)

    from

    BaseData

    )

    select

    CustomerNumber,

    Division,

    SalesRepType,

    SalesRepNumber,

    EnterDate

    from

    WorkingData

    where

    rn = 1;

  • June 16, 2015 at 3:15 pm

    #1805908

    Luis Cazares (6/16/2015)

    This can be solved by adding one more ROW_NUMBER() function to create groups based on the SalesRep.

    WITH

    cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)

    AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType ORDER BY EnterDate DESC)

    - ROW_NUMBER() OVER (PARTITION By CustomerNumber, Division, SalesRepType, SalesRepNumber ORDER BY EnterDate DESC) AS RowNum,

    CustomerNumber, Division, SalesRepType, SalesRepNumber, EnterDate AS BeginDate

    FROM #MyTest

    )

    SELECT CustomerNumber, Division, SalesRepType, SalesRepNumber, MIN( BeginDate) AS StartDate

    FROM cteCust

    WHERE RowNum = 0

    GROUP BY CustomerNumber, Division, SalesRepType, SalesRepNumber;

    Started going that direction, but couldn't see the answer.

  • June 16, 2015 at 3:40 pm

    #1805913

    Thanks everyone. I went down the road of adding the SalesRepNumber into the mix, but didn't think to get it as another row_number. Good stuff...

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

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