June 23, 2014 at 11:16 am
I have a table (tblEmployees) that stores all employees for our company. I have another table (tblInternalAudit) that lists internal audits that our company is required to complete each week. What I'd like to do is assign to each audit an employee by incrementing through both tables - the next audit record is assigned to the next employee. From what I've read, I can do this with cursors. If this is the best way to accomplish this, then I can figure this out. However, I've read that cursors should be avoided when possible. Is it possible to achieve this without using cursors?
June 23, 2014 at 11:38 am
Not sure I understood what you want to achieve. Is it OK to ask you to show before / after / what would you like to have by the end of the day, please?
You can use something like
WITH myEmp AS (
SELECT 1 AS empID,
'John' AS empName
UNION ALL
SELECT 2,
'Stephen')
SELECT * FROM myEmp
and so on....
June 23, 2014 at 12:36 pm
Sorry my post wasn't very clear. Each audit needs to have someone assigned to complete the audit. I'd like to assign the next employee in our employee table to the next audit, and continue to do so for every audit. I have attached a jpg to try to clarify what I mean. Let me know if this still isn't clear - I'm not very good at explaining things.
June 23, 2014 at 12:48 pm
Hi and welcome to the forums. From your explanation I am almost certain we can do this without a cursor. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
June 23, 2014 at 12:53 pm
I can see something that may work on the sample data; please be sure to test this for correct results in your environment, however!
DECLARE @EmployeeMax int = (SELECT COUNT(EmployeeID) FROM tblEmployee)
SELECT AuditName, CASE WHEN AuditID%@EmployeeMax = 0 THEN @EmployeeMax ELSE AuditID%@EmployeeMax END AS [Assigned To]
FROM tblInternalAudit
Basically, this will generate a sequence of assignments, from 1 to the max number of employees, across the tblInternalAudit table, and the sequence will repeat if anything is left over.
The key is the modulus (%) operator; it allows you to generate the first series painlessly, and then repeat it by replacing cases where an even division occurs with the maximum employee number with the use of a CASE statement..
This should generate the required information without the need for a cursor, but someone else may well have a method that buries this one in terms of performance 🙂
EDIT: Whoops. Looking at Lynn's code below, the MAX I had on tblEmployee is dead wrong :-P. It's possible for the MAX of EmployeeID to be lower than the number of employees, so that could provide inaccurate results. Changed it to a COUNT instead.
- 😀
June 23, 2014 at 12:53 pm
Still not with you. Is it something you've been looking for?
WITH myEmp AS (
SELECT 1 AS empID,
'John' AS empName
UNION ALL
SELECT 2,
'Stephen'),
myAudit AS (
SELECT 1 AS auditID,
'AuditOne' AS auditDesc,
GETDATE()+1 AS auditdateTime
UNION ALL
SELECT 2,
'AuditTwo',
GETDATE()+2
UNION ALL
SELECT 3,
'AuditThree',
GETDATE()-10)
SELECT e.empID,e.empName,a.auditDesc,a.auditdateTime
FROM myEmp e
JOIN myAudit a
ON a.auditID = e.empID
June 23, 2014 at 1:44 pm
Maybe something like this:
create table dbo.Emp(
EmpId int identity(1,1),
EmpName varchar(32)
);
create table dbo.Audit(
AuditId int identity(1,1),
AuditName varchar(32)
);
insert into dbo.Emp(EmpName)
values ('EmployeeOne'),('EmployeeTwo'),('EmployeeThree'),('EmployeeFour'),('EmployeeFive');
insert into dbo.Audit(AuditName)
values ('AuditOne'),('AuditTwo'),('AuditThree'),('AuditFour'),('AuditFive'),('AuditSix'),('AuditSeven'),('AuditEight'),('AuditNine'),('AuditTen');
with BaseEmp as (
select
EmpId,
EmpName,
rn = row_number() over (order by EmpId)
from
dbo.Emp)
, AuditBase as (
select
AuditId,
AuditName,
rn = (row_number() over (order by AuditId) - 1) % (select count(*) from dbo.Emp) + 1
from
dbo.Audit)
select
EmpName,
AuditName
from
BaseEmp be
inner join AuditBase ab
on (be.rn = ab.rn)
order by
ab.AuditId;
go
drop table dbo.Emp;
drop table dbo.Audit;
go
June 23, 2014 at 2:05 pm
Lynn, I was just putting together the sample DDL for creating the tables and sample data as Sean suggested, when I saw your post come through. Your post is exactly what I was looking for (I'm glad you could understand/translate my request). It's a little over my head, so I'm going to try to wrap my mind around exactly what it's doing, but the results are definitely what I was going for. In the future I will include the script for creating tables and sample data to help clarify what I'm trying to do. Thank you to everyone for trying to understand what I was trying to accomplish. Sorry for the confusion.
Thank you all so much,
Kevin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply