June 29, 2010 at 1:13 pm
I'm working on a problem trying to update field payrtamt in following table:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[UPR00400](
[EMPLOYID] [char](15) NOT NULL,
[PAYRCORD] [char](7) NOT NULL,
[INACTIVE] [tinyint] NOT NULL,
[PAYTYPE] [smallint] NOT NULL,
[BSPAYRCD] [char](7) NOT NULL,
[PAYRTAMT] [numeric](19, 5) NOT NULL,
[PAYUNIT] [char](25) NOT NULL,
[PAYUNPER] [smallint] NOT NULL,
[RPTASWGS] [tinyint] NOT NULL,
[TAXABLE] [tinyint] NOT NULL,
[SBJTFDTX] [tinyint] NOT NULL,
[SBJTSSEC] [tinyint] NOT NULL,
[SBJTMCAR] [tinyint] NOT NULL,
[SBJTSTTX] [tinyint] NOT NULL,
[SBJTLTAX] [tinyint] NOT NULL,
[SBJTFUTA] [tinyint] NOT NULL,
[SBJTSUTA] [tinyint] NOT NULL,
[SUTASTAT] [char](3) NOT NULL,
[FFEDTXRT] [smallint] NOT NULL,
[FLSTTXRT] [smallint] NOT NULL,
[PAYPEROD] [smallint] NOT NULL,
[PAYPRPRD] [numeric](19, 5) NOT NULL,
[MXPYPPER] [numeric](19, 5) NOT NULL,
[TipType] [smallint] NOT NULL,
[PAYADVNC] [numeric](19, 5) NOT NULL,
[ACRUVACN] [tinyint] NOT NULL,
[ACRUSTIM] [tinyint] NOT NULL,
[WRKRCOMP] [char](7) NOT NULL,
[W2BXNMBR] [smallint] NOT NULL,
[W2BXLABL] [char](7) NOT NULL,
[PYADVTKN] [numeric](19, 5) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[DATAENTDFLT] [tinyint] NOT NULL,
[SHFTCODE] [char](7) NOT NULL,
[PAYFACTR] [numeric](19, 5) NOT NULL,
[BSDONRTE] [numeric](19, 5) NOT NULL,
[PYSTPTBLID] [char](15) NOT NULL,
[Base_Step_Increased_On] [smallint] NOT NULL,
[Step] [smallint] NOT NULL,
[Step_Effective_Date] [datetime] NOT NULL,
[Step_FTE] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKUPR00400] PRIMARY KEY NONCLUSTERED
(
[EMPLOYID] ASC,
[PAYRCORD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Each EMPLOYID has as many as 15 rows each with a different PAYRCORD (same group of PAYRCORDs is used for other employees). PAYRTAMT is filled in for some PAYRCORD but not others. PAYRTAMT is based on BSDONRTE * PAYFACTR.
Application was changed and now PAYRTAMT is blank for some PAYRCORDs; also PAYFACTR is blank. I corrected PAYFACTR for all and now need way to correct BSDONRTE so I can multiple PAYFACTR * BSDONRTE to get PAYRTAMT. Generally only one PAYCORD for each EMPLOYID has the BSDONRTE filled in, rest are blank. If I could find way to update BSDONRTE so it is filled in for each PAYRCORD then I would be set.
I tried using following code but it sets all BSDONRTEs to 0 instead of to correct value. Also BSDONRTE is different between EMPLOYIDs.
UPDATE P1
SET
P1.BSDONRTE = P2.PAYRTAMT
FROM UPR00400 P1
JOIN UPR00400 P2
ON P1.EMPLOYID = P2.EMPLOYID
WHERE P2.BSDONRTE <> 0.0
Any ideas on how to fix this update?
Thanks
June 29, 2010 at 2:44 pm
Dave, can you add about a dozen rows of data for us to test with? (Do it in the form of insert statements!)
It looks like you're pretty close, but we're going to need some data to work with to see what's going on and how to make it work correctly.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2010 at 9:27 am
Wayne,
Here's the data (insert statements) you requested. I only supplied data for the fields that are applicable as the rest of the fields have no impact.
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (111111, HH, SL, 1, 7.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (111156, HF, HR, 1.5, 8.0)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (112221, HH, EX, 1, 8.25)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (221111, HH, SL, 1, 7.75)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (123111, HO, HR, 1.5, 7.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (222222, HO, SL, 1, 9.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (333331, HH, HR, 1.5, 7.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (133333, HH, SL, 1, 8.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (333111, HF, SL, 1, 10.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (444411, HH, HR, 1.5, 7.5)
INSERT INTO [Test].[dbo].[UPR00400] (EMPLOYID, PAYRCORD, BSPAYRCD, PAYFACTR, BSDONRTE)
Values (442211, HH, HR, 1.0, 9.0)
Thanks
Dave
June 30, 2010 at 10:19 am
Dave, please test the script you post before posting it.
It doesn't work against DDL submitted in your previous post (it doesn't work at all as there are no quotes around string values).
Actually, the dataset you posted does not contain cases for update. Each row has unique EMPLYID and BSDONRTE populated.
However the following should work:
;WITH cteWR
AS
(
SELECT EMPLOYID, MAX(BSDONRTE) AS WantedRTE
FROM UPR00400 M
WHERE BSDONRTE != 0.0
GROUP BY EMPLOYID
)
UPDATE UPR00400 SET BSDONRTE = wr.WantedRTE
FROM UPR00400 U
JOIN cteWR wr ON wr.EMPLOYID = U.EMPLOYID
WHERE U.BSDONRTE = 0.0 --?
I'am not sure which BSDONRTE you want to use if you have more than one record for the same EMPLOYID with BSDONRTE populated. I have choosen MAX.
June 30, 2010 at 11:08 am
Eugene,
Sorry, I was in a rush and forgot about the string issue. The BSDONRTE is normally 0 except one row for each EMPLOYID. My goal is to make each BSDONRTE equal to the value of the one that is not 0 for each EMPLOYID.
June 30, 2010 at 12:00 pm
Eugene,
Your CTE code worked very well. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply