# Order By Time Range (in VARCHAR)

### Question

• Hi All,

I am stuck in a sorting issue.

Here is the Code

```SELECT Lead_Date,TimeRange,COUNT(*) AS Cnt
FROM
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END AS Time
,COUNT(* ) AS CNT
ORDER BY 1,2```
Now I want data to be sorted first with date, then with the TimeRange in a proper manner.

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.

Saturday, September 21, 2013 8:47 AM

• Since the result of the CASE expression does not sort the way you want, referring to the output column for sorting ("2") does not work.

In the example solution below, I have chosen MIN(LeadTime) for the sorting order.

```SELECT	Lead_Date
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END AS Time
,COUNT(* ) AS CNT
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END

Gert-Jan

• Proposed as answer by Sunday, September 22, 2013 10:08 PM
• Marked as answer by Friday, October 04, 2013 8:22 AM
Saturday, September 21, 2013 9:46 AM

### All replies

• A Slight catch, I dont want to write another SubQuery on top of it.

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.

Saturday, September 21, 2013 8:52 AM
• PFB code

```SELECT Lead_Date,TimeRange,COUNT(*) AS Cnt
FROM
CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN 1
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN 2
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN 3
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN 4
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN 5
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN 6
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN 7
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN 8
END AS Timeofsort
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END AS Time
,COUNT(* ) AS CNT

Thanks

Saravana Kumar C

Saturday, September 21, 2013 9:11 AM
• Don't specify the column position in ORDER BY clause , it is not a best practice.

>>>Now I want data to be sorted first with date, then with the TimeRange in a proper manner.

What do you mean by proper manner?

Are those two columns are Date and Time types?

But clearly the Time is a string literal, the sorting may not be same as TIME data type.

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Saturday, September 21, 2013 9:43 AM
• Since the result of the CASE expression does not sort the way you want, referring to the output column for sorting ("2") does not work.

In the example solution below, I have chosen MIN(LeadTime) for the sorting order.

```SELECT	Lead_Date
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END AS Time
,COUNT(* ) AS CNT
,CASE	WHEN DATEPART(HH,LeadTime) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,LeadTime) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,LeadTime) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END

Gert-Jan

• Proposed as answer by Sunday, September 22, 2013 10:08 PM
• Marked as answer by Friday, October 04, 2013 8:22 AM
Saturday, September 21, 2013 9:46 AM
• Hi Gert and Saravana,

Thanks 4 replying. Foremost, I dont need an outer query for this, Please suggest if there is any other option.

Hi Sarat,

Thanks 4 reminding me that (Actually this is not the original code, I wrote this in a jiffy so just used the ordinal_position for sorting.)

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.

Saturday, September 21, 2013 10:04 AM
• In case if you dont want to go with Sub query and even the sorting with VARCHAR type will also be not as expected.

Can you put all the timings in a table in the order needed and have sequence number along with it, then join with that table and sort with that sequence column value (INT).

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Saturday, September 21, 2013 2:01 PM
• I suggested a query in my previous reply. Did you try it?

Gert-Jan

Saturday, September 21, 2013 4:40 PM
• Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

We do not use AM/PM in programming, not just SQL. I really hate those range names; it tells the world that you do not know one of the basic data types in this profession. Since you also did not post DDL, I will guess that you have DATETIME2(0) columns.

Use a table of time slots set to one more decimal second of precision than your data. You can now use temporal math to add it to a DATE to TIME(1) get a full DATETIME2(0). Here is the basic skeleton.

CREATE TABLE Timeslots
(slot_name CHAR(15) NOT NULL,
slot_start_time TIME(1) NOT NULL PRIMARY KEY,
slot_end_time TIME(1) NOT NULL,
CHECK (start_time < end_time));

INSERT INTO Timeslots
VALUES ('12 AM TO 03 AM', '00:00:00.0', '02:59:59.9'),
('03 AM TO 06 AM', '03:00:00.0', '05:59:59.9'),
etc;

SELECT X.lead_date, X.slot_name, T.slot_start_time, COUNT(*)
FROM (SELECT CAST (lead_date AS DATE) AS lead_date,
T.slot_name, T.slot_start_time,
COUNT(*) AS something_cnt
FROM Timeslots AS T, Leads AS L
WHERE CAST (lead_date AS TIME)
BETWEEN T.slot_start_time
AND T.slot_end_time
AND lead_date >= '2013-09-01')
AS X
GROUP BY X.lead_date, X.slot_name, X.slot_start_time;
ORDER BY X.lead_date, X.slot_start_time;

Untested.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, September 21, 2013 5:37 PM
• Hi Himanshu,

please let us know why you dont want derived table, i think derived table will not degrade the performance in this case, this is just grouping the result from derived table, any way as you ask, PFB code, but this may case some extra CPU time for the string match,

Code1 -

```select *,1 as Leads into #NewLead from

case
when substring(convert(varchar(13),lead_date,121),12,2) in('00','01','02') then '12 AM TO 3 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('03','04','05') then '3 AM TO 6 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('06','07','08') then '6 AM TO 9 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('09','10','11') then '9 AM TO 12 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('12','13','14') then '12 PM TO 3 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('15','16','17') then '3 PM TO 6 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('18','19','20') then '6 PM TO 9 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('21','22','24') then '9 PM TO 12 AM'
else '' end time,

case
when substring(convert(varchar(13),lead_date,121),12,2) in('00','01','02') then 1
when substring(convert(varchar(13),lead_date,121),12,2) in('03','04','05') then 2
when substring(convert(varchar(13),lead_date,121),12,2) in('06','07','08') then 3
when substring(convert(varchar(13),lead_date,121),12,2) in('09','10','11') then 4
when substring(convert(varchar(13),lead_date,121),12,2) in('12','13','14') then 5
when substring(convert(varchar(13),lead_date,121),12,2) in('15','16','17') then 6
when substring(convert(varchar(13),lead_date,121),12,2) in('18','19','20') then 7
when substring(convert(varchar(13),lead_date,121),12,2) in('21','22','24') then 8
else 0 end timeforsort

,COUNT(* ) AS CNT

case
when substring(convert(varchar(13),lead_date,121),12,2) in('00','01','02') then '12 AM TO 3 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('03','04','05') then '3 AM TO 6 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('06','07','08') then '6 AM TO 9 AM'
when substring(convert(varchar(13),lead_date,121),12,2) in('09','10','11') then '9 AM TO 12 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('12','13','14') then '12 PM TO 3 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('15','16','17') then '3 PM TO 6 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('18','19','20') then '6 PM TO 9 PM'
when substring(convert(varchar(13),lead_date,121),12,2) in('21','22','24') then '9 PM TO 12 AM'
else '' end,

case
when substring(convert(varchar(13),lead_date,121),12,2) in('00','01','02') then 1
when substring(convert(varchar(13),lead_date,121),12,2) in('03','04','05') then 2
when substring(convert(varchar(13),lead_date,121),12,2) in('06','07','08') then 3
when substring(convert(varchar(13),lead_date,121),12,2) in('09','10','11') then 4
when substring(convert(varchar(13),lead_date,121),12,2) in('12','13','14') then 5
when substring(convert(varchar(13),lead_date,121),12,2) in('15','16','17') then 6
when substring(convert(varchar(13),lead_date,121),12,2) in('18','19','20') then 7
when substring(convert(varchar(13),lead_date,121),12,2) in('21','22','24') then 8
else 0 end
order by date,timeforsort```

Code 2 - by matching the BASE table with Search table(intervel - created by CTE)

```DECLARE
@StartTime datetime = '2013-09-01 00:00'
,@EndTime datetime = getdate()
,@Intervals int;
SET @Intervals = (DATEDIFF(HH, @StartTime, @EndTime) / 3) + 1;

WITH
t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c)
,t64K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) - 1 AS num FROM t256 AS a CROSS JOIN t256 AS b)
,intervals AS (
SELECT DATEADD(HH, num * 3, @StartTime) AS Interval,convert(varchar(10),DATEADD(HH, num * 3, @StartTime),121) as date,

CASE WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 0 AND 3 THEN '12 AM TO 3 AM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 3 AND 6 THEN '3 AM TO 6 AM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 6 AND 9 THEN '6 AM TO 9 AM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 9 AND 12 THEN '9 AM TO 12 PM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 12 AND 15 THEN '12 PM TO 3 PM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 15 AND 18 THEN '3 PM TO 6 PM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 18 AND 21 THEN '6 PM TO 9 PM'
WHEN DATEPART(HH,DATEADD(HH, num * 3, @StartTime)) BETWEEN 21 AND 25 THEN '9 PM TO 12 AM'
END AS Time
FROM t64K WHERE num < @Intervals)
SELECT
Interval,date,Time
,COUNT(1) as CNT
FROM intervals

GROUP BY intervals.interval,Time,date
ORDER BY intervals.interval;```

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you

Thanks

Saravana Kumar C

Saturday, September 21, 2013 5:57 PM
• Thanks all 4 replying to the post,

Well let me be clear for why I dont want a subquery. My data is huge. It is in millions. So When I write a subquery it affects the performance. I have already tried all the above approaches and checked the execution plan. My statistics are not showing any good results.

I just wanted if there is any solution where we can Identify or SQL engine is intelligent enough to identify the order of occurance of Time Ranges.

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.

Monday, September 23, 2013 7:21 AM