October 28, 2015 at 8:33 am
I have a person table
CREATE TABLE Person
(
PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int
)
Assume I have data like this
INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(2, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(3, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(4, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(5, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(6, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(7, 'Ray', '01/23/2012', 0, 0)
I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.
I have tried writing various conditions but couldn't achieve a query that meets all my requirements here. Any pointers in the right direction would be helpful for me. Thanks
Note: I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).
October 28, 2015 at 8:55 am
sarath.tata (10/28/2015)
I have a person tableCREATE TABLE Person
(
PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int
)
Assume I have data like this
INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(1, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(1, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(1, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(1, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(1, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(1, 'Ray', '01/23/2012', 0, 0)
I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.
I have tried writing various conditions but couldn't achieve a query that meets all my requirements here. Any pointers in the right direction would be helpful for me. Thanks
Note: I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).
Can you explain your meaning of "next". Your first example doesn't make sense unless you are trying to use the "order" of the rows in the table. I hope that isn't the case because a table by definition is an unordered set.
Let's look at your examples closely:
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
The only way this works is if you mean the order of the table. This is actually impossible and you need to define the order.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
This one works because of the HireOrder but you mention AltOrder. The values for AltOrder are all 0 so any ordering on that column is pointless.
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
Your logic here is also flawed. You might be Cris but you might also get Jen. They both have the same HireDate, same HireOrder and same AltOrder.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
This is just plain bizarre. If there are no rows meeting your logic you want the earliest?
All in all the queries here are incredibly simple. What is however impossible is producing the expected results because as posted the business rules are not complete.
_______________________________________________________________
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 28, 2015 at 9:13 am
So many questions... This could be achieved with this query... The mistake i did was putting same number in person id for everyone (copy / paste issue). There can never be two persons with the same HireDate, HireOrder and AltOrder. These columns are defined to ensure seniority is maintained in the system. These three columns combined provide seniority of the person.
select top 1 p.*
from person p
where (p.hiredate > @hiredate) or
(p.hiredate = @hiredate and p.hireorder > @hireorder) or
(p.hiredate = @hiredate and p.hireorder = @hireorder and p.altorder > @altorder)
order by hiredate, hireorder, altorder;
October 28, 2015 at 9:50 am
sarath.tata (10/28/2015)
So many questions... This could be achieved with this query... The mistake i did was putting same number in person id for everyone (copy / paste issue). There can never be two persons with the same HireDate, HireOrder and AltOrder. These columns are defined to ensure seniority is maintained in the system. These three columns combined provide seniority of the person.select top 1 p.*
from person p
where p.hiredate > @hiredate or
p.hiredate = @hiredate and p.hireorder > @hireorder or
p.hiredate = @hiredate and p.hireorder = @hireorder and p.altorder > @altorder
order by hiredate, hireorder, altorder;
You are missing parenthesis all over the place here and your logic is very confusing. Couldn't you simplify the where predicates to this?
where p.hiredate >= @hiredate
and p.hireorder >= @hireorder
and p.altorder >= @altorder
_______________________________________________________________
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 28, 2015 at 11:01 am
Sean Lange (10/28/2015)
sarath.tata (10/28/2015)
So many questions... This could be achieved with this query... The mistake i did was putting same number in person id for everyone (copy / paste issue). There can never be two persons with the same HireDate, HireOrder and AltOrder. These columns are defined to ensure seniority is maintained in the system. These three columns combined provide seniority of the person.select top 1 p.*
from person p
where p.hiredate > @hiredate or
p.hiredate = @hiredate and p.hireorder > @hireorder or
p.hiredate = @hiredate and p.hireorder = @hireorder and p.altorder > @altorder
order by hiredate, hireorder, altorder;
You are missing parenthesis all over the place here and your logic is very confusing. Couldn't you simplify the where predicates to this?
where p.hiredate >= @hiredate
and p.hireorder >= @hireorder
and p.altorder >= @altorder
Yes, I changed answer to add parenthesis.
I can't use this simple query in certain scenarios. I know it is little bit complex to explain why but I'm sure you would understand if you get a chance to see the output.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply