Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to write join part of query so that only most recent equipment assignments are returned Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 8:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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?
Post #1507445
Posted Tuesday, October 22, 2013 8:48 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 811, Visits: 5,155
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?
Post #1507452
Posted Tuesday, October 22, 2013 10:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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.
Post #1507459
Posted Tuesday, October 22, 2013 11:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 1,912, Visits: 19,453
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
Post #1507462
Posted Tuesday, October 22, 2013 11:31 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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

Post #1507464
Posted Wednesday, October 23, 2013 2:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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.
Post #1507505
Posted Thursday, October 24, 2013 6:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 357, Visits: 1,478
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/
Post #1508303
Posted Thursday, October 24, 2013 6:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 3,420, Visits: 5,347
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1508304
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse