March 27, 2013 at 12:36 pm
hi,
i need to group people who weight more less than 200 ordered by age (from the oldest to the youngest), and the result need to work with bigger tables.
how can i build a case?, this is the data.
my major problem is that i dont know how to build a case that once we have the first group of people will jump to the next person.
create table #Person (
PersonId int identity(1,1),
PersonName nvarchar(100),
PersonHeight int,
PersonWeight int,
PersonBorn datetime
)
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'),
thanks!
March 27, 2013 at 12:39 pm
astrid 69000 (3/27/2013)
hi,i need to group people who weight more less than 200 ordered by age (from the oldest to the youngest), and the result need to work with bigger tables.
how can i build a case?, this is the data.
my major problem is that i dont know how to build a case that once we have the first group of people will jump to the next person.
create table #Person (
PersonId int identity(1,1),
PersonName nvarchar(100),
PersonHeight int,
PersonWeight int,
PersonBorn datetime
)
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'),
thanks!
Your sample data sure has light weight people. 😛
Is it as simple as this??
Where PersonWeight < 200
Order by 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 27, 2013 at 1:26 pm
lol,
no no, that is not what i need
i need to run a query that will take the people and group them, in groups
each group cannot weight all together more than 200 and the people need to be ordered in age from the older to the younger 😀
is not that simple :w00t:
March 27, 2013 at 1:44 pm
astrid 69000 (3/27/2013)
lol,no no, that is not what i need
i need to run a query that will take the people and group them, in groups
each group cannot weight all together more than 200 and the people need to be ordered in age from the older to the younger 😀
is not that simple :w00t:
Are you trying to group people to as close to 200 in each group? If so, then this is the age old packing problem in computers.
http://en.wikipedia.org/wiki/Bin_packing_problem
If not, then can you please post ddl and sample data in addition to desired output? The first article in my signature is a great place to start to see what you should post in order to get the best help on the forums.
_______________________________________________________________
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 27, 2013 at 1:56 pm
i never heard of the bin problem lol. but i will look into it 😀
i am trying to learn sql and i got asked to look into that.
thanks!
March 27, 2013 at 1:59 pm
astrid 69000 (3/27/2013)
i never heard of the bin problem lol. but i will look into it 😀i am trying to learn sql and i got asked to look into that.
thanks!
If you just want to create groups up to 200 pounds ordered by age that is an entirely different animal.
_______________________________________________________________
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 27, 2013 at 2:18 pm
😀 well i am looking into the bin thingy and doesnt look simple, there is a 6 page article with many many things :w00t:
and i thought this problem was going to make me use some of the wonderful new things of sql 2012, which i barely know :w00t:
here is what i was told, to group the people (we are speaking about young children, not really skinny people lol) to fit on an elevator, the groups cannot be over 200 pounds and it needs to be arrange by age from the oldest to the youngest...
i am so confused right now... :crying:
March 27, 2013 at 2:29 pm
I don't have time to work on it right now but I'm guessing you're going to need a (to pseudocode)
row_number() over (partition by rolling_total < 200 order by age) rownum
kind of thing
March 27, 2013 at 2:33 pm
astrid 69000 (3/27/2013)
😀 well i am looking into the bin thingy and doesnt look simple, there is a 6 page article with many many things :w00t:and i thought this problem was going to make me use some of the wonderful new things of sql 2012, which i barely know :w00t:
here is what i was told, to group the people (we are speaking about young children, not really skinny people lol) to fit on an elevator, the groups cannot be over 200 pounds and it needs to be arrange by age from the oldest to the youngest...
i am so confused right now... :crying:
I guess this depends on how the groups are put together.
If you just want to to list all the people by age oldest to youngest and then partition them into groups that are up to 200 pounds it is entirely different than trying to figure out the best way to pack all those people into groups.
So given your sample data you would have three groups?
First group would contain Sychev and Orlov with a total of 177. Second group would be Evseev and Maminov with a total of 161 and the third group would be Loskov by themself?
This can be done. In order to do this you will need to use ROW_NUMBER and PARTITION BY.
Since you stated you are trying to learn sql I don't want to just give you the query. That won't teach you anything. I can think of a couple of ways to accomplish this. I would not classify this challenge as something great for learning sql. The concepts are probably more like mid level and are likely to only confuse a beginner.
If you want to tackle this try posting what you think needs to happen.
_______________________________________________________________
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 27, 2013 at 2:53 pm
well i know more than just a little 🙂
and i dont want the result, i just want to understand, my ego cannot take it if someone will solve it :-D:-D
but basically i know how to write the row thingy but
here are my questions :ermm:
i cannot partition by name cause every row has a name.
i have no clue how to make a partition where i can add a value ">=200"
and i was thinking i can group using a case but then again, that looks even more complicated.
March 27, 2013 at 3:20 pm
astrid 69000 (3/27/2013)
well i know more than just a little 🙂and i dont want the result, i just want to understand, my ego cannot take it if someone will solve it :-D:-D
but basically i know how to write the row thingy but
here are my questions :ermm:
i cannot partition by name cause every row has a name.
i have no clue how to make a partition where i can add a value ">=200"
and i was thinking i can group using a case but then again, that looks even more complicated.
I came up with a solution that doesn't use row_number. It does however use the quirky update as a running total and some modulus math.
_______________________________________________________________
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 12:13 am
ok... it is official i am completely lost :w00t:
but i haven't had my coffee yet, therefore after i get a dose of caffeine, i will get to it 🙂
March 28, 2013 at 2:52 am
ok, so now i am after my coffee
i found a temporary solution to the problem but i cuts people :w00t:
keeping in mind that i am all about peace and love, how can i tell the query, don't cut someone in half and move him to the next elevator 😀
CREATE TABLE #numseq (n INT NOT NULL IDENTITY) ;
GO
INSERT #numseq DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 1000 INSERT #numseq DEFAULT VALUES ;
GO
SELECT Elevator#, PersonBorn, count(*) Count_Person
INTO #result
FROM (
SELECT 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 Elevator#, PersonBorn
ORDER BY Elevator#, PersonBorn
March 28, 2013 at 3:16 am
I may have mis-understood what you are after and this is by no means the most efficient but I think this may be what you are after
DECLARE @WeightValue SMALLINT = 90 --Set your weight grouping value (groups over or under)
SELECT * ,
Age = DATEDIFF(YY, PersonBorn, GETDATE()) --Get the persons age in years
,CASE WHEN PersonWeight > @WeightValue THEN 'Over'
ELSE 'Less'
END AS [GROUP BY]
FROM #Person
ORDER BY [GROUP BY] ,
Age
You could use temp tables and indexes to speed the query up for larger data sets
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 28, 2013 at 3:21 am
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:
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply