none
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 
    (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
    FROM NewLeads
    WHERE Lead_Date>='2013-09-01'
    GROUP BY Lead_Date,DATEPART(HH,LeadTime))Interim
    GROUP BY Lead_Date,Time
    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

Answers

  • 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
    FROM NewLeads
    WHERE Lead_Date>='2013-09-01'
    GROUP BY 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
    ORDER BY Lead_Date, MIN(LeadTime)


    Gert-Jan

    • Proposed as answer by Martina White Sunday, September 22, 2013 10:08 PM
    • Marked as answer by HimanshuSharma 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 
    (SELECT	Lead_Date
    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
    FROM NewLeads
    WHERE Lead_Date>='2013-09-01'
    GROUP BY Lead_Date,DATEPART(HH,LeadTime))Interim
    GROUP BY Lead_Date,Timeofsort,time
    ORDER BY Lead_Date,Timeofsort

    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
    FROM NewLeads
    WHERE Lead_Date>='2013-09-01'
    GROUP BY 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
    ORDER BY Lead_Date, MIN(LeadTime)


    Gert-Jan

    • Proposed as answer by Martina White Sunday, September 22, 2013 10:08 PM
    • Marked as answer by HimanshuSharma 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 
    (select dateadd(hh,3,getdate()) lead_date union all
    select dateadd(hh,4,getdate()) lead_date union all
    select dateadd(hh,6,getdate()) lead_date union all
    select dateadd(hh,9,getdate()) lead_date union all
    select dateadd(hh,12,getdate()) lead_date union all
    select dateadd(hh,15,getdate()) lead_date union all
    select dateadd(hh,18,getdate()) lead_date union all
    select dateadd(hh,21,getdate()) lead_date union all
    select dateadd(hh,24,getdate()) lead_date) a
    
    
    
    
    SELECT	convert(varchar(10),lead_date,121) date, 
    
    
     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
    FROM #NewLead
    
    GROUP BY convert(varchar(10),lead_date,121),
    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
     JOIN #NewLead ON lead_date >= intervals.interval AND lead_date < DATEADD(HH, 3, intervals.interval)
    	
    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