Yannis Yannis - 6 months ago 8
SQL Question

sql extremely advanced group query

i have a table where i enter data of a product (SlobID) which is

SELECT TemplateID, SlobID, FromYear, ToYear, MAX(ValidFrom) AS CurrentDate
FROM CommissionsPerTemplate AS CommissionsPerTemplate_1
GROUP BY TemplateID, SlobID, FromYear, ToYear
HAVING (MAX(ValidFrom) <= GETDATE()) AND (TemplateID = 2) AND (SlobID = 743)


the result i get is

TemplateID SlobID (From Year) (To year) Valid date
2 743 1 1 2016-01-01
2 743 1 99 2015-01-01
2 743 2 99 2016-01-01


the difficult part is that we need a query to get the latest version of this product.
The 1st version (1/1/2015) if from year=1 to year=99
but 2nd version created on (1/1/2016) which splitter the years
from year 1 to 1 and from year 2 to 99

so the correct result for me should be the following

TemplateID SlobID (From Year) (To year) Valid date
2 743 1 1 2016-01-01
2 743 2 99 2016-01-01


but i have no idea on how to get this.
perhaps on 1/1/2017 the new version should have from year =1 to year=10 and from year =11 to year =99 or something else.

Answer

not sure if this will work for the rest of the data, but if you use row_number instead of group by and take out the "to year" from partition by it works for your sample data.

select * from 
(
SELECT        TemplateID, SlobID, FromYear, ToYear, ValidFrom As CurrentDate , row_number () over ( partition by TemplateID, SlobID,FromYear  order by ValidFrom desc) rowid
FROM            CommissionsPerTemplate AS CommissionsPerTemplate_1
where  (TemplateID = 2) AND (SlobID = 743)
) as x 
where rowid = 1