November 24, 2018 at 11:36 am
Hi all, I need help. I hope someone can provide some info.
I have provided the SQL code below.
Please note on the sales table that employees 1 and 2 have an entry where MediaName = 'Gift Card" but, employee number 3 does not?
For the tasks I need to do, I need to use a cursor or any other approach to insert a row for all employees with out the 'Gift Card" row with a total of 0 as I have on the cursor script below.
I am stuck on identifying the employees with not 'Gift Card" row.
I will appreciate help on this.
Thanks,
Jose
CREATE TABLE [dbo].[Sales](
[EmployeeID] [int] NOT NULL,
[MediaName] [varchar](75) NOT NULL,
[Total] [float] NULL
) ON [PRIMARY]
GO
INSERT into Sales(EmployeeId, MediaName, Total) VALUES
(1, 'CASH', 100.00),
(1, 'Gift Card', 100.00),
(1, 'MasterCard', 100.00),
(1, 'Visa', 100.00),
(2, 'CASH', 100.00),
(2, 'Gift Card', 100.00),
(2, 'MasterCard', 100.00),
(2, 'Visa', 100.00),
(3, 'CASH', 100.00),
(3, 'MasterCard', 100.00),
(3, 'Visa', 100.00);
select * from sales
DECLARE @EmployeeID int
,@MediaName VARCHAR(75)
,@Total int
DECLARE @Counter INT
SET @Counter = 1
DECLARE LoadMedia CURSOR
FOR
SELECT EmployeeID, MediaName, Total
FROM Sales
OPEN LoadMedia
FETCH NEXT FROM LoadMedia INTO
@EmployeeID, @MediaName, @Total
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Counter = 1
BEGIN
-- find employees who don't have a row with 'Gift Card"
Insert into Sales
(EmployeeID, MediaName, Total)
Values(@EmployeeID, 'Gift Card', 0)
END
SET @Counter = @Counter + 1
FETCH NEXT FROM LoadMedia INTO
@EmployeeID, @MediaName, @Total
END
CLOSE LoadMedia
DEALLOCATE LoadMedia
November 24, 2018 at 12:08 pm
A cursor for that is all kinds of overkill. How about a simple not exists subquery?
SELECT *
FROM Employee e
WHERE NOT EXISTS (
SELECT s.EmployeeID
FROM Sales s
WHERE [MediaName] = 'Gift Card'
AND s.EmployeeID = e.EmployeeID
);
Since you didn't provide a script for the destination table, I didn't do any inserting... If you had a table of just Employee IDs, ....CREATE TABLE EmployeesWithoutGiftCards( EmployeeID INT PIRMARY KEY);
GO
and then the insert script...INSERT INTO EmployeesWithoutGiftCards(EmployeeID)
SELECT e.EmployeeID
FROM Employee e
WHERE NOT EXISTS (
SELECT s.EmployeeID
FROM Sales s
WHERE [MediaName] = 'Gift Card'
AND s.EmployeeID = e.EmployeeID );
Where's Chris Morris when you need him?! I remember writing some code with a cursor in it to generate a bunch of random data, and my ears were red after reading what he wrote. Maybe I should post a link to it.
November 24, 2018 at 12:33 pm
Hi, the data involved in this task is fairly small. So, I think the cursor would not be very costly.
I have this other query,
SELECT DISTINCT
[EmployeeID]
--INTO MissingGC
FROM [Sales] WHERE MediaName <> 'Gift Card'
and [EmployeeID] not in (Select [EmployeeID] from [Sales] WHERE MediaName = 'Gift Card');
it returns employee number 3. So now, I need to insert a row as show below.
Insert into Sales
(EmployeeID, MediaName, Total)
Values('3', 'Gift Card', 0)
but, in reality, I will have several employees where I will need to insert this row.
Thanks,
November 24, 2018 at 1:50 pm
josetur12 - Saturday, November 24, 2018 12:33 PMHi, the data involved in this task is fairly small. So, I think the cursor would not be very costly.
People REALLY need to stop using low row counts as an excuse for not doing things the right way. You have no control over what the future data will look like and should write your code for scalability at all times or go through what I go through when trying to fix performance issues at every company I've ever worked at for SQL Server... a death by a thousand cuts.
It's easy to write good code and actually takes less code and thought to avoid the cursor than not. Learn the right way to do it and stop "practicing" the wrong way just because of low row counts. 😉
I have this other query,
SELECT DISTINCT
[EmployeeID]
--INTO MissingGC
FROM [Sales] WHERE MediaName <> 'Gift Card'
and [EmployeeID] not in (Select [EmployeeID] from [Sales] WHERE MediaName = 'Gift Card');
it returns employee number 3. So now, I need to insert a row as show below.
Insert into Sales
(EmployeeID, MediaName, Total)
Values('3', 'Gift Card', 0)
but, in reality, I will have several employees where I will need to insert this row.
Thanks,
Did you look at pietlinden 's code? It's pretty easy to add the words INSERT INTO and a column list.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2018 at 2:30 pm
josetur12 - Saturday, November 24, 2018 12:33 PMHi, the data involved in this task is fairly small. So, I think the cursor would not be very costly.
I have this other query,
SELECT DISTINCT
[EmployeeID]
--INTO MissingGC
FROM [Sales] WHERE MediaName <> 'Gift Card'
and [EmployeeID] not in (Select [EmployeeID] from [Sales] WHERE MediaName = 'Gift Card');
it returns employee number 3. So now, I need to insert a row as show below.
Insert into Sales
(EmployeeID, MediaName, Total)
Values('3', 'Gift Card', 0)
but, in reality, I will have several employees where I will need to insert this row.
Thanks,
If you have a table of employees already, use it. DISTINCT is really expensive too. Don't use that unless you have to. Not as bad as a cursor, but slow.
November 24, 2018 at 3:32 pm
Thank you all, I will try pietlinden 's code.
November 25, 2018 at 12:03 pm
Thank you, it worked!!
November 26, 2018 at 12:02 pm
josetur12 - Saturday, November 24, 2018 11:36 AM
>> .. insert a row for all employees with out the 'Gift Card" row with a total of 0.00 as I have on the cursor script below. I am stuck on identifying the employees with no 'Gift Card" row. <<
CREATE TABLE Sales_Summary
(emp_id CHAR(3) NOT NULL,
payment_method CHAR(10)NOT NULL
CHECK(payment_method IN
('CASH', 'Gift Card', 'MasterCard')),
PRIMARY KEY (emp_id, payment_method),
sale_amt_tot DECIMAL (8,2) NOT NULL
);
INSERT INTO Sales
VALUES
('001', 'CASH', 100.00),
('001', 'Gift Card', 100.00),
('001', 'MasterCard', 100.00),
('001', 'Visa', 100.00),
('002', 'CASH', 100.00),
('002', 'Gift Card', 100.00),
('002', 'MasterCard', 100.00),
('002', 'Visa', 100.00),
('003', 'CASH', 100.00),
('003', 'MasterCard', 100.00),
('003', 'Visa', 100.00);
[/end]
A simple insertion statement to replace your cursor. Please notice that it's declarative, not procedural like a cursor. The reason the cursors exist in SQL at all is that the original SQL standards and products were built on top of existing filesystems. In fact, it was based on the magnetic tape commands for the IBM products!
You need to change your mindset. We'll never be able to write decent SQL. When I'm teaching classes. I found that telling people to put the phrase "the set of <name of the set of things you want>, such that…" In front of simple declarative sentences that describe what you're after. In this example, the set you're looking for is "the set of employees that do not have gift cards", which means the whole set all at once. Not element by element.
You then begin nesting these definitions in much the same way that you learn to nest expressions in algebra. Your goal in a declarative language is to do it all in one single statement (not always possible, but a good design goal).
I found over the decades of teaching SQL that at some point in learning the language people have an epiphany, and after that they don't think the same way as they did before. This usually takes 1 to 2 years of full-time employment (don't despair; think how long it takes to start thinking in a foreign language when you're learning it).
INSERT INTO Sales_Summary
SELECT emp_id, emp_id, 'Gift Card', 0.00
FROM Sales_Summary AS S0
WHERE NOT EXISTS
(SELECT *
FROM Sales_Summary AS S1
WHERE S1.payment_method = 'Gift Card'
AND S1.emp_id = S0.emp_id);
Please post DDL and follow ANSI/ISO standards when asking for help.
November 26, 2018 at 2:33 pm
jcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AM>> .. insert a row for all employees with out the 'Gift Card" row with a total of 0.00 as I have on the cursor script below. I am stuck on identifying the employees with no 'Gift Card" row. <<
CREATE TABLE Sales_Summary
(emp_id CHAR(3) NOT NULL,
payment_method CHAR(10)NOT NULL
CHECK(payment_method IN
('CASH', 'Gift Card', 'MasterCard')),
PRIMARY KEY (emp_id, payment_method),
sale_amt_tot DECIMAL (8,2) NOT NULL
);INSERT INTO Sales
VALUES
('001', 'CASH', 100.00),
('001', 'Gift Card', 100.00),
('001', 'MasterCard', 100.00),
('001', 'Visa', 100.00),
('002', 'CASH', 100.00),
('002', 'Gift Card', 100.00),
('002', 'MasterCard', 100.00),
('002', 'Visa', 100.00),
('003', 'CASH', 100.00),
('003', 'MasterCard', 100.00),
('003', 'Visa', 100.00);
[/end]A simple insertion statement to replace your cursor. Please notice that it's declarative, not procedural like a cursor. The reason the cursors exist in SQL at all is that the original SQL standards and products were built on top of existing filesystems. In fact, it was based on the magnetic tape commands for the IBM products!
You need to change your mindset. We'll never be able to write decent SQL. When I'm teaching classes. I found that telling people to put the phrase "the set of <name of the set of things you want>, such that…" In front of simple declarative sentences that describe what you're after. In this example, the set you're looking for is "the set of employees that do not have gift cards", which means the whole set all at once. Not element by element.
You then begin nesting these definitions in much the same way that you learn to nest expressions in algebra. Your goal in a declarative language is to do it all in one single statement (not always possible, but a good design goal).
I found over the decades of teaching SQL that at some point in learning the language people have an epiphany, and after that they don't think the same way as they did before. This usually takes 1 to 2 years of full-time employment (don't despair; think how long it takes to start thinking in a foreign language when you're learning it).
INSERT INTO Sales_Summary
SELECT emp_id, emp_id, 'Gift Card', 0.00
FROM Sales_Summary AS S0
WHERE NOT EXISTS
(SELECT *
FROM Sales_Summary AS S1
WHERE S1.payment_method = 'Gift Card'
AND S1.emp_id = S0.emp_id);
Mr. Celko, don't you know how to properly use end tags for the IF Code blocks?
November 26, 2018 at 3:04 pm
jcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AM>> .. insert a row for all employees with out the 'Gift Card" row with a total of 0.00 as I have on the cursor script below. I am stuck on identifying the employees with no 'Gift Card" row. <<
CREATE TABLE Sales_Summary
(emp_id CHAR(3) NOT NULL,
payment_method CHAR(10)NOT NULL
CHECK(payment_method IN
('CASH', 'Gift Card', 'MasterCard')),
PRIMARY KEY (emp_id, payment_method),
sale_amt_tot DECIMAL (8,2) NOT NULL
);INSERT INTO Sales
VALUES
('001', 'CASH', 100.00),
('001', 'Gift Card', 100.00),
('001', 'MasterCard', 100.00),
('001', 'Visa', 100.00),
('002', 'CASH', 100.00),
('002', 'Gift Card', 100.00),
('002', 'MasterCard', 100.00),
('002', 'Visa', 100.00),
('003', 'CASH', 100.00),
('003', 'MasterCard', 100.00),
('003', 'Visa', 100.00);
[/end]A simple insertion statement to replace your cursor. Please notice that it's declarative, not procedural like a cursor. The reason the cursors exist in SQL at all is that the original SQL standards and products were built on top of existing filesystems. In fact, it was based on the magnetic tape commands for the IBM products!
You need to change your mindset. We'll never be able to write decent SQL. When I'm teaching classes. I found that telling people to put the phrase "the set of <name of the set of things you want>, such that…" In front of simple declarative sentences that describe what you're after. In this example, the set you're looking for is "the set of employees that do not have gift cards", which means the whole set all at once. Not element by element.
You then begin nesting these definitions in much the same way that you learn to nest expressions in algebra. Your goal in a declarative language is to do it all in one single statement (not always possible, but a good design goal).
I found over the decades of teaching SQL that at some point in learning the language people have an epiphany, and after that they don't think the same way as they did before. This usually takes 1 to 2 years of full-time employment (don't despair; think how long it takes to start thinking in a foreign language when you're learning it).
INSERT INTO Sales_Summary
SELECT emp_id, emp_id, 'Gift Card', 0.00
FROM Sales_Summary AS S0
WHERE NOT EXISTS
(SELECT *
FROM Sales_Summary AS S1
WHERE S1.payment_method = 'Gift Card'
AND S1.emp_id = S0.emp_id);
Heh... Emp_ID shouldn't be a CHAR(3). 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2018 at 4:51 am
jcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AM>> .. insert a row for all employees with out the 'Gift Card" row with a total of 0.00 as I have on the cursor script below. I am stuck on identifying the employees with no 'Gift Card" row. <<
CREATE TABLE Sales_Summary
(emp_id CHAR(3) NOT NULL,
payment_method CHAR(10)NOT NULL
CHECK(payment_method IN
('CASH', 'Gift Card', 'MasterCard')),
PRIMARY KEY (emp_id, payment_method),
sale_amt_tot DECIMAL (8,2) NOT NULL
);INSERT INTO Sales
VALUES
('001', 'CASH', 100.00),
('001', 'Gift Card', 100.00),
('001', 'MasterCard', 100.00),
('001', 'Visa', 100.00),
('002', 'CASH', 100.00),
('002', 'Gift Card', 100.00),
('002', 'MasterCard', 100.00),
('002', 'Visa', 100.00),
('003', 'CASH', 100.00),
('003', 'MasterCard', 100.00),
('003', 'Visa', 100.00);
[/end]A simple insertion statement to replace your cursor. Please notice that it's declarative, not procedural like a cursor. The reason the cursors exist in SQL at all is that the original SQL standards and products were built on top of existing filesystems. In fact, it was based on the magnetic tape commands for the IBM products!
You need to change your mindset. We'll never be able to write decent SQL. When I'm teaching classes. I found that telling people to put the phrase "the set of <name of the set of things you want>, such that…" In front of simple declarative sentences that describe what you're after. In this example, the set you're looking for is "the set of employees that do not have gift cards", which means the whole set all at once. Not element by element.
You then begin nesting these definitions in much the same way that you learn to nest expressions in algebra. Your goal in a declarative language is to do it all in one single statement (not always possible, but a good design goal).
I found over the decades of teaching SQL that at some point in learning the language people have an epiphany, and after that they don't think the same way as they did before. This usually takes 1 to 2 years of full-time employment (don't despair; think how long it takes to start thinking in a foreign language when you're learning it).
INSERT INTO Sales_Summary
SELECT emp_id, emp_id, 'Gift Card', 0.00
FROM Sales_Summary AS S0
WHERE NOT EXISTS
(SELECT *
FROM Sales_Summary AS S1
WHERE S1.payment_method = 'Gift Card'
AND S1.emp_id = S0.emp_id);
create statement for Sales_Summary and insert statement for sales table???!!! So many mistakes Mr. Celko.
Saravanan
November 27, 2018 at 7:44 am
jcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AM
Heh... Emp_ID shouldn't be a CHAR(3). 😉
I would prefer 9 or 10 digits (SSN or SSI compatible for North America) and I would like to have a check digit. Many years ago, I worked with a State level agency personnel system that used sequential numbers without leading zeroes or a check digit. Total mess! Sometimes the COBOL added leading zero padding but not in other programs, so screens (3270 Terminals mostly) and paper forms did not quite match. Any typo toward the right side of the input field was probably an employee. Etc.
But for a posting, I want people to learn that an identifier is on a nominal scale and cannot be a numeric or pointer value.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 27, 2018 at 7:50 am
Jeff Moden - Monday, November 26, 2018 3:04 PMjcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AMHeh... Emp_ID shouldn't be a CHAR(3). 😉
I would prefer 9 or 10 digits (SSN or SSI compatible for North America) and I would like to have a check digit. Many years ago, I worked with a State level agency personnel system that used sequential numbers without leading zeroes or a check digit. Total mess! Sometimes the COBOL added leading zero padding but not in other programs, so screens (3270 Terminals mostly) and paper forms did not quite match. Any typo toward the right side of the input field was probably an employee. Etc.
But for a posting, I want people to learn that an identifier is on a nominal scale and cannot be a numeric or pointer value.
And just why can't an integer be used for that purpose? I can understand it if there can be leading zeroes. However, a nine digit number as an integer takes 4 bytes of space while a 9 "digit" character string takes 9 bytes.
November 28, 2018 at 5:22 am
Jeff Moden - Monday, November 26, 2018 3:04 PMjcelko212 32090 - Monday, November 26, 2018 12:02 PMjosetur12 - Saturday, November 24, 2018 11:36 AMHeh... Emp_ID shouldn't be a CHAR(3). 😉
I would prefer 9 or 10 digits (SSN or SSI compatible for North America) and I would like to have a check digit. Many years ago, I worked with a State level agency personnel system that used sequential numbers without leading zeroes or a check digit. Total mess! Sometimes the COBOL added leading zero padding but not in other programs, so screens (3270 Terminals mostly) and paper forms did not quite match. Any typo toward the right side of the input field was probably an employee. Etc.
But for a posting, I want people to learn that an identifier is on a nominal scale and cannot be a numeric or pointer value.
SSN as an id, even for an employee, is NOT a good idea. SSN should only be used for purposes related to Social Security itself, or for paying taxes.
For example, everyone is getting new Medicare ID's that don't include SSN.
November 28, 2018 at 5:40 am
gvoshol 73146 - Wednesday, November 28, 2018 5:22 AMjcelko212 32090 - Tuesday, November 27, 2018 7:44 AMI would prefer 9 or 10 digits (SSN or SSI compatible for North America) and I would like to have a check digit. Many years ago, I worked with a State level agency personnel system that used sequential numbers without leading zeroes or a check digit. Total mess! Sometimes the COBOL added leading zero padding but not in other programs, so screens (3270 Terminals mostly) and paper forms did not quite match. Any typo toward the right side of the input field was probably an employee. Etc.But for a posting, I want people to learn that an identifier is on a nominal scale and cannot be a numeric or pointer value.
SSN as an id, even for an employee, is NOT a good idea. SSN should only be used for purposes related to Social Security itself, or for paying taxes.
For example, everyone is getting new Medicare ID's that don't include SSN.
SSN isn't unique, either. https://www.pcworld.com/article/3004654/government/a-tale-of-two-women-same-birthday-same-social-security-number-same-big-data-mess.html
and some people have more than one SSN... https://www.idanalytics.com/press-release/20-million-americans-multiple-social-security-numbers-associated-name/
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply