February 9, 2016 at 4:36 pm
I am working on the stored procedure below but running into issues. My issue is that if the [breed] column has the same value in the table more than specified based on a variable, I need to update the table with another breed value from the same table that is listed in the table < the specified amount of times. For example, 'pitbull' is in the table 6 times and 'boxer' is in the table 4 times. They both need to be listed in the database 5 times each based on the specified value of 'BreedListed' that is = 5. I am attempting to write an update query to update the [breed] value so all [breeds] will be listed 5 times. Thanks for input.
create table #TempCanine (ID INT IDENTITY(1,1), AppID INT, Breed varchar(8), age INT)
insert for sample data
insert into #TempCanine values(101, 'Pitbull', 2)
insert into #TempCanine values(102, 'Boxer', 4)
insert into #TempCanine values(103, 'Pitbull', 1)
insert into #TempCanine values(104, 'Pitbull', 5)
insert into #TempCanine values(105, 'Boxer', 2)
insert into #TempCanine values(106, 'Pitbull', 2)
insert into #TempCanine values(107, 'Boxer', 8)
insert into #TempCanine values(108, 'Pitbull', 1)
insert into #TempCanine values(109, 'Pitbull', 3)
insert into #TempCanine values(110, 'Boxer', 8)
The update query is a struggle (I am a SQL newbie)
DECLARE @Counter INT
DECLARE @BreedListed int
DECLARE @GetBreed varchar(50)
SET @BreedListed = 5
SET @Counter = 1
--loop through all records in database
WHILE @COUNTER <= (SELECT COUNT(*) FROM #TempCanine) BEGIN
--select breed value that is over the limit
SELECT @GetBreed = breed
FROM #TempCanine
WHERE ID = @Counter AND COUNT(breed) > @BreedListed
GROUP BY Breed
--LOST
IF @GetBreed IS NOT NULL
BEGIN
UPDATE #TempCanine
SET T.breed = (SELECT breed
FROM #TempCanine T INNER JOIN #TempCanine C
---LOST HERE
END
February 9, 2016 at 8:06 pm
Thanks for the create table/insert scripts. That helps a lot. One more thing, though. Could you post your expected output? Is the age of the dog significant somehow? Or are you just trying to level the groups out?
February 9, 2016 at 11:02 pm
I am posting this as A solution, not as THE solution. I am hoping to learn from a critique of the answer as much as you are!
Deleting is easy. The problem is knowing which one to delete. This code will delete the largest AppID.
To delete:
with cte as
(
Select AppID, Breed,
Row_Number() over(Partition by breed order by AppID) RowNum
from #TempCanine
)
Delete from cte where RowNum > 5
Inserting, as you discovered, is harder. A big problem with Inserts is that we do not know the age of the dog. Without something to tell us what it is, I put it at 0. The other problem is how to enter an unknown number of rows. This is where the tally table is very valuable. The important part of the tally table is that by joining against it and using a correct where clause, it will return the number of rows that need to be inserted. As a side note, there are a number of ways to generate a tally table. What I have below is to keep the solution cleaner without cluttering it with a dynamic one. The last issue is generating an ascending appID regardless of the breed.
create table #tally
(
n int
)
insert #tally (n) values (1), (2), (3), (4), (5), (6)
with cte as
(
Select count(*) BreedCnt, Breed
from #TempCanine
group by breed
),
MaxID as
(
Select 1 i, MAX(AppID) AppID from #tempCanine
)
insert #TempCanine (breed, age, AppID)
select c.breed, 0, m.AppID + row_Number() over(order by (select null))
from #tally t
inner join maxID m on m.i = 1
cross apply
(Select Breed, BreedCnt
from cte) c
where t.N > c.BreedCnt and t.N < 6
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2016 at 6:37 am
The age column is not important. Just need to find a way to level the groups out.
February 10, 2016 at 6:40 am
I am attempting to update the temp table not delete the records or insert new ones.
February 10, 2016 at 7:29 am
For example, 'pitbull' is in the table 6 times and 'boxer' is in the table 4 times. They both need to be listed in the database 5 times each based on the specified value of 'BreedListed' that is = 5. I am attempting to write an update query to update the [breed] value so all [breeds] will be listed 5 times.
Your description sure makes it sound like you want only 5 rows per breed. There is no column in your temp table that can be updated to 5. As it stands now, your table ddl does not match your narrative.
As was previously requested, expected output would be very helpful.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2016 at 7:38 am
Original data
AppIDBreedage
101Pitbull2
102Boxer4
103Pitbull1
104Pitbull5
105Boxer2
106Pitbull2
107Boxer8
108Pitbull1
109Pitbull3
110Boxer8
Expected output
AppIDBreedage
101Pitbull2
102Boxer4
103Pitbull1
104Pitbull5
105Boxer2
106Pitbull2
107Boxer8
108Pitbull1
109Boxer 3
110Boxer8
February 10, 2016 at 8:04 am
Raxter (2/10/2016)
Original data
AppIDBreedage
101Pitbull2
102Boxer4
103Pitbull1
104Pitbull5
105Boxer2
106Pitbull2
107Boxer8
108Pitbull1
109Pitbull3
110Boxer8
Expected output
AppIDBreedage
101Pitbull2
102Boxer4
103Pitbull1
104Pitbull5
105Boxer2
106Pitbull2
107Boxer8
108Pitbull1
109Boxer 3
110Boxer8
This raises more questions.
Your example has 2 breeds. What happens when there are more?
How do you relate Pitbulls to Boxers? Or any one breed to another?
What do you do when it is not a multiple of 5? What happens when you have 4 Boxers and 4 Pitbulls?
What is the purpose of age if the name of the breed changes?
How is AppID used? Can you just change the label of what it is pointing to without ramifications to other tables?
Is it possible to re-think the design?
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 23, 2016 at 3:36 pm
This sounds poorly thought out. Why would you want to arbitrarily change the breed of a dog?
That being said, I came up with something using NTILE() which splits a set up into the given number of groups as evenly as possible, which sounds like exactly what you are looking for.
;
WITH breed_updates AS (
SELECT *, CASE WHEN NTILE(2) OVER(ORDER BY Breed, AppID DESC) = 1 THEN 'Boxer' ELSE 'Pitbull' END AS new_breed
FROM #TempCanine
)
UPDATE breed_updates
SET Breed = new_breed
SELECT *
FROM #TempCanine
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 24, 2016 at 5:01 pm
They both need to be listed in the database 5 times each based on the specified value of 'BreedListed' that is = 5
Why not both be 7, or three? What is so special about five and that this data set conveniently had six and four dogs across the two breeds (which average 5)? I dont understand the business problem being solved. Are you a pet shop and you need to buy a boxer if a pitbull gets sold to keep an even equal number of breeds available?
----------------------------------------------------
February 25, 2016 at 8:21 am
This is dummy data. I can not post the actual production data. The idea is that if a certain value is in the table more than a certain number of times, it needs to be updated with one that is not. Thanks for all the input.
February 25, 2016 at 12:01 pm
Raxter (2/25/2016)
This is dummy data. I can not post the actual production data. The idea is that if a certain value is in the table more than a certain number of times, it needs to be updated with one that is not. Thanks for all the input.
It is not so much if this is production data or equivalent. The issue is understanding in detail the what and the why. That would help us to point out bugs in your approach if that were the case, rather than merely handing you a solution to a particular recordset of data you provide (while we dont understand the business case ) . How many breeds? How is this magic number (5) determined? When can it change at what existing data does that impact? Why do this at all to start with (the most important Q) ?
----------------------------------------------------
February 25, 2016 at 12:31 pm
I appreciate everyone's help and patience.
To begin I have temp table that has all the data (the dummy canine table). The breed was previously assigned to each record. I now Need to do a check and make sure that each breed (could be many different breeds) are only listed in the database a certain number of times. For this scenario, I have that cut off value as 5. The age and ID columns are not important because they have no relationship to the breed column. (My fault, I should not have named the column age because it imply a major connection).
The goal is to 'reassign' the breed to any record that has a count > 5 by using a breed with a count < 5.
For this example, I have 10 records in the database: 6 are a breed of 'pitbull' and 5 are a breed of 'boxer', how do I UPDATE any records with a breed value count > 5? The breed count can not be over the cutoff value. Does this make sense?
February 25, 2016 at 1:08 pm
That doesn't make sense. Breeds or colors.
How do you decide that item A (Pitbull) becomes Item B (Boxer)? I am guessing you assume that the ID is an order, and if you reach #6 of either item, you change it to the other. However, what if you already have 5 of item b?
If you're sure this isn't the case, this is easier. In that case, I assume you are on SQL 2008. That's more complex, but it can be done. Is this only two buckets of items?
February 25, 2016 at 1:28 pm
Steve Jones - SSC Editor (2/25/2016)
That doesn't make sense. Breeds or colors.How do you decide that item A (Pitbull) becomes Item B (Boxer)? I am guessing you assume that the ID is an order, and if you reach #6 of either item, you change it to the other. However, what if you already have 5 of item b?
If you're sure this isn't the case, this is easier. In that case, I assume you are on SQL 2008. That's more complex, but it can be done. Is this only two buckets of items?
Not sure what you mean by colors.
IF item A (Pitbull) is already in the table 5 times, then that breed is good and doesn't need to be updated. If 'boxer' is already in the table 5 times then it doesnt need to be updated. I am using SQL 2014.. The table can have many different breeds in the temp table. I need to update the breed column so no value is listed more than the cutoff value of 5. (this cutoff is actually a variable so it wont be a 5 - I am using 5 as an example).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply