For correct representation of data in our datawarehouse we want to extract data from our database to the datawarehouse.
But now I'm some sort of stuck because can't find the cause for the case below.
We have a season structure which looks like
level 1 - 2020
level 2 - S21
level 3 - S21LAM
level 2 - X21
level 3 - X21BAL
During the creation of this group we've made a typo. So level 1 was called 2019 (Name and Identifier). We've changed the Name and Identifier to 2020 but now I'm not able to compose the correct query to retrieve the correct version.
SELECT sgr.Identifier as "Code", sgl.Name as "Name", sgd.ParentIdentifier as "Parent"
FROM [HPM_MAIN].[dbo].[StructureGroupDetail] sgd
, [HPM_MAIN].[dbo].[StructureGroupRevision] sgr
, [HPM_MAIN].[dbo].[StructureGroupLang] sgl
, [HPM_MAIN].[dbo].[Language] lang
where sgr.StructureID = (select max(StructureID)
where Identifier = 'Seasons')
and sgr.RevisionID = (select max(sgrr.RevisionID)
FROM [HPM_MAIN].[dbo].[StructureGroupRevision] sgrr
where sgrr.Identifier = sgr.Identifier
and sgd.StructureGroupRevisionID = sgr.ID
and sgl.StructureGroupRevisionID = sgr.ID
and sgl.LanguageID = lang.ID
and lang.Alpha3Code = 'eng'
and lang.Locale = 'en'
I think we are missing something in the bold section. Because it returns 2019 in stead of 2020. But based on the data I can't find the distinctive part of the data.
Hope someone can help.