Yannis - 4 months ago 4

SQL Question

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
```

Source (Stackoverflow)

Comments