how to write join part of query so that only most recent equipment assignments are returned

  • Using SQL Server 2008

    I've got to find who's got our equipment. Should be easy, cause there's a table (location_history) that has a compound PK, tracking the latest association of equipment to location. However, while I'm doing fine on the first join of my query, after that the result set balloons.

    My result set should contain only the most recent equipment assignment (based on date_assigned column).

    DDL

    create table iPhones

    (iphone_id int,

    seriel_number varchar(20),

    PRIMARY KEY (iphone_id)

    )

    insert into iPhones

    values

    (1, 'dfdasfkljlj'),

    (2, 'werdfaflldk'),

    (3, 'cvcvcnmmkds');

    create table location_history

    (iphone_id int,

    location_id int,

    date_assigned datetime,

    PRIMARY KEY (iphone_id, location_id, date_assigned)

    );

    insert into location_history

    values

    (01, 63, getdate()-100),

    (02, 64, getdate()-99),

    (03, 65, GETDATE()-98),

    (01, 108, GETDATE()-90),

    (02, 112, getdate()-91),

    (03, 115, GETDATE()-92),

    (01, 122, GETDATE()-80),

    (02, 130, getdate()-79),

    (03, 140, getdate()-78);

    create table locations

    (location_id int,

    Emp_name varchar(10),

    Department varchar(10),

    PRIMARY KEY (location_id));

    insert into locations

    values

    (63, 'Betty', 'Test'),

    (64, 'Barb', 'Test'),

    (65, 'Rob', 'Dev'),

    (108, 'Steve', 'Dev'),

    (112, 'Becky', 'Dev'),

    (115, 'Leonard', 'Test'),

    (122, 'Ziggy', 'Dev'),

    (130, 'Joe', 'Test'),

    (140, null, 'Test');

    create table Department

    (dept_id int,

    department varchar(10)

    PRIMARY KEY (dept_id));

    insert into Department

    values

    (1, 'Test'),

    (2, 'Dev');

    QUERY 1. one of my attempts:

    ;with cte as

    (

    select

    distinct

    i.iphone_id,

    i.seriel_number,

    lh.date_assigned,

    l.Emp_name,

    d.department

    from iphones i

    left outer join location_history lh

    on i.iphone_id=lh.iphone_id

    left outer join locations l

    on lh.location_id= l.location_id

    left outer join department d

    on l.department = d.department

    )

    select

    iphone_id,

    seriel_number,

    max(date_assigned),

    Emp_name,

    department

    from cte

    group by

    iphone_id,

    seriel_number,

    Emp_name,

    department

    QUERY 2. another one of my attempts, where I isolated the max assigned_date in subquery, but didnt' know how to continue:

    select

    distinct

    i.iphone_id,

    i.seriel_number,

    lh.Last_Date_Assigned

    from iphones i

    inner join (

    select iphone_id, MAX(date_assigned) as Last_Date_Assigned

    from location_history group by iphone_id

    )lh

    on i.iphone_id = lh.iphone_id

    need result set to contain only the most recent assignment of the iphone (seriel #), date, person, department.

    SELECT 1, 'dfdasfkljlj', '2013-08-03 18:13:21.587', 'Ziggy', 'Dev' UNION ALL

    SELECT 2, 'werdfaflldk', '2013-08-04 18:13:21.587', 'Joe', 'Test' UNION ALL

    SELECT 3, 'cvcvcnmmkds', '2013-08-05 18:13:21.587, NULL, 'Test';

    How to fix either query to get the results?

    --Quote me

  • Just thinking...

    what if you did a summary on this:

    iphone_id,

    seriel_number,

    max(date_assigned),

    and then joined that result back to the table to return the rest of the info you need?

    so

    max(date_assigned) = assignments.date_assigned

    and serial_number = assignments.serial_number

    that would give you the latest date_assigned and then all the related info, right?

  • i supplied ddl. Can you show? I don't understand, otherwise, what you're getting at.

    personally, don't understand why the group by in QUERY 1 isn't working.

    --Quote me

  • does this work for you ...?

    with cte as

    (

    SELECT iphone_id, MAX(date_assigned) AS Maxd

    FROM location_history

    GROUP BY iphone_id

    )

    SELECT iPhones.iphone_id,

    iPhones.seriel_number,

    cte.Maxd,

    locations.Emp_name,

    locations.Department

    FROM iPhones

    INNER JOIN cte ON iPhones.iphone_id = cte.iphone_id

    INNER JOIN location_history ON cte.iphone_id = location_history.iphone_id

    AND cte.Maxd = location_history.date_assigned

    INNER JOIN locations ON location_history.location_id = locations.location_id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hi,

    Change the 2nd like this you will get the result

    select

    distinct

    i.iphone_id,

    i.seriel_number,

    lh.Last_Date_Assigned,

    loc.emp_name,

    loc.Department

    from iphones i

    inner join (

    select iphone_id, MAX(date_assigned) as Last_Date_Assigned

    from location_history group by iphone_id

    )lh

    join location_history loch on loch.date_assigned = lh.Last_Date_Assigned and loch.iphone_id=lh.iphone_id

    join locations loc on loc.location_id = loch.location_id

    on i.iphone_id = lh.iphone_id

  • i'm partial to parulprabu showing me how to make query 2 work using this join.

    join location_history loch on loch.date_assigned = lh.Last_Date_Assigned and loch.iphone_id=lh.iphone_id.

    really appreciate.

    Thanks to you both for your replies. I'll look to see if there's a performance benefit of one over the other.

    --Quote me

  • A little late to the party, but this is a variation on the cte that was posted earlier by J Livingston SQL. I like the cte because it is so clean. Give it a shot.

    ;with cte as

    (

    Select iPhone_id, location_id, date_assigned,

    ROW_NUMBER() over(partition by iphone_id order by date_assigned desc) RowNum

    from location_history

    )

    select iP.iphone_id, iP.seriel_number, c.date_assigned, l.Emp_name, l.Department

    from iPhones iP

    join cte c on c.iPhone_id = iP.iphone_id and c.RowNum = 1

    join locations l on l.location_id = c.location_id

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Yet another way:

    SELECT *

    FROM iPhones a

    CROSS APPLY

    (

    SELECT TOP 1 location_id, date_assigned

    FROM location_history b

    WHERE a.iphone_id = b.iphone_id

    ORDER BY date_assigned DESC

    ) b

    JOIN locations c ON b.location_id = c.location_id

    JOIN Department d ON c.department = d.department;


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 8 posts - 1 through 7 (of 7 total)

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