SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subquery issues/help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

morleyz
Starting Member

17 Posts

Posted - 03/19/2013 :  09:54:52  Show Profile  Reply with Quote
This seems like something that would be commonly asked, but I can't seem to find my own answer, so sorry if it's a FAQ.

We have a new asset management system that automatically collects information about computers on our network. We have to build a view that is used by our help desk system to import asset information. One of the nice features is that the asset management system goes out and retrieves warranty information from the manufacturer. The bad news is that it doesn't distinguish between types of warranties if there are multiple types (such as full warranty, parts only, etc.). For our view, we'd like to retrieve just the date that is furthest out.

Simplified Data Structure:
TableAssets
AssetID
AssetName
AssetInfo

TableWarranties
WarrantyID
AssetID
WarrantyExpireDate

Example Data:
TableAssets (AssetID, AssetName, AssetInfo)
1,Computer1,Notebook Computer
2,Computer2,Desktop Computer

TableWarranties (WarrantyID, AssetID, WarrantyExpireDate)
1,1,1/15/2014
2,1,1/15/2015
3,1,1/15/2017
4,2,3/16/2014
5,2,3/16/2015

I want to return a single row for each asset with the "biggest" warranty date like:
1,Computer1,Notebook Computer,1/15/2017
2,Computer2,Desktop Computer,3/16/2015

Any suggestions on a query structure to generate this? If I can get past the basic setup I'm sure I can customize it to the larger dataset that we're working with.

Thanks in advance.

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/19/2013 :  10:00:47  Show Profile  Reply with Quote
SELECT DISTINCT a.*, MAX(WarrantyExpireDate) OVER(PARTITION BY AssetID ORDER BY WarrantyExpireDate DESC) WarrantyDate
FROM TableAssets a
JOIN TableWarranties b
ON a.AssetID = b.AssetID


--
Chandu
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/19/2013 :  10:07:05  Show Profile  Visit webfred's Homepage  Reply with Quote
if you don't need the WarrantyID then you can do this:

select t1.AssetID, t1.AssetName, t1.AssetInfo, t2.WarrantyExpireDate
from TableAssets as t1
left join
(select AssetID, max(WarrantyExpireDate) as WarrantyExpireDate from TableWarranties group by AssetID) as t2
on t1.AssetID = t2.AssetID


Too old to Rock'n'Roll too young to die.
Go to Top of Page

morleyz
Starting Member

17 Posts

Posted - 03/19/2013 :  10:58:44  Show Profile  Reply with Quote
Thanks webfred, that got me on the right track and I was able to convert that to the real world and get it working.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000