cte help

Question

I am trying to create a cte to extract the very last location that an asset was .  I dont think my logic is quite there

select 
 a.[Serial No_]
,a.[Run Count]
,a.[Serial No_]
,a.[Item No_]
,a.[Common Item No_]
,a.[Item Description]
,a.[Item Description 2]
,'' as LastServiceDate
,'' as Location
,a.[District Dimension]
,a.[Asset Location]
,a.Owner
,e.entType
,'' as LastLocationChange 
,c.TotalRepair
,a.[Net Book Value]
,a.[Acq_ Date]
,a.[Asset Aq_ Value]
,b.UnitPrice as UnitPriceExclTax
,a.[Type]
,a.[Size]
,a.[Classification]
,a.[Class Job]
,a.[Product Group]
from [Company$Asset] a
 
left Join
	(select sil.[Asset Serial No_], SUM([Unit Price]) as UnitPrice 
		from [Company$Sales Invoice Line] sil group by [Asset Serial No_]) b on a.[Serial No_]=b.[Asset Serial No_]
left join		(select jle.[Asset Serial No_], sum([Total Cost (LCY)]) as TotalRepair
					FROM [Company$Job Ledger Entry] jle group by [Asset Serial No_]) c on a.[Serial No_]=c.[Asset Serial No_]
with cte as (
select top 1 Name
from [Company$Job] j
join [Company$Dimension Value] dv on dv.Code=j.[Global Dimension 2 Code]
where j.[Serial No_]=a.[Serial No_]
order by [Completion Date] desc) 

Answer 1

I am not sure I understand your current attempts (BTW, you can not put CTE into the middle of the query), but the general principle is simple. You create your CTE query as normal and add ROW_NUMBER() function there. And then select from CTE where Rn = 1 to get the latest location.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Right, as Naomi states, the cte should be at the start.  So with your query:

;with cte as
(
	Select	columns
			,ROW_NUMBER() OVER(Order By WhateverYouNeedToOrderBy) As rn
	From	Tables
	Join	OtherTables
)
Select	columns
From	cte
Where	rn = 1

Answer 3

Above the CTE i have a set of results returning.  I created the CTE b/c i need to pull all the names from the dimension value table and pick the first one for every asset, i will try and rearrange the query.

I have placed the CTE at the top and rearanged the query to complete.  How can i join the CTE with the below resultset and pull back the last Rn for every Serial number

with cte as (
select j.[Serial No_], Name, ROW_NUMBER() over(order by [Completion Date]desc) as Rn
from [Company$Job] j
join [Company$Dimension Value] dv on dv.Code=j.[Global Dimension 2 Code]
) 
Select Name from cte where Rn=1 



select 
 a.[Serial No_]
,a.[Run Count]
,a.[Legacy Serial No_]
,a.[Item No_]
,a.[Common Item No_]
,a.[Item Description]
,a.[Item Description 2]
,'' as LastServiceDate
,'' as Location
,a.[District Dimension]
,a.[Asset Location]
,a.Owner
,e.entType
,'' as LastLocationChange 
,c.TotalRepair
,a.[Net Book Value]
,a.[Acq_ Date]
,a.[Asset Aq_ Value]
,b.UnitPrice as UnitPriceExclTax
,a.[Type]
,a.[Size]
,a.[Classification]
,a.[Class Job]
,a.[Product Group]


from [Company$Asset] a
 join @english e on e.entNo=a.[Status]
 

 
left Join
	(select sil.[Asset Serial No_], SUM([Unit Price]) as UnitPrice 
		from [Company$Sales Invoice Line] sil group by [Asset Serial No_]) b on a.[Serial No_]=b.[Asset Serial No_]
left join		(select jle.[Asset Serial No_], sum([Total Cost (LCY)]) as TotalRepair
					FROM [Company$Job Ledger Entry] jle group by [Asset Serial No_]) c on a.[Serial No_]=c.[Asset Serial No_]

Answer 4

If you need the other resultset that's fine.  In which case, your cte should omit the "top 1" and implement the ROW_NUMBER() window function, then select from the cte.  This would return the first resultset, then the latest location.