January 30, 2018 at 3:52 pm
Are you concerned with how the data is going to be inserted into a table? If so - then you can stop worrying because tables do not have an order and inserting using an order by isn't going to change how the data is actually inserted into the table.
If you are using a UNION or UNION ALL to return the results - then you need to include the date columns for the sorting and use an ORDER BY at the end...
Looking at your original queries - they will not UNION because they don't have compatible columns. In the first query - the first column being returned is an Invoice # - in the second query the first column being returned is [Date] (bad column name - should not be used.
In neither query are you return the [DateTime] (again - bad name) column or the Exception_DateTime column and from what I can tell you are parsing these out to separate date and time columns. Note: you should not parse these out to character data - use the DATE and TIME data types.
SELECT Invoice#, VoidDate = CAST(ie.DateTime AS DATE), VoidTime = CAST(ie.DateTime AS TIME(0)), ...
UNION
SELECT 'nnnnnn', ExceptionDate = CAST(e.Exception_DateTime AS DATE), ExceptionTime = CAST(e.Exception_DateTime AS TIME(0)), ...
ORDER BY VoidDate, VoidTime;
The above is how you would sort the results from a UNION - the union takes the column names and data types from the first query and those are what you need to use to sort. However - your original queries wouldn't even work as written so I am not sure this helps.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 30, 2018 at 10:09 pm
drew.allen - Tuesday, January 30, 2018 11:24 AMchef423 - 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
Hi Drew, thank you for the response. Duly noted, I was just looking for advice and thought the code would be enough, my mistake, I will post data tmrw.
Thank you
February 1, 2018 at 11:22 am
drew.allen - Tuesday, January 30, 2018 9:12 AMAnd there is only one sort on the entire UNION.
Yes, exactly, and that is probably where the OP made some false assumptions.
Two datasets that are to be UNION'd must have the same structure (which is defined by the first one), so even though there isn't a rule that says that also the column names have to be the same, it sometimes makes it easier to understand. Anyway, the two datasets are combined BEFORE the sorting is applied. And if the column names are different in the two source datasets, it's the names from the first dataset that is used by ORDER BY.
SELECT Name1, Name2, Name3
FROM TableOrView1
UNION ALL
SELECT Name4, Name5, Name6
FROM TableOrView2
ORDER BY Name2
will sort the whole combined dataset on the second colomn, because that's the name the column has in the first dataset (and therefore also used in the resulting dataset).
February 1, 2018 at 11:40 am
An additional note:
If for some reason it is necessary to keep the two source datasets in separate sections within the combined dataset, one can add an Id for each one and include this in the sorting:
SELECT Name1, Name2, Name3, CAST(1 AS TINYINT) AS SourceDataset
FROM TableOrView1
UNION ALL
SELECT Name4, Name5, Name6, 2
FROM TableOrView2
ORDER BY SourceDataset, Name2
This will keep the source datasets together in sequencial order, and arrange the rows within each in the order of the second column.
February 1, 2018 at 1:57 pm
What you posted is so bad in so many ways. Sometimes a column has an alias on it. Sometimes it doesn’t. You don’t understand the most basic principle of a tiered architecture; COBOL style formatting is done in a presentation layer, never, never, never in the database layer. And you don’t seem to be aware that the proprietary old Sybase MONEY data type doesn’t do correct arithmetic! Google it. It was created because it includes COBOL style picture edits for punctuation for display purposes.
You also didn’t bother to publish the DDL for the tables so we have to guess and rename many of your columns conformed ISO 11179 naming rules. Here’s my attempt to do cleanup
SELECT IE.invoice_nbr, IE.void_timestamp, IE.item_nbr, INV.item_name,
IE.foobar_amt, IE.foobar_qty, IE.reason_code
FROM Invoice_Exceptions AS IE,
Inventory AS INV
WHERE item_nbr = INV.item_nbr
AND IE.something_timestamp BETWEEN @Yesterday AND @period_end_date;
you also don’t seem to understand the concept that a data element does not change its name from table to table are query to query. In the ANSI standards, what Microsoft and the old Sybase products call “DATETIME†is called a timestamp in the ANSI/ISO standards.It is a single unit of measure and you don’t split it into two fields. A “date†is not a valid top column name; it’s a data type in SQL. By definition, an identifier cannot be numeric because you do not math on it. Furthermore, a data element doesn’t change its name from place to place, nor can it change its attribute property. This means that your permission_id cannot go from being an identifier of one particular attribute to being the type of an exception. In data modeling, this would be taking your hat size and recasting it is your IQ. You can never have a valid data model.
SELECT E.exception_timestamp, P.cashier_id,
P.short_something_description, E.reason_code,
FROM Exceptions AS E,
Permissions AS P
WHERE P.exception_type = E.exception_type
AND E.exception_type IN (20, 21, 36)
AND E.exception_timestamp BETWEEN @yesterday AND @period_end_date;
did you notice that I took off your ORDER BY clauses? By definition, unlike the deck of punch card your modeling here, a table has no ordering. And it would disappear anyway when you union them together. What you have done is turn each of the query results from a table into an implicit cursor. With overhead and sorting and all these other things that will simply waste resources.
Finally, these two queries are not what is called “union compatible†in SQL. This means that they have a different number of columns, that they don’t match up position by position, datatype by datatype.
Why don’t you follow the forum rules and post actual DDL?
Please post DDL and follow ANSI/ISO standards when asking for help.
February 20, 2018 at 1:45 pm
chef423 - Tuesday, January 30, 2018 8:29 AM
What you posted is so bad in so many ways. Sometimes a column has an alias on it. Sometimes it doesn’t. You don’t understand the most basic principle of a tiered architecture; COBOL style formatting is done in a presentation layer, never, never, never in the database layer. And you don’t seem to be aware that the proprietary old Sybase MONEY data type doesn’t do correct arithmetic! Google it. It was created because it includes COBOL style picture edits for punctuation for display purposes.
You also didn’t bother to publish the DDL for the tables so we have to guess and rename many of your columns conformed ISO 11179 naming rules. Here’s my attempt to do cleanup
SELECT IE.invoice_nbr, IE.void_timestamp, IE.item_nbr, INV.item_name,
IE.foobar_amt, IE.foobar_qty, IE.reason_code
FROM Invoice_Exceptions AS IE,
Inventory AS INV
WHERE item_nbr = INV.item_nbr
AND IE.something_timestamp BETWEEN @Yesterday AND @period_end_date;
you also don’t seem to understand the concept that a data element does not change its name from table to table are query to query. In the ANSI standards, what Microsoft and the old Sybase products call “DATETIME†is called a timestamp in the ANSI/ISO standards.It is a single unit of measure and you don’t split it into two fields. A “date†is not a valid top column name; it’s a data type in SQL. By definition, an identifier cannot be numeric because you do not math on it. Furthermore, a data element doesn’t change its name from place to place, nor can it change its attribute property. This means that your permission_id cannot go from being an identifier of one particular attribute to being the type of an exception. In data modeling, this would be taking your hat size and recasting it is your IQ. You can never have a valid data model.
SELECT E.exception_timestamp, P.cashier_id,
P.short_something_description, E.reason_code,
FROM Exceptions AS E,
Permissions AS P
WHERE P.exception_type = E.exception_type
AND E.exception_type IN (20, 21, 36)
AND E.exception_timestamp BETWEEN @yesterday AND @period_end_date;
did you notice that I took off your ORDER BY clauses? By definition, unlike the deck of punch card your modeling here, a table has no ordering. And it would disappear anyway when you union them together. What you have done is turn each of the query results from a table into an implicit cursor. With overhead and sorting and all these other things that will simply waste resources.
Finally, these two queries are not what is called “union compatible†in SQL. This means that they have a different number of columns, that they don’t match up position by position, datatype by datatype.
Why don’t you follow the forum rules and post actual DDL?
Hi Joe, thank you for the feedback. "COBOL style formatting is done in a presentation layer, never, never, never in the database layer" - I understand this and unfortunately this is not possible for this workflow and outcome, that is why I am searching for a way @ the SQL/COBAL level. Its two very simple tables that i need sorted by time, as the dates will always be the same as the report is ran daily. Simple enough. I was looking for more of method thinking when I posted, not actual code, as a discussion. But I will give the DDL a shot.
February 20, 2018 at 2:02 pm
DECLARE @Yesterday DATETIME = DATEADD(DAY,-1,CAST(GETDATE() AS DATE));
DECLARE @PeriodStart DATETIME = DATEADD(DAY,-6,@Yesterday),
@PeriodEnd DATETIME = CAST(GETDATE() AS DATE),
@MonthStart DATETIME =DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);
SET NOCOUNT ON;
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
CREATE TABLE Exceptions(
Date DATETIME NOT NULL
,Time DATETIME NOT NULL
,Cashier_ID INT
,Description NVARCHAR(100)
,Reason NVARCHAR(100)
);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-23-2018','4:21PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-23-2018','4:23PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-23-2018','4:35PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-26-2018','6:30PM',100199,N'Invoice Price Changes',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-26-2018','6:30PM',100199,N'Invoice Price Changes',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-26-2018','6:30PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('01-26-2018','6:34PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-03-2018','3:36PM',100177,N'Open Cash Drawer',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-04-2018','12:48PM',100177,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-04-2018','1:25PM',100177,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-05-2018','5:53PM',100177,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-06-2018','5:02PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-12-2018','3:44PM',100199,N'Invoice Price Changes',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-13-2018','4:04PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-13-2018','4:08PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-15-2018','4:12PM',100199,N'Invoice Discounts',NULL);
INSERT INTO Exceptions(Date,Time,Cashier_ID,Description,Reason) VALUES ('02-17-2018','12:49PM',100177,N'Open Cash Drawer',NULL);
DECLARE @Yesterday DATETIME = DATEADD(DAY,-1,CAST(GETDATE() AS DATE));
DECLARE @PeriodStart DATETIME = DATEADD(DAY,-6,@Yesterday),
@PeriodEnd DATETIME = CAST(GETDATE() AS DATE),
@MonthStart DATETIME =DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);
SET NOCOUNT ON;
SELECT
LTRIM(RIGHT(CONVERT(varchar(6), [ie].[Invoice_Number],100),7)) AS [Invoice_#]
, CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Date]
, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[DateTime],100),7)) AS [Time]
, [ie].[ItemNum] AS [PLU_Code]
, [inv].[ItemName] AS [Item_Name]
, '$' + 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]
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
SELECT
CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Void_Date]
, '---'
, '---'
, '---'
, '---'
, '$' + CONVERT(VARCHAR(18), SUM(CAST([ie].[AMOUNT] AS MONEY))) AS TOTAL_DELETIONFROM
Invoice_Exceptions AS [ie]
JOIN inventory AS [inv]
ON [ie].[ItemNum] = [inv].[ItemNum]
AND [ie].[DateTime] >= @Yesterday AND [ie].[DateTime] < @PeriodEnd
GROUP BY CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-'))
CREATE TABLE IER(
Invoice_ INTEGER NOT NULL PRIMARY KEY
,Date DATE NOT NULL
,Time VARCHAR(7) NOT NULL
,PLU_Code VARCHAR(13) NOT NULL
,Item_Name VARCHAR(30) NOT NULL
,Amount VARCHAR(6) NOT NULL
,Quantity INTEGER NOT NULL
,Reason VARCHAR(13) NOT NULL
);
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','12:09PM','2590020626','Swisher sweets - Swisher Sweet','$1.65',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','12:09PM','2590022672','Swisher sweets - Swisher Ice','$1.65',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','12:10PM','RETURN','REFUND / RETURN','$3.70',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','1:40PM','6970232215845','SMOK QBOX','$58.99',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4072,'02-19-2018','1:43PM','1234','SVC FEE','$0.00',0,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4072,'02-19-2018','1:43PM','1236','THANK YOU','$0.00',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','5:35PM','283612','Marlboro menthol - MARL MT FF','$7.54',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','5:35PM','283612','Marlboro menthol - MARL MT FF','$7.54',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (0,'02-19-2018','7:15PM','283842','Marlboro - MARL LT BX','$7.94',10,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4137,'02-19-2018','7:43PM','1700','OPEN TAXABLE','$7.99',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4140,'02-19-2018','7:57PM','7161049508','Dutch - Dutch Irish','$1.75',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4150,'02-19-2018','8:59PM','1236','THANK YOU','$0.00',1,'Item Deletion');
INSERT INTO IER(Invoice_,Date,Time,PLU_Code,Item_Name,Amount,Quantity,Reason) VALUES (4150,'02-19-2018','8:59PM','1234','SVC FEE','$0.00',0,'Item Deletion');
Does this help?
February 20, 2018 at 2:25 pm
chef423 - Tuesday, February 20, 2018 2:02 PMDoes this help?
Invalid object name 'Permissions'.
Invalid object name 'Inventory_Exceptions'.
Invalid object name 'Inventory'.
You are also missing expected outcome.
So, no, this does not help.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 20, 2018 at 4:27 pm
drew.allen - Tuesday, February 20, 2018 2:25 PMchef423 - Tuesday, February 20, 2018 2:02 PMDoes this help?Invalid object name 'Permissions'.
Invalid object name 'Inventory_Exceptions'.
Invalid object name 'Inventory'.You are also missing expected outcome.
So, no, this does not help.
Drew
Ok, thanks for the feedback, I will correct.
February 26, 2018 at 7:49 pm
Here is a template for you.
Read the comments in the code - they should explain you how to build the query.SELECT -- Formatting of the output is here
CONVERT(...DocDateTime DocDateTime ...) [Date],
CONVERT(...DocDateTime ...) [Time],
{other columns formatting here}
FROM (
-- Derived table to prepare all the required data
SELECT IE.invoice_nbr DocNumber,
IE.something_timestamp DocDateTime, -- the field to be used for ordering
IE.void_timestamp, IE.item_nbr,
INV.item_name,
IE.foobar_amt, IE.foobar_qty, IE.reason_code
FROM Invoice_Exceptions AS IE,
Inventory AS INV
WHERE item_nbr = INV.item_nbr
AND IE.something_timestamp BETWEEN @Yesterday AND @period_end_date
UNION ALL
SELECT
NULL DocNumber -- Number or columns, their data types and their sequence in both queries must be identical
E.exception_timestamp DocDateTime
P.cashier_id,
P.short_something_description,
NULL foobar_amt, NULL foobar_qty, E.reason_code,
FROM Exceptions AS E,
Permissions AS P
WHERE P.exception_type = E.exception_type
AND E.exception_type IN (20, 21, 36) AND E.exception_timestamp BETWEEN @yesterday AND @period_end_date
) DT
ORDER BY DocDateTime
_____________
Code for TallyGenerator
February 26, 2018 at 9:28 pm
You cannot UNION and sort these 2 queries as they stand.
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
February 26, 2018 at 10:36 pm
DesNorton - Monday, February 26, 2018 9:28 PMYou cannot UNION and sort these 2 queries as they stand.
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
UNION is not only one thing which won't work in my query.
🙂
As I said - it's just a template.
_____________
Code for TallyGenerator
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply