Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subquery issues/help

Author  Topic 

morleyz
Starting Member

17 Posts

Posted - 2013-03-19 : 09:54:52
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 10:00:47
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-19 : 10:07:05
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 - 2013-03-19 : 10:58:44
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
   

- Advertisement -