CODEWITHSUNDEEP
snowflake-cloud-data-platform
Scout75
Scout75

Reputation: 11

Values on the frist day of the week

I have a table in snowflake like below

StockName    Date          Value

GOOG         10/12/2021     $100
GOOG          10/11/2021     $995

Now i want to create a new column which would give me results that would show the value of the stock on weeks first day which would be monday or the next day if monday is a holiday

StockName         date          Value        value_weekstart
GOOG              10/12/2021    $1000         $995
GOOG              10/11/2021    $995          $995

Upvotes: 0

Views: 192

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

The following should work:

SELECT 
    stockname,
    date,
    value,
    FIRST_VALUE(value) OVER (PARTITON BY stockname, datetrunc('week',date) ORDER BY dayofweekiso(date)) as value_weekstart
FROM table_name
ORDER BY 1,2;

that will return the "sunday price" if there is one, but will side step the possible jump if you use the stock dayofweek and someone changes the setting on your instance.

If you really want sunday to be last going to (dayofweekiso(date)+6)%7 will roll it around to last position.

Upvotes: 2

Related Questions