October 22, 2013 at 8:09 pm
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
October 22, 2013 at 8:48 pm
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?
October 22, 2013 at 10:08 pm
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
October 22, 2013 at 11:24 pm
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
October 22, 2013 at 11:31 pm
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
October 23, 2013 at 2:54 am
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
October 24, 2013 at 6:09 pm
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/
October 24, 2013 at 6:24 pm
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 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