CODEWITHSUNDEEP
sql-server
akd
akd

Reputation: 6758

Group By and inner join with latest records based on TimeStamp

I have a History table as below:

ID | GroupCode | Category | TimeStamp
---+-----------+----------+-----------
1  | x         | shoes    | 2016-09-01
2  | y         | blach    | 2016-09-01

History table gets updated every month and a single entry for each GroupCode gets inserted in the table.

I have also a Current table which holds the latest position.

Before or after I update the History table with the current position I would like to find out whether the Category has changed from last month to this month.

I need to compare the last Category with the current Category and, if it has changed, then flag the CategoryChanged in the Current table.

Current table:

ID | GroupCode | Category | CategoryChanged
---+-----------+----------+----------------
1  | x         | shoes    | True       
2  | y         | blah     | False

I tried to achieve this with INNER JOIN but I am having difficulties to INNER JOIN to latest month and year entries in History table, but no success.

Upvotes: 1

Views: 83

Answers (2)

Cato
Cato

Reputation: 3701

if you make a CTE where the history records have rown_numbwer for each GroupCode ordered by date descending, then you are interested in rows 1 AND 2, SO YOU CAN THEREFORE join your CTE on GroupCode, and select records 1 and 2, you can the see if category has changed between rows 1 and 2

    ;WITH CTE AS (SELECT *, row_number() OVER (PARTITION BY GroupCode ORDER BY TimeStamp Desc) RN FROM History)
    SELECT 
            C1.ID, 
            C1.GroupCode, 
            C1.Category, 
            CASE WHEN C1.Category = C2.Category THEN 
                'false' 
            else 
                'true' 
            end AS CategoryChanged

                FROM CTE C1 
                        JOIN 
                            CTE C2 
                                ON C1.GroupCode = C2.GroupCode 
                                    AND C1.Rn=1 AND C2.RN = 2;

if you have null categories, you can avoid with - BTW you will need to learn how to handle NULLs the way you want to handle them - you can't expect people to post on here thinking about NULLs you never mentioned forever! And happening to realise what you want to do with them for that matter

;WITH CTE AS (SELECT *, row_number() OVER (PARTITION BY GroupCode ORDER BY TimeStamp Desc) RN FROM History)
    SELECT 
            C1.ID, 
            C1.GroupCode, 
            C1.Category, 
            CASE WHEN C1.Category = C2.Category OR C1.Category IS NULL AND C2.Category IS NULL THEN 
                'false' 
            else 
                'true' 
            end AS CategoryChanged

                FROM CTE C1 
                        JOIN 
                            CTE C2 
                                ON C1.GroupCode = C2.GroupCode 
                                    AND C1.Rn=1 AND C2.RN = 2;

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

--get highest group code based on timestamp   
 ;with History
    as
    (select top 1 with ties groupcode,category
    from
    history
    order by 
    row_number() over (partition by group code order by timestamp desc) as rownum
    )

--now do a left join with current table
    select 
    ct.ID,
    ct.GroupCode,
    ct.Category,
    case when ct.category=ht.category or ht.category is null then 'False'
         else 'true'
    end as 'changed'
     from
    currenttable ct
    left join
    history ht
    on ht.groupcode=ct.groupcode

use below to update ,after checking if your select values are correct..

update ct
set ct.category=
 case when ct.category=ht.category or ht.category is null then 'False'
             else 'true'
        end 
         from
        currenttable ct
        left join
        history ht
        on ht.groupcode=ct.groupcode

Upvotes: 1

Related Questions