January 30, 2018 at 8:29 am
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
January 30, 2018 at 8:47 am
I can't work out what your problem is - there are no UNIONs in your code.
John
January 30, 2018 at 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?
January 30, 2018 at 9:12 am
And there is only one sort on the entire UNION.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2018 at 9:25 am
<<Scratches head>>
There are three queries...
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 Moden
January 30, 2018 at 9:31 am
Steve Jones - SSC Editor - Tuesday, January 30, 2018 8:58 AMI 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-sqlIn 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...
January 30, 2018 at 9:34 am
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.
January 30, 2018 at 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?
January 30, 2018 at 9:46 am
Steve Jones - SSC Editor - Tuesday, January 30, 2018 9:40 AMYou'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.
.
January 30, 2018 at 11:24 am
chef423 - Tuesday, January 30, 2018 9:46 AMSteve Jones - SSC Editor - Tuesday, January 30, 2018 9:40 AMYou'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
January 30, 2018 at 11:25 am
Steve Jones - SSC Editor - Tuesday, January 30, 2018 8:58 AMI 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-sqlIn 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.
January 30, 2018 at 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]
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2018 at 12:02 pm
J Livingston SQL - Tuesday, January 30, 2018 11:48 AMas 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.
January 30, 2018 at 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
January 30, 2018 at 2:13 pm
Steve Jones - SSC Editor - Tuesday, January 30, 2018 1:25 PMThe 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 tablebThat will work. This will not:
select a, b, c
from tablea
union
select d, e
from tablebIf 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 26 total)
You must be logged in to reply to this topic. Login to reply