I have table with sales transaction data returning the transaction date for each sales transaction.

NB: Transaction Date is in INT format in the database i am working from

ORIGINAL TABLE

In a separate table I have a selling season and the date range in which the selling season can be applied to a sales transaction (below is just sample data not actual table data just to demonstrate what the table will look like).

SEASON TABLE

The desired outcome would be in the SEASON column on the original table to pick up which season is required based on the transaction date and if the transaction falls between the FROM and TO dates in the season table

UPDATES ORIGINAL TABLE

I have no idea where to start but i did find something and was wondering if this would be the most optimized way to look at the required statement or is there something better that would work.

DECLARE @TRANSACTIONDATE AS DATETIME


SELECT CASE 
    WHEN @TRANSACTIONDATE  BETWEEN '2017-01-01' AND '2017-03-31' THEN 'SS22'
    WHEN @TRANSACTIONDATE  BETWEEN '2017-04-01' AND '2017-06-31' THEN 'FW21'
    WHEN @TRANSACTIONDATE  BETWEEN '2017-07-01' AND '2017-09-31' THEN 'SS20'
    WHEN @TRANSACTIONDATE  BETWEEN '2017-10-01' AND '2018-12-31' THEN 'HO21'
    ELSE 'FW21?'
    END AS SEASON

CodePudding user response：

To update a column in the same table you can use a SQL UPDATE with a SELECT subquery to obtain the Season column value (from the [Season_Table] lookup table), which is then updated into the Season column in the original transaction table [Transaction_Table]:

  UPDATE [Transaction_Table]
  SET [Season] = (SELECT Season FROM [Season_Table] s
                  WHERE s.From_Date <= [Transaction_Table].TransactionDate AND 
                        s.To_Date >= [Transaction_Table].TransactionDate)

This avoids having to use a CASE statement.

If using SQL server T-SQL there are many useful examples and syntax describing SELECT UPDATE at https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15