Update Row with Combination of Values
-
Hello Forum Gurus!
Stuck coming up with a usefully simple solution to an update problem.
Table has rows with time-off codes.
Business has comibined Sick and Vacation.
Business decided (poorly) to reuse the Vacation code as the new "combined" code.
Need:
1) When both codes exist for an employee, combine the existing Sick and Vacation code row values into the Vacation code row values and "zero" out the Sick code row values.
2) When only the Sick code exists for an employee, create a new Vacation code row and move all Sick code values to the new Vacation code row values and finally "zero" out the Sick code row values.
3) <Obviously> When only the Vacation code exists for an employee, do nothing.
Pertinent Info:
After limiting selection to just the Sick and Vacation codes for employees in a given year less than 3500 rows will be involved in this update.
Data Examples:
EmployeeName Code CodeDesc Jan Feb
JohnGinglehim 45 Sick 16 0
JohnGinglehim 50 Vac 0 8
HerrSchmidt 45 Sick 0 8
MasterJohns 50 Vac 8 0
Desired Result:
EmployeeName Code CodeDesc Jan Feb
JohnGinglehim 50 SickVac 16 8
HerrSchmidt 50 SickVac 0 8
MasterJohns 50 SickVac 8 0
THANKS in advance for any assistance you can promptly provide!!
-
Is this the kind of thing you are after?
create table #temp (name varchar(50), Code varchar(50), CodeDesc varchar(50), Jan int,Feb int)
set nocount on
insert into #temp values ('JohnGinglehim', '45','Sick', 16, 0)
insert into #temp values ('JohnGinglehim', '50','Vac', 0, 8)
insert into #temp values ('HerrSchmidt', '45','Sick', 0, 8)
insert into #temp values ('MasterJohns', '50','Vac', 8, 0)
insert into #temp
select name,
'50' as Code,
'SickVac' as codedsc,
sum(Jan) as Jan,
sum(Feb) as Feb
from #temp
where CodeDesc = 'Vac' or CodeDesc = 'Sick'
group by name
delete from #temp where CodeDesc = 'Sick' or CodeDesc = 'Vac'
-- or
-- update #temp set jan = 0, Feb = 0 where CodeDesc = 'Sick' or CodeDesc = 'Vac'
select * from #temp
drop table #temp
-
Thanks DavidT.
Your solution has pointed out one minor problem in my Data Description.
The Sick, Vac, and SickVac codes are NOT in the physical table (I only used them for illustrative purposes to describe the real physical code values 45 and 50).
This is important in that only the Code 50 will have values after the updates.
Thus ALL Code 45 row values will need to be "zeroed" and ALL existing code 50 row values will need to be updated with the combined Code 45 and 50 row values.
Should only a Code 45 row exist then the values in that row need to be "copied" into a new Code 50 row (and the Code 45 row then needs to be "zeroed").
Sorry for any confusion this may have caused.
Thanks for the quick reply and assistance.
Would anyone have additional insights given this clarification?
-
ok... Not finished yet
How about the following.
If I am still oversimplifying things, then I appologise, and I will get back to my own work!!!
(Shame all the formatting / tabbing will be lost when posted)
create table #temp (name varchar(50), Code varchar(50), CodeDesc varchar(50), Jan int,Feb int)
set nocount on
insert into #temp values ('JohnGinglehim', '45','Sick', 16, 0)
insert into #temp values ('JohnGinglehim', '50','Vac', 0, 8)
insert into #temp values ('HerrSchmidt', '45','Sick', 0, 8)
insert into #temp values ('MasterJohns', '50','Vac', 8, 0)
declare @Table table (Ident int identity,name varchar(50)) -- providing all your names are unique, perhaps this shoul dbe an employee number or something
insert into @Table (Name)
select distinct name from #temp
declare @C int
declare @max-2 int
declare @Name varchar(50)
set @C = 1
set @max-2 = (select max(ident) from @Table)
begin
set @Name = (select name from @Table where Ident = @C)
if exists (select * from #temp where Code = 50 and Name = @Name)
begin
update #temp
set Jan = (select sum(Jan) from #temp where name = @Name and Code in(45,50)),
Feb = (select sum(Feb) from #temp where name = @Name and Code in(45,50)),
CodeDesc = 'SickVac' --- i know, not essential
where name = @Name and Code = 50
update #temp set Jan = 0, Feb = 0 where name = @Name and Code =45
end
else if exists(select * from #temp where Code = 45 and Name = @Name)
begin
insert into #Temp (name, code,CodeDesc, jan, feb)
select @Name,
'50',
'SickVac', --- i know, not essential
Jan ,
Feb
from #temp
where name = @Name and Code = 45
update #temp set Jan = 0, Feb = 0 where name = @Name and Code =45
end
else
Print 'What if not Sick code..?'
end
select * from #temp
drop table #temp
Cheers
David
-
quote:
(Shame all the formatting / tabbing will be lost when posted)I had the same issue - if you use the 'Insert Code' button or just type the html-like tags in manuallly then it retains your tabbing etc.
-
This should be easy to do. It's essentially just three statements.
UPDATE TimeOff
SET Code = 50
WHERE Code = 45
AND NOT EXISTS
(SELECT *
FROM TimeOff t
WHERE EmployeeName = TimeOff.EmployeeName
AND Code = 50)
UPDATE t
SET Jan = s.Jan, Feb = s.Feb
FROM TimeOff t JOIN
(SELECT EmployeeName, SUM(Jan) Jan, SUM(Feb) Feb
FROM TimeOff
WHERE Code IN (45,50)
GROUP BY EmployeeName) s
ON s.EmployeeName = t.EmployeeName
WHERE t.Code = 50
DELETE TimeOff
WHERE Code = 45--Jonathan
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply