CODEWITHSUNDEEP
sqlsql-server
Sowmiya Raja Radhakrishnan
Sowmiya Raja Radhakrishnan

Reputation: 41

Deriving Dynamic Column in SQL

I have a table like this:

Store_Id | Week 
---------+--------
   A1    | 201601
   A1    | 201602
   A1    | 201604
   A1    | 201606
   A1    | 201607
   A2    | 201552
   A2    | 201603
   A2    | 201604
   A2    | 201605
   A2    | 201608

I need to derive a dynamic week column next to this which will be ideally looking like this:

Store_Id | Week   | Dynamic_Week
---------+--------+-------------
   A1    | 201602 |    1
   A1    | 201603 |    2
   A1    | 201605 |    4
   A1    | 201606 |    5
   A1    | 201607 |    6
   A2    | 201552 |    1
   A2    | 201603 |    4
   A2    | 201604 |    5
   A2    | 201605 |    6
   A2    | 201608 |    9

The logic is: the MIN(Week) for each store is considered as the first week of sales for the corresponding store. The preceding values in the dynamic week will be incremented based on the reference of the first week of sales on each store.

I tried Row_Number(), RANK(), DENSE_RANK(), but couldn't get a solution that's needed. All the three things didn't work.

Can anyone suggest me with possible solution.

Thanks in advance.

Upvotes: 0

Views: 65

Answers (2)

Utsav
Utsav

Reputation: 8093

Edit: Made the query dynamic to handle any set of years, not just 1.

New Restester Sample

select d.*
,((d.week-m.min_w+1)
  -(48*(cast(left(d.week,4) as int) 
       - cast(left(min_w,4) as int) 
       )
   )
 )as dynamic_week
from dynw d
inner join (select store_id,min(week) min_w 
            from dynw 
            group by store_id
           ) m
on d.store_id=m.store_id
order by 1,2

Output for more complex sample

+----------+--------+--------------+
| Store_Id |  Week  | dynamic_week |
+----------+--------+--------------+
| A1       | 201602 |            1 |
| A1       | 201607 |            6 |
| A2       | 201552 |            1 |
| A2       | 201603 |            4 |
| A2       | 201704 |           57 |
| A3       | 201352 |            1 |
| A3       | 201601 |          106 |
+----------+--------+--------------+

Previous: This is what you need. Although your sample data and expected data don't match, Also this solution is only good for store_id spanning across 1 year. For more than that, you have to change the query a bit. But it is good for the sample you have.

Restester Sample

SELECT t1.*,
       CASE
           WHEN (t1.week-t.week1+1)>=52 
           THEN (t1.week-t.week1+1)-48
           ELSE (t1.week-t.week1+1)
       END AS dynamic_week
FROM table1 t1
INNER JOIN
  (SELECT store_id,
          min(week) AS week1
   FROM table1
   GROUP BY store_id) t 
ON t1.store_id=t.store_id

Output for my sample

+----------+--------+--------------+
| Store_Id |  Week  | dynamic_week |
+----------+--------+--------------+
| A1       | 201602 |            1 |
| A1       | 201604 |            3 |
| A1       | 201606 |            5 |
| A1       | 201607 |            6 |
| A2       | 201552 |            1 |
| A2       | 201603 |            4 |
| A2       | 201604 |            5 |
| A2       | 201605 |            6 |
| A2       | 201608 |            9 |
+----------+--------+--------------+

Upvotes: 1

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

select a.storeID,a.week,b.min_week,(a.week-b.min_week+1) as Dynamic_week
from
table1 as a 
join
(select distinct storeId,min(week) as min_week from table1 group by 1) as b
where a.store_id=b.store_id

I am not sure this will work on sql-server, i am not used to sql-server you make this code comapatible for sql-server

Upvotes: 1

Related Questions