March 28, 2013 at 7:37 am
astrid 69000 (3/28/2013)
Thanks for your reply but that is not it 🙂i need to divide the people to fit in an elevator, and the total amount of weight on the elevator cannot be over 200
and i need to group them by age
without killing anyone like i did :w00t:
Let me know if you want me to post the solution I came up with. I didn't want to just toss it there because...
my ego cannot take it if someone will solve it
_______________________________________________________________
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/
March 28, 2013 at 7:39 am
good morning, i would love to see your query, did you see mine?
i was sweet enough to butcher people to fit in the elevator hahahahah
March 28, 2013 at 7:51 am
I did like you chopping people into pieces. 😀
Here is what I came up with. I use the quirky update method to create a running total. Once you have the running total it is pretty straight foward. You should read this article[/url] which explains the quirky update. There are some rules you have to follow for it tow work correctly.
Because of the running total I had to make a couple of changes to the #Person table so this is my entire script.
if object_id('tempdb..#Person') is not null
drop table #Person
create table #Person
(
PersonId int identity(1,1),
PersonName nvarchar(100),
PersonHeight int,
PersonWeight int,
PersonBorn datetime
)
create clustered index IX_Person on #Person (PersonBorn) --This is important for the quirky update to work correctly
insert into #Person(PersonName, PersonHeight, PersonWeight, PersonBorn)
values ('Loskov', 180, 80, '1988-01-19'),
('Sychev', 190, 88, '1966-05-15'),
('Evseev', 174, 91, '1974-12-29'),
('Maminov', 166, 70, '1981-09-05'),
('Orlov', 176, 89, '1972-12-29')
alter table #Person add RunningTotal int
declare @RunningTotal int = 0
select * from #Person order by PersonBorn
update #Person set @RunningTotal = RunningTotal = @RunningTotal + PersonWeight
from #Person with (TABLOCKX)
OPTION (MAXDOP 1)
select *, cast(RunningTotal / 200. as int) + 1 as GroupNumber from #Person order by RunningTotal
_______________________________________________________________
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/
March 28, 2013 at 8:25 am
of course that works and i was nowhere close to the answer :w00t:
but of course it worked 😀
now the million dollar question... the way i did it, how do i prevent people from being cut in the middle cause of their weight? 😛
and diet is not the answer :-D:-D:-D
March 28, 2013 at 8:37 am
astrid 69000 (3/28/2013)
of course that works and i was nowhere close to the answer :w00t:but of course it worked 😀
now the million dollar question... the way i did it, how do i prevent people from being cut in the middle cause of their weight? 😛
and diet is not the answer :-D:-D:-D
You just need to aggregate your results. It was close.
SELECT max(Elevator#), PersonID, PersonBorn, count(*) Count_Person
FROM (
SELECT PersonId, t.PersonBorn, ((row_number() over (order by t.PersonBorn, s.n) - 1) / 200) + 1 as Elevator#
FROM #Person t
INNER JOIN #numseq s
ON t.PersonWeight >= s.n
) a
GROUP BY PersonID, PersonBorn
ORDER BY max(Elevator#), PersonID, PersonBorn
_______________________________________________________________
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/
March 28, 2013 at 8:44 am
thanks so much!!!!!
and thanks for the article, i will also read it.
now people can ride safe on the elevator thanks to you 😎
March 28, 2013 at 9:37 am
Bad news :crying:, it doesn’t work in every scenario. Neither of the queries. I tried changing the numbers and it only worked some times.
It divides the total weight by lift (at least it doesn't butcher people like me :Whistling:) but I got back lifts who were weighting more than 200. :w00t:
So what are we doing that is not working? apparently it is a much more complex issue than i thought :w00t::w00t::w00t:
March 28, 2013 at 9:39 am
astrid 69000 (3/28/2013)
Bad news :crying:, it doesn’t work in every scenario. Neither of the queries. I tried changing the numbers and it only worked some times.It divides the total weight by lift (at least it doesn't butcher people like me :Whistling:) but I got back lifts who were weighting more than 200. :w00t:
So what are we doing that is not working? apparently it is a much more complex issue than i thought :w00t::w00t::w00t:
Can you post the sample data that doesn't work. That would certainly make it easier for me to see. 😛
_______________________________________________________________
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/
March 28, 2013 at 9:47 am
we tried different weight scenarios)
this one didnt work on one.
insert into #Person(PersonName, PersonHeight, PersonWeight, PersonBorn)
values ('Loskov', 180, 150, '1988-01-19'),
('Sychev', 190, 150, '1966-05-15'),
('Evseev', 174, 150, '1974-12-29'),
('Maminov', 166, 150, '1981-09-05'),
('Orlov', 176, 150, '1972-12-29')
and this one also didnt work :w00t:
values ('Loskov', 180, 88, '1988-01-19'),
('Sychev', 190, 89, '1966-05-15'),
('Evseev', 174, 199, '1974-12-29'),
('Maminov', 166, 1, '1981-09-05'),
('Orlov', 176, 2, '1972-12-29')
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply