TERADATA

Database

TERADATA

I have table like this.

CREATE TABLE RUN(RUN_ID   INTEGER,
START_DT VARCHAR(30),
END_DT VARCHAR(30));

INSERT INTO RUN VALUES(1,'24-APR-2014 12:00:00','24-APR-2014 12:05:00');
INSERT INTO RUN VALUES(1,'2014-04-24 12:00:00','2014-04-24 12:05:00');
INSERT INTO RUN VALUES(2,'2014-04-24 12:06:00','2014-04-24 12:11:00');
INSERT INTO RUN VALUES(3,'2014-04-24 12:12:00','2014-04-24 12:17:00');
INSERT INTO RUN VALUES(4,'2014-04-24 12:18:00','2014-04-24 12:23:00');
INSERT INTO RUN VALUES(5,'2014-04-24 12:24:00','2014-04-24 12:29:00');
INSERT INTO RUN VALUES(6,'2014-04-24 12:30:00','2014-04-24 12:35:00');
INSERT INTO RUN VALUES(7,'2014-04-24 12:36:00','2014-04-24 12:41:00');
INSERT INTO RUN VALUES(8,'2014-04-24 12:42:00','2014-04-24 12:47:00');
INSERT INTO RUN VALUES(9,'2014-04-24 12:48:00','2014-04-24 12:53:00');
INSERT INTO RUN VALUES(10,'2014-04-24 12:54:00','2014-04-24 12:59:00');
INSERT INTO RUN VALUES(11,'2014-04-24 13:00:00','2014-04-24 13:05:00');
INSERT INTO RUN VALUES(12,'2014-04-24 13:06:00','2014-04-24 13:11:00');
INSERT INTO RUN VALUES(13,'2014-04-24 13:12:00','2014-04-24 13:17:00');
INSERT INTO RUN VALUES(14,'2014-04-24 13:18:00','2014-04-24 13:23:00');

My Requirement:If i am entering any value of start_dt and end_dt o/p should be only that run_id which lies between that start_dt and end_dt.

suppose i entered '2014-04-24 12:13:00' as start_dt and '2014-04-24 12:52:00' as end_dt then run_id should come 

run_id

3

4

5

6

7

8

9

Regards,

Abhilah kumar

3 REPLIES
N/A

Re: TERADATA

Hi Abhilah,

are those timestamp columns actually VarChars? That's bad.

If they were TIMESTAMPs it would be a simple

WHERE (START_DT, END_DT) OVERLAPS (TIMESTAMP '2014-04-24 12:13:00',TIMESTAMP'2014-04-24 12:52:00')

If they are VarChars and always stored with a format like all but the first row in your example:

WHERE START_DT <= '2014-04-24 12:52:00'  AND END_DT >= '2014-04-24 12:13:00'

If there are multiple formats there's no way...

Re: TERADATA

Hi Dnoeth

Answer that you have given is not perfect.

suppose i entered '2014-04-24 12:52:00' as start_dt  and '2014-04-24 13:13:00' as end_dt then  it is not giving any run_id but it should come like

run_id

9

10

11

12

13

SELECT RUN_ID FROM RUN WHERE START_DT <= '2014-04-24 12:52:00' AND END_DT >= '2014-04-24 13:13:00';

Please provide me right query.

Regards,

abhilash kumar

N/A

Re: TERADATA

Hi Abhilash,

it is the right query :-)

WHERE START_DT <= 'your END date'  AND END_DT >= 'your START date'