CODEWITHSUNDEEP
sqlteradata
Pawan
Pawan

Reputation: 3

How to select next date from table in teradata

I have table containing following info

Quarter_END_DATE | Flag 
2014/31/03       |  N 
2014/30/06       |  Y 
2014/30/09       |  N 
2014/31/12       |  N

and so on....

whenever query will be run it should update the next quarter flag to Y and previous to N

Please provide the solution to implement this.

Upvotes: 0

Views: 393

Answers (3)

dnoeth
dnoeth

Reputation: 60462

UPDATE tab
FROM 
 ( SELECT Quarter_END_DATE, Flag
   FROM tab
   QUALIFY Flag = 'Y'   -- current row with 'Y'
        OR MAX(Flag)    -- next row to be set to 'Y'
           OVER (ORDER BY Quarter_END_DATE
                 ROWS BETWEEN 1 PRECEDING AND  1 PRECEDING) = 'Y'
 ) AS src
SET Flag = CASE WHEN src.Flag = 'Y' THEN 'N' ELSE 'Y' END -- switch the flag
WHERE tab.Quarter_END_DATE = src.Quarter_END_DATE
  AND tab.Flag = src.Flag
;

Upvotes: 1

tning
tning

Reputation: 1251

Update next quarter's flag to Y (assume there is only one Y flag in the table): 

UPDATE [table_name] SET Flag = 'Y'
WHERE Quarter_END_DATE = DATEADD(day, 90, (
   SELECT FIRST(Quarter_END_DATE)
   FROM [table_name]
   WHERE Flag = 'Y'));

update previous quarter's flag to N:

UPDATE [table_name] SET Flag = 'N'
WHERE Quarter_END_DATE = (
   SELECT MIN(Quarter_END_DATE)
   FROM [table_name]
   WHERE Flag = 'Y'));

Upvotes: 1

Walker Farrow
Walker Farrow

Reputation: 3875

Best would be to state what dbms you are operating off of, however if you were doing this in mysql you could:

update [table_name] set flag = 'N'
where flag = 'Y'
;

update [table_name] set flag = 'Y'
where quarter_end_date between now() and dateadd(now(), 90 DAYS);

Upvotes: 0

Related Questions