Looking to sort two queries by Date, Time

  • chef423

    SSCarpal Tunnel

    Points: 4920

    SELECT
    LTRIM(RIGHT(CONVERT(varchar(6), [ie].[Invoice_Number],100),7)) AS [Invoice#]
    –, FORMAT([ie].[DateTime], ‘MM-dd-yyyy’)       AS [Void_Date]
    , CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),’/’,’-‘)) AS [Void_Date]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[DateTime],100),7))  AS [Void_Time]
    , [ie].[ItemNum]                AS [Item_#_Removed]
    , [inv].[ItemName]
    , ‘$’ + CONVERT(VARCHAR(18), CAST([ie].[Amount] AS MONEY))  AS [Amount]
    , CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT))    AS [Quantity]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[Reason_Code],100),14)) AS [Reason_Code]
    FROM
    Invoice_Exceptions AS [ie]
    JOIN inventory AS [inv]
      ON [ie].[ItemNum] = [inv].[ItemNum]
      AND [ie].[DateTime] >= @Yesterday AND [ie].[DateTime] < @PeriodEnd
    ORDER BY DateTime ASC

    AND


    SELECT
    CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [E].Exception_DateTime,101),’/’,’-‘)) AS [Date],
    LTRIM(RIGHT(CONVERT(varchar(20), [E].Exception_DateTime,100),7))  AS [Time],
    CONVERT(VARCHAR(10), CAST([E].Cashier_ID AS INT)) AS [Cashier_ID],
    LTRIM(RIGHT(CONVERT(varchar(25), [P].shortDescription,100),25)) AS [Description],
    LTRIM(RIGHT(CONVERT(varchar(50), [E].Reason_Code,100),50)) AS [Reason]
    FROM
    Exceptions AS [E]
    JOIN Permissions AS [P]
    ON [P].PermissionID = [E].Exception_Type and Exception_Type IN (20,21,36)
        AND [E].Exception_DateTime >= @Yesterday AND [E].Exception_DateTime < @PeriodEnd
    ORDER BY Exception_DateTime ASC

    I understand with a UNION the queries need to be similar with the same lines of code. The issue for me it sorting by 2 different Date and Time (DateTime, Exception_DateTime)

    Thank you in advance for any assistance

  • John Mitchell-245523

    SSC Guru

    Points: 148192

    I can’t work out what your problem is – there are no UNIONs in your code.

    John

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713651

    I assume you mean that you want to UNION these two queries. The columns need to be the same number and data types must be compatible: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql

    In terms of sorting, what is the issue?

  • drew.allen

    SSC Guru

    Points: 76386

    And there is only one sort on the entire UNION.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA


    How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

  • ChrisM@Work

    SSC Guru

    Points: 186019

    <<Scratches head>>
    There are three queries…

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • chef423

    SSCarpal Tunnel

    Points: 4920

    Steve Jones - SSC Editor - Tuesday, January 30, 2018 8:58 AM

    I assume you mean that you want to UNION these two queries. The columns need to be the same number and data types must be compatible: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql

    In terms of sorting, what is the issue?

    Sorting by DateTime and Exception_DateTime

    So if the 1st query returns a value on 1-29-2018 @ 8:00AM and then the 2nd query returns a value @ 8:02AM and then back to the 1st and so forth…

  • chef423

    SSCarpal Tunnel

    Points: 4920

    ChrisM@Work - Tuesday, January 30, 2018 9:25 AM

    <<Scratches head>>
    There are three queries...

    Tehe, you are correct, I updated the code. The query I removed is not relevant, my apologies.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713651

    You’re not really explaining the issue you are having. I get that you want a sort order among the queries, but what don’t you understand or what doesn’t work?

  • chef423

    SSCarpal Tunnel

    Points: 4920

    Steve Jones - SSC Editor - Tuesday, January 30, 2018 9:40 AM

    You're not really explaining the issue you are having. I get that you want a sort order among the queries, but what don't you understand or what doesn't work?

    Attempting to ORDER BY DateTime and ORDER BY Exception_DateTime (so both queries sort into one table by Date then Time)

    I apologize for not being more clear. I need both of these queries to place the data into one output and sort the data by Date then Time.

    .

  • drew.allen

    SSC Guru

    Points: 76386

    chef423 - Tuesday, January 30, 2018 9:46 AM

    Steve Jones - SSC Editor - Tuesday, January 30, 2018 9:40 AM

    You're not really explaining the issue you are having. I get that you want a sort order among the queries, but what don't you understand or what doesn't work?

    Attempting to ORDER BY DateTime and ORDER BY Exception_DateTime (so both queries sort into one table by Date then Time)

    I apologize for not being more clear. I need both of these queries to place the data into one output and sort the data by Date then Time.

    .

    This is exactly why we ask for sample data and expected results.  It provides very clear starting and end points.  There are links in my signature about how to do so.

    Also, sorting by date then time is easy.  All you have to do is sort on a datetime field.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA


    How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

  • chef423

    SSCarpal Tunnel

    Points: 4920

    Steve Jones - SSC Editor - Tuesday, January 30, 2018 8:58 AM

    I assume you mean that you want to UNION these two queries. The columns need to be the same number and data types must be compatible: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql

    In terms of sorting, what is the issue?

    Hi Steve, I apologize for not being more clear. I assume I need a 4 table join then to get all the data into one output. But still the issue is sorting by Date and Time.

    Hoping you can assist. Thanks.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    as a side issue what is the datatype for [ie].[DateTime]
    am just wondering what this bit of code is for?
    CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),’/’,’-‘)) AS [Void_Date]

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • chef423

    SSCarpal Tunnel

    Points: 4920

    J Livingston SQL - Tuesday, January 30, 2018 11:48 AM

    as a side issue what is the datatype for [ie].[DateTime]
    am just wondering what this bit of code is for?
    CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Void_Date]

    For this time format: 2017-12-15 22:44:09.000

    The formatting makes it readable for the end user reading this simple report.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713651

    The main problem I see is that you different numbers of columns in each query. You need the same number of items in the select list

    select  a, b, c
    from tablea
    union
    select d, e, f
     from tableb

    That will work. This will not:


    select  a, b, c
    from tablea
    union
    select d, e
     from tableb

    If you need the order by, add it to the end of the  final query, like this:
    select  a, b, c
    from tablea
    union
    select a, b, c
     from tableb
    order by  e, f

  • chef423

    SSCarpal Tunnel

    Points: 4920

    Steve Jones - SSC Editor - Tuesday, January 30, 2018 1:25 PM

    The main problem I see is that you different numbers of columns in each query. You need the same number of items in the select list

    select  a, b, c
    from tablea
    union
    select d, e, f
     from tableb

    That will work. This will not:


    select  a, b, c
    from tablea
    union
    select d, e
     from tableb

    If you need the order by, add it to the end of the  final query, like this:
    select  a, b, c
    from tablea
    union
    select a, b, c
     from tableb
    order by  e, f

    So that is the only way to accomplish this joining? Cant i just do a 4 table join?

Viewing 15 posts - 1 through 15 (of 27 total)

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