Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-11-2013
09:25 AM

11-11-2013
09:25 AM

My data has four columns date, departure, destination and depart time. I have a case statement that groups depart times. It works fine but it does not group departures between 18:31 and 19:00. Time is

Any idea why not? Thank you for your help.

**SEL**

**dept_date,**

depart,

dest,

depart_tm ,

**CASE**

*WHEN depart_tm BETWEEN '00:00:01' AND '00:30:00' THEN '0000AM'*

*WHEN depart_tm BETWEEN '00:31:00' AND '01:00:00' THEN '0030AM'*

*WHEN depart_tm BETWEEN '01:01:01' AND '01:30:00' THEN '0100AM'*

*WHEN depart_tm BETWEEN '01:31:00' AND '02:00:00' THEN '0130AM'*

*WHEN depart_tm BETWEEN '02:01:00'AND '05:00:00' THEN '0200AM'*

*WHEN depart_tm BETWEEN '05:01:00' AND '05:30:00' THEN '0500AM'*

*WHEN depart_tm BETWEEN '05:31:00' AND '06:00:00' THEN '0530AM'*

*WHEN depart_tm BETWEEN '06:01:00' AND '06:30:00' THEN '0600AM'*

*WHEN depart_tm BETWEEN '06:31:00' AND '07:00:00' THEN '0630AM'*

*WHEN depart_tm BETWEEN '07:01:00' AND '07:30:00' THEN '0700AM'*

*WHEN depart_tm BETWEEN '07:31:00' AND '08:00:00' THEN '0730AM'*

*WHEN depart_tm BETWEEN '08:01:00' AND '08:30:00' THEN '0800AM'*

*WHEN depart_tm BETWEEN '08:31:00' AND '09:00:00' THEN '0830AM'*

*WHEN depart_tm BETWEEN '09:01:00' AND '09:30:00' THEN '0900AM'*

*WHEN depart_tm BETWEEN '09:31:00' AND '10:00:00' THEN '0930AM'*

*WHEN depart_tm BETWEEN '10:01:00' AND '10:30:00' THEN '1000AM'*

*WHEN depart_tm BETWEEN '10:31:00' AND '11:00:00' THEN '1030AM'*

*WHEN depart_tm BETWEEN '11:01:00' AND '11:30:00' THEN '1100AM'*

*WHEN depart_tm BETWEEN '11:31:00' AND '12:00:00' THEN '1130AM'*

*WHEN depart_tm BETWEEN '12:01:00' AND '12:30:00' THEN '1200N'*

*WHEN depart_tm BETWEEN '12:31:00' AND '13:00:00' THEN '1230PM'*

*WHEN depart_tm BETWEEN '13:01:00' AND '13:30:00' THEN '0100PM'*

*WHEN depart_tm BETWEEN '13:31:00' AND '14:00:00' THEN '0130PM'*

*WHEN depart_tm BETWEEN '14:01:00' AND '14:30:00' THEN '0200PM'*

*WHEN depart_tm BETWEEN '14:31:00' AND '15:00:00' THEN '0230PM'*

*WHEN depart_tm BETWEEN '15:01:00' AND '15:30:00' THEN '0300PM'*

*WHEN depart_tm BETWEEN '15:31:00' AND '16:00:00' THEN '0330PM'*

*WHEN depart_tm BETWEEN '16:01:00' AND '16:30:00' THEN '0400PM'*

*WHEN depart_tm BETWEEN '16:31:00' AND '17:00:00' THEN '0430PM'*

*WHEN depart_tm BETWEEN '17:01:00' AND '17:30:00' THEN '0500PM'*

*WHEN depart_tm BETWEEN '17:31:00' AND '18:00:00' THEN '0530PM'*

*WHEN depart_tm BETWEEN '18:01:00' AND '18:30:00' THEN '0600PM'*

*WHEN depart_tm BETWEEN '18:31:00' AND '19:00:00' THEN '0630PM'*

*WHEN depart_tm BETWEEN '19:01:00' AND '19:30:00' THEN '0700PM'*

*WHEN depart_tm BETWEEN '19:31:00' AND '20:00:00' THEN '0730PM'*

*WHEN depart_tm BETWEEN '20:01:00'AND '20:30:00' THEN '0800PM'*

*WHEN depart_tm BETWEEN '20:31:00' AND '21:00:00' THEN '0830PM'*

*WHEN depart_tm BETWEEN '21:01:00'AND '21:30:00' THEN '0900PM'*

*WHEN depart_tm BETWEEN '21:31:00' AND '22:00:00' THEN '0930PM'*

*WHEN depart_tm BETWEEN '22:01:00'AND '22:30:00' THEN '1000PM'*

*WHEN depart_tm BETWEEN '22:31:00' AND '23:00:00' THEN '1030PM'*

*WHEN depart_tm BETWEEN '23:01:00'AND '23:30:00' THEN '1100PM'*

*WHEN depart_tm BETWEEN '23:31:00' AND '23:59:00 'THEN '1130PM'*

*END AS timeinterval*

**FROM**

Finallift

EG. output

Depart | Dest | Depart_tm | timeinterval |

MSP | KEF | 18:30:00 | 0600PM |

MSP | AMS | 18:30:00 | 0600PM |

MSP | CLT | 18:34:00 | |

MSP | MSN | 18:34:00 | |

MSP | ORD | 18:35:00 | |

MSP | ALB | 18:35:00 | |

MSP | ATW | 18:35:00 | |

MSP | FNT | 18:35:00 | |

MSP | SBN | 18:35:00 |

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-11-2013
10:34 PM

11-11-2013
10:34 PM

Hi,

How about beyond 18:31 and 19:00?

Next time, if you dont mind, when you paste your result set, please put in a proper format so that it will be easy to see.

Cheers,

Raja

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2014
12:20 PM

01-17-2014
12:20 PM

It pulls everything except the time slot between 18:31 and 19:00.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-19-2014
10:23 AM

01-19-2014
10:23 AM

Is there any extra space or special characters while checking this condition.(which is not visible)

Some times retyping the statment may solve these type of surprising issues. please try.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.