Urgent help needed with SQL query

[tranz125]

Hello,

I have the following scenario please help:

i have my source data as::

DEL STATUS TIMESTAMP EVENT

D1 ACT JAN 13 audit
D1 CURE JAN 23 CURRENT



MY TARGET TABLLE MUST HAVE DATA IN THSI FORMAT:

DEL STATUS EFFECTIVE_DATE END_DATE

D1 ACT JAN 13 JAN 23
D1 CURE JAN23 NULL


I am trying to write a SQL to implement it please help me or give your suggestions to implement it.


Thanks

 

[JarlH]

You did never explain how to find out end_date...

Maybe you want something like this?

SELECT del,
status,
timestamp AS effective_date,
CASE WHEN status = 'ACT' THEN
(SELECT timestamp FROM sourcedata
WHERE del = sd.del
AND status = 'CURE')
ELSE NULL END AS end_date
FROM sourcedata AS sd


BTW, TIMESTAMP is a reserved word in ANSI SQL. Consider renaming that column.