SQL add Order column that represents Day of the week

0
votes

Working a 3 month dataset that has a record/line for each day in that time frame. I want to add a "Order" column that possesses values of 0-6 or 1-7. Where, for example, if query executed on Monday - Mondays would be 0, Tuesdays: 1, Wednesdays: 2, Thursdays: 3, Fridays:4, Saturdays: 5, Sundays: 6. If the query was executed on a Friday then Friday records would contain: 0, Saturday: 1, Sunday: 2, etc...

Ultimate goal is to use this query in a PBI dashboard - where, after aggregating by day of week - the current day is shown first when sorted by the order column; i.e. I don't want the results always showing Sunday through Saturday. If it is Wednesday and someone is looking at the dashboard, I'll want the order of data to be W, Th, F, S, Su, M, T. I believe this can be accomplished with this order column.

Any ideas? Thank you!

2
sqloraclepowerbi

2 Answers

1
votes

You want the day of the week based on the current day. This is really an arithmetic calculation, where you want the difference between the particular date and the current date mod 7, but with a bit more arithmetic.

This does the calculation:

select 6 - mod(trunc(sysdate - 1) - <datecol>, 7)
0
votes

To compute a relative "day" you can do:

mod(to_char(t.recorded, 'D') + 7 - to_char(current_date, 'D'), 7)

For example:

select
  t.*,
  mod(to_char(t.recorded, 'D') + 7 - to_char(current_date, 'D'), 7) as diff
from t;

Result (today is Monday):

 RECORDED   VALUE  DIFF 
 ---------- ------ ---- 
 2021-01-01  123    4    
 2021-01-02  456    5    
 2021-01-03  789    6    
 2021-01-04  12     0    
 2021-01-05  345    1    
 2021-01-06  678    2    
 2021-01-07  901    3    
 2021-01-08  234    4    
 2021-01-09  567    5

See running example at SQL Fiddle.