May 29, 2015 at 8:43 am
I need to prepare a roster rotating persons within each Department for the available dates
Please refer to the below sample tables and data
CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department
(
DeptName VARCHAR(10),
PersonName VARCHAR(10)
)
INSERT INTO Roster VALUES('01/01/2015', '01/05/2015')
INSERT INTO Roster VALUES('01/06/2015', '01/10/2015')
INSERT INTO Roster VALUES('01/11/2015', '01/15/2015')
INSERT INTO Roster VALUES('01/16/2015', '01/20/2015')
INSERT INTO Department VALUES('A','P1')
INSERT INTO Department VALUES('A','P2')
INSERT INTO Department VALUES('B','P3')
INSERT INTO Department VALUES('B','P4')
INSERT INTO Department VALUES('B','P5')
INSERT INTO Department VALUES('C','P6')
INSERT INTO Department VALUES('C','P7')
INSERT INTO Department VALUES('C','P8')
INSERT INTO Department VALUES('C','P9')
Expected Output
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person
UNION
SELECT '01/06/2015' StartDate, '01/10/2015' EndDate, 'A' Department,'P2' Person
UNION
SELECT '01/06/2015' StartDate, '01/10/2015' EndDate, 'B' Department,'P4' Person
UNION
SELECT '01/06/2015' StartDate, '01/10/2015' EndDate, 'C' Department,'P7' Person
UNION
SELECT '01/11/2015' StartDate, '01/15/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/11/2015' StartDate, '01/15/2015' EndDate, 'B' Department,'P5' Person
UNION
SELECT '01/11/2015' StartDate, '01/15/2015' EndDate, 'C' Department,'P8' Person
UNION
SELECT '01/16/2015' StartDate, '01/20/2015' EndDate, 'A' Department,'P2' Person
UNION
SELECT '01/16/2015' StartDate, '01/20/2015' EndDate, 'B' Department,'P2' Person
UNION
SELECT '01/16/2015' StartDate, '01/20/2015' EndDate, 'C' Department,'P9' Person
May 29, 2015 at 9:19 am
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. 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/
May 29, 2015 at 9:31 am
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.
Thanks Sean for your comment. I have rephrased my original post now.
May 29, 2015 at 9:43 am
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
_______________________________________________________________
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/
May 29, 2015 at 9:47 am
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.
May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.
May 29, 2015 at 10:04 am
Based on your original post, this will give you exactly what you were requesting:
DECLARE @order TABLE (OrderNbr int);
DECLARE @dept TABLE (Dept CHAR(1), Person varchar(3));
INSERT @order VALUES (1),(2),(3),(4),(5),(6);
INSERT @dept VALUES
('A','P1'),
('A','P2'),
('B','P3'),
('B','P4'),
('B','P5'),
('C','P6'),
('C','P7'),
('C','P8'),
('C','P9');
SELECT d.Dept, Person, OrderNbr
FROM @order
CROSS APPLY @dept d
CROSS APPLY
(
SELECT dept, x = COUNT(*)
FROM @dept
GROUP BY dept
) xx
WHERE xx.Dept = d.Dept
AND RIGHT(person,1)%x = OrderNbr%x
ORDER BY d.Dept, OrderNbr;
I have not had an opportunity to review the most recent comments, DDL and Sample Data.
Edit: minor formatting change in sample code.
-- Itzik Ben-Gan 2001
May 29, 2015 at 10:06 am
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.
May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.
To you it isn't random. But from where I sit there is no rhyme or reason from the limited data. Why is person A-P1 assigned to work in the first time slot? And why are there also people from each department in that time slot? I think the problem is that you have simplified the logic so much to post it that I can't see the details of what you are trying to do.
Is this simply assign the first person from each department to each time slot and keep the rotation so that it cycles through the list? I guess for ordering we can just use Person?
_______________________________________________________________
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/
May 29, 2015 at 10:11 am
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.
May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.
To you it isn't random. But from where I sit there is no rhyme or reason from the limited data. Why is person A-P1 assigned to work in the first time slot? And why are there also people from each department in that time slot? I think the problem is that you have simplified the logic so much to post it that I can't see the details of what you are trying to do.
Is this simply assign the first person from each department to each time slot and keep the rotation so that it cycles through the list? I guess for ordering we can just use Person?
You are absolutely correct,!! We can have another column "Order" which dictates whom to pick up first and which sequence.
May 29, 2015 at 10:18 am
A solution from a guy:
SELECT DeptName, PersonName, StartDate, EndDate
FROM (
SELECT DeptName, PersonName,
ROW_NUMBER() OVER (PARTITION BY DeptName
ORDER BY PersonName) AS Rotation,
COUNT(*) OVER (PARTITION BY DeptName) AS PersonCount
FROM Department
) t
CROSS JOIN
(SELECT StartDate, EndDate, ROW_NUMBER() OVER (ORDER BY StartDate) AS DrivingOrder
FROM Roster) AS temp
WHERE Rotation = (DrivingOrder - 1) % PersonCount + 1
ORDER BY DeptName, StartDate
May 29, 2015 at 10:20 am
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.
May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.
To you it isn't random. But from where I sit there is no rhyme or reason from the limited data. Why is person A-P1 assigned to work in the first time slot? And why are there also people from each department in that time slot? I think the problem is that you have simplified the logic so much to post it that I can't see the details of what you are trying to do.
Is this simply assign the first person from each department to each time slot and keep the rotation so that it cycles through the list? I guess for ordering we can just use Person?
Sean - I have not had a chance to look at the new data but take a look at what I posted. I think I understand what the OP wants (though they're not explaining it clearly). The method I used to get him his results is obviously not very scientific but it get's them the distribution they're looking for. E.g. 6 orders, 2 people in dept A, 1/2 the orders are going to person 1, the other 1/2 to person 2. 3 people in dept B - 1/3 of the orders going to each. 4 people in the group C, orders divided as evenly as possible with the the orders divided as evenly as possible (kinda NTILE-esk)... If you added a row-number over partition by each group it would replace how I'm using the RIGHT() function to get the number (I just used that because it worked).
Long story-short, dividing the 6 orders as evenly possible to each member of each group (dept). I could be totally wrong (would not be the first time), just guessing really - but it's kind of a fun little problem if I understand it correctly. I want to come back to this but I'm late to a meeting :hehe:
-- Itzik Ben-Gan 2001
May 29, 2015 at 11:14 am
Alan.B (5/29/2015)
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
sarath.tata (5/29/2015)
Sean Lange (5/29/2015)
Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.Thanks Sean for your comment. I have rephrased my original post now.
Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.
well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.
May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.
To you it isn't random. But from where I sit there is no rhyme or reason from the limited data. Why is person A-P1 assigned to work in the first time slot? And why are there also people from each department in that time slot? I think the problem is that you have simplified the logic so much to post it that I can't see the details of what you are trying to do.
Is this simply assign the first person from each department to each time slot and keep the rotation so that it cycles through the list? I guess for ordering we can just use Person?
Sean - I have not had a chance to look at the new data but take a look at what I posted. I think I understand what the OP wants (though they're not explaining it clearly). The method I used to get him his results is obviously not very scientific but it get's them the distribution they're looking for. E.g. 6 orders, 2 people in dept A, 1/2 the orders are going to person 1, the other 1/2 to person 2. 3 people in dept B - 1/3 of the orders going to each. 4 people in the group C, orders divided as evenly as possible with the the orders divided as evenly as possible (kinda NTILE-esk)... If you added a row-number over partition by each group it would replace how I'm using the RIGHT() function to get the number (I just used that because it worked).
Long story-short, dividing the 6 orders as evenly possible to each member of each group (dept). I could be totally wrong (would not be the first time), just guessing really - but it's kind of a fun little problem if I understand it correctly. I want to come back to this but I'm late to a meeting :hehe:
Alan somehow I managed to miss your post in the middle there. Nice job interpreting the requirement. I am a bit dense and as such it took me a few more iterations before I understood the requirements.
_______________________________________________________________
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/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply