SQL Query Return max - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query Return max

Author

Topic

Tanianiania
Starting Member

1 Post

Posted - 2014-01-27 : 23:10:40

Hi all

My SQL skills are fairly basic. Using SQL 2008, although this particular query is in an excel refreshable report. This is what I have so far...



select 
T1.[EQSEQUIPID] as [Equip ID],
T1.[Equipment_Status] as [Status],
T1.[EQS_Serial_Number] as [Serial No],
T2.[EQSEQUIPDESC] as [Description],
T2.[Equipment_Branch] as [Branch],
isnull(T4.[DSCRIPTN], '') as [Manufacturer],
isnull(T5.[DSCRIPTN], '') as [Model],
T6.[Acquisition_Cost] as [Acquisition Cost],
T6.[ACQDATE] as [Acquisition Date],
T7.[NETBOOKVALUE] as [Net Book Value],
T8.[ENDDATE] as [Last Hire Date],
T8.[CUSTNAME] as [Customer Name]

 from FPLL..EQSA0000 T1  with (nolock) 

 LEFT JOIN FPLL..EQSA0240 T2 with (nolock) 
 on 
 T2.[EQSEQUIPINDEX] =  T1.[EQSEQUIPINDEX]

 LEFT JOIN FPLL..EQSA0015 T3 with (nolock) 
 on 
 T3.[EQSEQUIPTYPE] =  T2.[EQSEQUIPTYPE]

 LEFT JOIN FPLL..EQSA0035 T4 with (nolock) 
 on 
 T4.[Equipment_Manufacturer] =  T2.[Equipment_Manufacturer]

 LEFT JOIN FPLL..EQSA0005 T5 with (nolock) 
 on 
 T5.[EQSEQUIPMODEL] =  T1.[EQSEQUIPMODEL]

 LEFT JOIN FPLL..FA00100 T6 with (nolock) 
 on 
 T6.[ASSETID] =  T1.[EQSEQUIPID]

LEFT JOIN FPLL..FA00200 T7 with (nolock) 
 on 
 T7.[ASSETINDEX] = T6.[ASSETINDEX]

LEFT JOIN FPLL..EQSV0040 T8 with (nolock)
on
T8.[EQSEQUIPID] = T1.[EQSEQUIPID]

 where T1.[Equipment_Status] IN ('AVAILABLE', 'R & M')
and T2.[Equipment_Branch] = 'PERTH'
and T7.[BOOKINDX] = '1'

So basically table EQSV0040 T8 has multiple lines per EQSEQUIPID. For the 2 fields I'm querying on this table

T8.[ENDDATE] as [Last Hire Date],
T8.[CUSTNAME] as [Customer Name]

I only want the most recent record returned. There is a sequential field in this table DEX_ROW_ID and I have seen a MAX function but can't get the 2 working together.

Any help appreciated, thanks.
Tania

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:05:16

[code]

select
T1.[EQSEQUIPID] as [Equip ID],
T1.[Equipment_Status] as [Status],
T1.[EQS_Serial_Number] as [Serial No],
T2.[EQSEQUIPDESC] as [Description],
T2.[Equipment_Branch] as [Branch],
isnull(T4.[DSCRIPTN], '') as [Manufacturer],
isnull(T5.[DSCRIPTN], '') as [Model],
T6.[Acquisition_Cost] as [Acquisition Cost],
T6.[ACQDATE] as [Acquisition Date],
T7.[NETBOOKVALUE] as [Net Book Value],
T8.[ENDDATE] as [Last Hire Date],
T8.[CUSTNAME] as [Customer Name]

from FPLL..EQSA0000 T1 with (nolock)

LEFT JOIN FPLL..EQSA0240 T2 with (nolock)
on
T2.[EQSEQUIPINDEX] = T1.[EQSEQUIPINDEX]

LEFT JOIN FPLL..EQSA0015 T3 with (nolock)
on
T3.[EQSEQUIPTYPE] = T2.[EQSEQUIPTYPE]

LEFT JOIN FPLL..EQSA0035 T4 with (nolock)
on
T4.[Equipment_Manufacturer] = T2.[Equipment_Manufacturer]

LEFT JOIN FPLL..EQSA0005 T5 with (nolock)
on
T5.[EQSEQUIPMODEL] = T1.[EQSEQUIPMODEL]

LEFT JOIN FPLL..FA00100 T6 with (nolock)
on
T6.[ASSETID] = T1.[EQSEQUIPID]

LEFT JOIN FPLL..FA00200 T7 with (nolock)
on
T7.[ASSETINDEX] = T6.[ASSETINDEX]

LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EQSEQUIPID ORDER BY DEX_ROW_ID DESC) AS Rn,*
FROM FPLL..EQSV0040 with (nolock))T8
on
T8.[EQSEQUIPID] = T1.[EQSEQUIPID]
AND t8.Rn =1

where T1.[Equipment_Status] IN ('AVAILABLE', 'R & M')
and T2.[Equipment_Branch] = 'PERTH'
and T7.[BOOKINDX] = '1'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources