CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Shade
Shade

Reputation: 191

SQL: How to select the most recent of versioned items

In our system, items gets different versions depending on from when they're available. E.g. an Item 'Tool 1' has a version which is available for the following dates:

Therefore, the table 'Tools' I have in the DB is

ItemCode   |    ParentCode   |    EffectiveFrom    
1          |    1            |    01.01.2015
2          |    1            |    01.01.2016
3          |    1            |    01.02.2016
4          |    1            |    01.01.2016

Now, Mgmt wants to know which items are being sold and which one are coming soon. ParentCode refers to the first version of the item.

So, they'd need ItemCodes 2 (is being sold currently) and 3,4 (coming soon). Can't find a way for a SELECT to get those.

Working with 'Microsoft SQL Server Management Studio 2012'.

Thanks for your help.

Regards

Upvotes: 3

Views: 86

Answers (2)

Vincent G
Vincent G

Reputation: 361

This answer might not be the one you want, but you can try this :

SELECT * 
FROM Tools 
WHERE EffectiveForm > CONVERT(NVARCHAR(10),GETDATE(),104)
OR (MONTH(EffectiveForm) = MONTH(GETDATE()) 
    YEAR(EffectiveForm) = YEAR(GETDATE()))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think this is just a comparison on the current date along with information about the next date.

It would be better to have the end date directly in the table, but in SQL Server 2012+ it is readily calculated using lead():

select t.*,
       (case when effectivefrom >= getdate() then 'Coming Soon'
             else 'Currently Sold'
        end) as timingStatus
from (select t.*,
             lead(effectivefrom) over (partition by parentcode order by effectivefrom) as next_effectivefrom
      from tools t
     ) t
where next_effectivefrom is null or next_effectivefrom >= getdate();

Note: This uses getdate() which has a time component. Your dates probably don't. So, I think the following is slightly more accurate:

select t.*,
       (case when effectivefrom > cast(getdate() as date) then 'Coming Soon'
             else 'Currently Sold'
        end) as timingStatus
from (select t.*,
             lead(effectivefrom) over (partition by parentcode order by effectivefrom) as next_effectivefrom
      from tools t
     ) t
where next_effectivefrom is null or
      next_effectivefrom > cast(getdate() as date);

Upvotes: 2

Related Questions