October 6, 2014 at 8:42 am
Hi all,
I'm trying to create some SQL to then run a Cognos report. This is what I have so far but I am finding that every few runs you get a scenario where the name and email in the 3rd/4th column are the same as the name/email in the 1st and 2nd column.
Is there a way to prevent this? If anyone has any suggestions that would be greatly appreciated.
;WITH Random AS (
SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY NEWID()) AS 'Eggs', [id]
,[employeeName]
,[employeeCode]
,[employeeEmail]
FROM [Timesheets].[dbo].[tblEmployee]
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
ORDER BY NEWID()
)
SELECT e.employeeName, e.employeeEmail, Random.employeeName, Random.employeeEmail
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY e.employeeName) AS id, e.employeeName, e.employeeEmail
FROM [Timesheets].[dbo].tblEmployee e
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
) e
INNER JOIN Random ON e.id = Random.Eggs
October 6, 2014 at 12:05 pm
LittleDuck (10/6/2014)
Hi all,I'm trying to create some SQL to then run a Cognos report. This is what I have so far but I am finding that every few runs you get a scenario where the name and email in the 3rd/4th column are the same as the name/email in the 1st and 2nd column.
Is there a way to prevent this? If anyone has any suggestions that would be greatly appreciated.
;WITH Random AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY NEWID()) AS 'Eggs', [id]
,[employeeName]
,[employeeCode]
,[employeeEmail]
FROM [Timesheets].[dbo].[tblEmployee]
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
ORDER BY NEWID()
)
SELECT e.employeeName, e.employeeEmail, Random.employeeName, Random.employeeEmail
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY e.employeeName) AS id, e.employeeName, e.employeeEmail
FROM [Timesheets].[dbo].tblEmployee e
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
) e
INNER JOIN Random ON e.id = Random.Eggs
Not totally clear from your post what you are trying to do here but maybe just add a where to your last query. Something like "Where e.employeeemail <> Random.email"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2014 at 1:41 pm
If you're creating a random column, you could use it twice to ensure that you're not hitting the same employee.
CREATE TABLE tblEmployee(
[id] int IDENTITY
,[employeeName] varchar(100)
,[employeeCode] char(5)
,[employeeEmail] varchar(100)
,[retired] bit
)
INSERTtblEmployee VALUES
('Luis', 'A', 'Luis@SSC.com',0),
('Peter', 'A', 'Peter@SSC.com',0),
('John', 'A', 'John@SSC.com',0),
('Michael', 'C', 'Michael@SSC.com',0),
('Mary', 'C', 'Mary@SSC.com',0),
('Wayne', 'TK', 'Wayne@SSC.com',0);
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS Eggs
,[id]
,[employeeName]
,[employeeCode]
,[employeeEmail]
INTO #Random
FROM [dbo].[tblEmployee]
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
SELECT r.Eggs, r.employeeName, r.employeeEmail, r2.Eggs, r2.employeeName, r2.employeeEmail
FROM #Random r
JOIN #Random r2 ON r.Eggs + 1 = r2.Eggs --join with the next row
OR ( r2.Eggs =1 AND r.Eggs = (SELECT COUNT(*) n FROM #Random)) --join the first and the last rows
DROP TABLE #Random
GO
DROP TABLE tblEmployee
EDIT: Changed the CTE into a temp table because the Eggs column was being calculated twice.
October 7, 2014 at 2:19 am
Apologies for not being completely clear.
I'm trying to generate a table which displays:
Name | Email | Name | Email
Tom @ Dave @
Dave @ Steve @
Paul @ Paul @
Sally @ Pete @
Pete @ Sally @
Kind of like this but the issue I was having was from time to time I'd end up with the person in the first part matching the person in the second part.
It's basically to assign one person in the office to another person in the office randomly.
October 7, 2014 at 2:47 am
Luis Cazares (10/6/2014)
If you're creating a random column, you could use it twice to ensure that you're not hitting the same employee.
CREATE TABLE tblEmployee(
[id] int IDENTITY
,[employeeName] varchar(100)
,[employeeCode] char(5)
,[employeeEmail] varchar(100)
,[retired] bit
)
INSERTtblEmployee VALUES
('Luis', 'A', 'Luis@SSC.com',0),
('Peter', 'A', 'Peter@SSC.com',0),
('John', 'A', 'John@SSC.com',0),
('Michael', 'C', 'Michael@SSC.com',0),
('Mary', 'C', 'Mary@SSC.com',0),
('Wayne', 'TK', 'Wayne@SSC.com',0);
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS Eggs
,[id]
,[employeeName]
,[employeeCode]
,[employeeEmail]
INTO #Random
FROM [dbo].[tblEmployee]
WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'
SELECT r.Eggs, r.employeeName, r.employeeEmail, r2.Eggs, r2.employeeName, r2.employeeEmail
FROM #Random r
JOIN #Random r2 ON r.Eggs + 1 = r2.Eggs --join with the next row
OR ( r2.Eggs =1 AND r.Eggs = (SELECT COUNT(*) n FROM #Random)) --join the first and the last rows
DROP TABLE #Random
GO
DROP TABLE tblEmployee
EDIT: Changed the CTE into a temp table because the Eggs column was being calculated twice.
Giving this a try now, looking good so far 🙂
I've run in to one issue which is that it doesn't work well in Cognos.... besides that it does what I want it to do.
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply