CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Jo-Herman Haugholt
Jo-Herman Haugholt

Reputation: 462

Using the value of a column to select another column in SQL

I have an MSSQL 2000 DB with a table I need to perform a query against. It has a column 'STEP' and the columns 'DATE1','DATE2'...'DATE7'. When a particular script runs, it updates the 'DATE$step' column and increments 'STEP' by one (wrapping around if necessary).

I'm trying to create a query which returns the rows where the last date, e.g. 'DATE3' if 'STEP'=4, is older than X days, but I a bit stumped as to how to make this query.

Upvotes: 0

Views: 351

Answers (3)

Simen S
Simen S

Reputation: 3205

 Select MyTable.* from MyTable INNER JOIN
     (Select tableID, CASE STEP
                WHEN 7 THEN DATE6   -- The STEP column increments are done 
                WHEN 6 THEN DATE5   -- *after* updating the date, therefore
                WHEN 5 THEN DATE4   -- the last update date is in Date[STEP-1]
                WHEN 4 THEN DATE3
                WHEN 3 THEN DATE2
                WHEN 2 THEN DATE1
                WHEN 1 THEN DATE7   -- Rollover (special case)
             END AS LastDate
     From MyTable
     -- DATEDIFF Returns the count (signed integer) of the specified 
     --          datepart boundaries crossed. I therefore use seconds
     --          to get predicable results regardless of execution time.
     --          (86400 seconds in a day)
     WHERE DATEDIFF('s',LastDate,getdate()) > 
                    (86400 * @DaysParameter)) as dateResult           
     ON MyTable.tableID = dateResult.tableID

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

select Step, [Date]
from 
  (select 
    Step,
    case Step
      when 1 then Date1
      when 2 then Date2
      when 3 then Date3
      when 4 then Date4
      when 5 then Date5
      when 6 then Date6
      when 7 then Date7
    end as [Date]
  from YourTable) as T
where datediff(d, T.[Date], getdate()) > @XDays

Upvotes: 1

hallie
hallie

Reputation: 2845

Something like this?

SELECT
  [DateColumn]=CASE STEP WHEN 1 THEN DATE1 
WHEN 2 THEN DATE2
WHEN 3 THEN DATE3
ELSE DATE4 END
FROM <Table>

Upvotes: 3

Related Questions