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.
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 AssetInfoTableWarranties WarrantyID AssetID WarrantyExpireDateExample Data:TableAssets (AssetID, AssetName, AssetInfo)1,Computer1,Notebook Computer2,Computer2,Desktop ComputerTableWarranties (WarrantyID, AssetID, WarrantyExpireDate)1,1,1/15/20142,1,1/15/20153,1,1/15/20174,2,3/16/20145,2,3/16/2015I want to return a single row for each asset with the "biggest" warranty date like:1,Computer1,Notebook Computer,1/15/20172,Computer2,Desktop Computer,3/16/2015Any 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) WarrantyDateFROM TableAssets aJOIN TableWarranties bON a.AssetID = b.AssetID--Chandu |
|
|
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.WarrantyExpireDatefrom TableAssets as t1left 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. |
|
|
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. |
|
|
|
|
|
|
|