Group: General Forum Members
Points: 4722
Visits: 458
|
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
|
Group: General Forum Members
Points: 140241
Visits: 19649
|
I can't work out what your problem is - there are no UNIONs in your code.
John
|
Group: Administrators
Points: 650727
Visits: 21472
|
|
Group: General Forum Members
Points: 71084
Visits: 18742
|
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.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
|
Group: General Forum Members
Points: 174937
Visits: 22009
|
<<Scratches head>> There are three queries...
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
|
Group: General Forum Members
Points: 4722
Visits: 458
|
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...
|
Group: General Forum Members
Points: 4722
Visits: 458
|
+x<<Scratches head>> There are three queries... Tehe, you are correct, I updated the code. The query I removed is not relevant, my apologies.
|
Group: Administrators
Points: 650727
Visits: 21472
|
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?
Follow me on Twitter: @way0utwest Forum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com
|
Group: General Forum Members
Points: 4722
Visits: 458
|
+xYou'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. .
|
Group: General Forum Members
Points: 71084
Visits: 18742
|
+x+xYou'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.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
|