Post reply

Sort Data by Time but start at specific time

  • December 22, 2010 at 4:19 pm

    #242670

    Hi,

    I have a table which contains 24 hours worth of events for a TV channel (running from 11:30:00:00 to 11:30:00:00 the next day) with each row starting at a certain time in hh:mm:ss:ff format. I am exporting the data to a text file in SSIS which is fine. However I need to sort the data on StartTime but starting at 11:30:00:00 and ascending to 11:30:00:00 the next day..

    My query is:

    SELECT MaterialID

    , StartTime,' ' As Blank1

    , EventTitle

    , Duration

    ,' ' As Blank2

    , SecondaryEvents, EventID

    ,' ' AS Blank3

    , EventType

    , ' ' As Blank4

    , ClipTag

    , ' ' AS Blank5

    , Offset

    FROM dbo.Star_RunningOrders

    ORDER BY StartTime

    This returns the data I need but sorts the data from 00:00:00:00 to 00:00:00:00 the next day.

    This has me stumped.

    I was thinking of trying a UNION but that would just sort everything at the end anyways.

    I need to start on row 11:30:00:00, sort ascending to 00:00:00:00 and then append 00:00:00:00 to 11:30:00:00 therefore giving me the 24 hrs of data from 11:30:00:00 -> 11:30:00:00.

    Any help greatly appreciated!

    JayK

  • December 22, 2010 at 4:22 pm

    #1265738

    What's the data type of StartTime?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 22, 2010 at 4:26 pm

    #1265740

    Its an nchar(11)...

  • December 22, 2010 at 4:56 pm

    #1265745

    Here's one version to define the order:

    ORDER BY CASE WHEN cast('19000101' + ' ' +StartTime as datetime) >'19000101 11:30:00:00' THEN 0 ELSE 1 END, StartTime

    But I strongly recommend to rethink the data typ of StartTime. Since you're obviously on SS2K8 (based on the forum you posted in), the best data type would be TIME. Below SS2K8 it would be DATETIME. CHAR(11) would be in a category "definitely to be improved" but NCHAR(11) is in the category of "never, ever". Reason: the time part f a datetime value does not include any specific character not covered by CHAR. Therefore, the additional space is not justified.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 22, 2010 at 5:28 pm

    #1265748

    Hi LutzM - Ok - I take on board the comment about nchar - I'm actually in 2008 now but will need to deploy this to 2005 so could use datetime....

    Your code worked perfectly - never seen a CASE in an ORDER BY clause before so you've saved me a lot of headscratching - thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply