March 28, 2014 at 4:42 am
In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:
Document Number
Version
Occurrence number
so if doc 1 is archived, then the records would be
Doc# | Version | Occurrence #
1 | 1 | 1
When a second copy is archived a new record is added:
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
and then when maybe a 3rd version is archived a 3rd entry added
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2
This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!
If I
select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]
then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.
How do I select just these?
I thought (wrongly) that I could simply say:
Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])
Can anyone help?
March 28, 2014 at 5:20 am
I prefer to use Row_Number().
with cte as
(Select *
, row_number() over (Partition by [Doc#] order by Version Desc, [Occurrence #] Desc)
as RowNum from Invoice Table
)
select * from cte
where RowNum = 1;
Russel Loski, MCSE Business Intelligence, Data Platform
March 28, 2014 at 5:31 am
Thanks for the reply - I've worked it out. Little did I know, you can actually join a query to a query to pull out the data!
The query that gives me the distinct list of possible values is:
Select [DocNo],Max([Doc_ No_ Occurrence]) as Occur, Max([Version No_]) as Vers from [DW_Sales Header Archive] group by [DocNo])
therefore if I join a select query to this to select all the row data where those three criteria match, then I get the dataset I am looking for:
Select a.*
from [DW_Sales Header Archive] as a
inner join (
Select [DocNo],Max([Doc_ No_ Occurrence]) as Occur, Max([Version No_]) as Vers from [DW_Sales Header Archive] group by [DocNo]) as q
on a.DocNo = q.DocNo
and a.[Doc_ No_ Occurrence] = q.Occur and a.[Version No_]=q.Vers
Once again, thanks for the reply, but I found the answer myself 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply