July 23, 2008 at 1:04 pm
I have an interesting problem that I'm not sure how to tackle. I need to INSERT/UPDATE employee data into a table used for budgeting purposes. Every column is just fine with one exception. SCODE is a varchar(12) that needs to be auto-incremented for each employee for that particular property (HCODE is the property identifier). Should be easy but the values all start with an exclamation mark (!). I can't change this since this is vendor specified. The vendor would like our users to manually enter all of this information. We are trying to save them from their own mistakes by auto-populating and maintaining this data. Here is the table and some sample data:
USE [Budget]
GO
/****** Object: Table [dbo].[propbut_pr] Script Date: 07/23/2008 13:50:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[propbut_pr](
[HMY] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[HCODE] [numeric](18, 0) NULL,
[SCODE] [varchar](12) NULL,
[SNAME] [varchar](30) NULL,
[SGENLABEL] [varchar](30) NULL,
[STITLE] [varchar](40) NULL,
[SEMPLOYEE] [varchar](30) NULL,
[DHOURLYRATE] [numeric](18, 2) NULL,
[DBONUSPCT] [numeric](6, 2) NULL,
[DHOUSING] [numeric](18, 2) NULL,
[SMEDINS] [varchar](10) NULL,
[SWCCATEGORY] [varchar](10) NULL,
[DTSTART] [datetime] NULL,
[DTEND] [datetime] NULL,
[SOTLABEL] [varchar](30) NULL,
[IOTJAN] [numeric](4, 0) NULL,
[IOTFEB] [numeric](4, 0) NULL,
[IOTMAR] [numeric](4, 0) NULL,
[IOTAPR] [numeric](4, 0) NULL,
[IOTMAY] [numeric](4, 0) NULL,
[IOTJUN] [numeric](4, 0) NULL,
[IOTJUL] [numeric](4, 0) NULL,
[IOTAUG] [numeric](4, 0) NULL,
[IOTSEP] [numeric](4, 0) NULL,
[IOTOCT] [numeric](4, 0) NULL,
[IOTNOV] [numeric](4, 0) NULL,
[IOTDEC] [numeric](4, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [Budget].[dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (320,'!00001','Manager - 6210-0100','Kellie Bonner',29.00,'Associate','2009-01-01')
INSERT INTO [Budget].[dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (320,'!00002','Asst Manager - 6213-0100','Dayna Whitney',17.00,'Associate','2009-01-01')
INSERT INTO [Budget].[dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (320,'!00003','Maintenance - 6260-0000','Jose',25.00,'Maintenance','2009-01-01')
INSERT INTO [Budget].[dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (332,'!00001','Asst Manager - 6213-0100','John Doe',22.00,'Associate','2009-01-01')
INSERT INTO [Budget].[dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (332,'!00002','Maverick - 6218-0000','Sam Spade',13.50,'Associate','2009-01-01')
The only way my tired mind is coming up with is to use a cursor and manipulate a variable to write out to the field with each new record. For updating, that value will not change.
Any ideas?
July 23, 2008 at 3:10 pm
Well I don't have enough details to do anything exact, but assuming that they are only using 5 digits in that Varchar(12) field, then something like this ought to work:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Cast( (Select Max(Cast(Substring(SCODE, 2,12) as int))
From propbut_pr Where HCODE = @HCode)
+ 1000000001 as varchar(5))
, ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 8:51 pm
First of all this input data
INSERT INTO [dbo].[propbut_pr] ([HCODE],[SCODE],[STITLE],[SEMPLOYEE],[DHOURLYRATE],
[SWCCATEGORY],[DTSTART])
VALUES (320,'!00003','Maintenance - 6260-0000','Jose',25.00,'Maintenance','2009-01-01')
The SWCCATEGORY field was one character too large. Trivial I know.
Would suggest using something like:
Find the largest value for SCODE and then convert the last character to its ASCII equivalent
SELECT ASCII(SUBSTRING(SCODE,DATALENGTH(SCODE),1)),SUBSTRING(SCODE,2,11)
Remembering the ASCII decimal value goes from 48 to 57 that is character zero to character 9. To the value determined above add one test that it is less than or equal to 57, if it is convert back to a character using CHAR function. If it is greater than 57 that is it is already a 9, then check the next to right most character. Leave it to you as to using a loop or prehaps a Tally table as you move from right to left through the string
Then again you might consider the CAST function to convert all but the left most character to an integer, add 1 to that and test for value and then cast back as VARCHAR - thinking that this might be easier and quicker then converting to ASCII as per above.
July 24, 2008 at 6:56 am
bitbucket (7/23/2008)
The SWCCATEGORY field was one character too large. Trivial I know.
I caught that as well after posting. I'll have to let the person who sets up the tables know it needs to be increased.
Then again you might consider the CAST function to convert all but the left most character to an integer, add 1 to that and test for value and then cast back as VARCHAR - thinking that this might be easier and quicker then converting to ASCII as per above.
This is essentially what I did. The requirements changed shortly after I posted the original question as well. I only have to do an initial population of the table at the beginning of the budget process so no regular updating of the data. I decided to keep "playing" with this while waiting to hear back and came up with the following solution:
-- RUN FROM YARDI DATABASE!
DECLARE @hProp NUMERIC(18,0)
DECLARE @SCODE INT
DECLARE @SCODEOut VARCHAR(10)
DECLARE @STITLEOut VARCHAR(40)
DECLARE @SEMPLOYEEOut VARCHAR(30)
DECLARE @DHOURLYRATEOut NUMERIC(18,2)
DECLARE @SWCCATEGORYOut VARCHAR(10)
DECLARE @DTSTARTOut DATETIME
DECLARE PropCode CURSOR FOR
SELECT DISTINCT(p.HMY)
FROM PROPERTY AS p
INNER JOIN FWDWD.dbo.EmployeeData AS e ON p.SCODE = e.CostCenter
WHERE (ISNULL(e.TermDate,'') = '') OR (CAST(e.TermDate AS DATETIME) < CAST(e.RehireDate AS DATETIME))
ORDER BY p.HMY
OPEN PropCode
FETCH NEXT FROM PropCode INTO @hProp
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE EmployeeData CURSOR FOR
SELECT j.GLAccount AS STITLE,
e.EmployeeName AS SEMPLOYEE,
e.StdPay AS DHOURLYRATE,
j.WCCategory AS SWCCATEGORY,
CASE RehireDate
WHEN '' THEN CAST(e.HireDate AS DATETIME)
ELSE CAST(e.RehireDate AS DATETIME)
END AS DTSTART
FROM FWDWD.dbo.EmployeeData e
LEFT OUTER JOIN FWDWD.dbo.JobCodeBudgetInfo j ON e.JobCode = j.JobCode
INNER JOIN PROPERTY p ON e.CostCenter = p.SCODE
WHERE (p.HMY = @hProp) AND
((ISNULL(e.TermDate,'') = '') OR (CAST(e.TermDate AS DATETIME) < CAST(e.RehireDate AS DATETIME)))
ORDER BY SEMPLOYEE
SELECT @SCODE = ISNULL(MAX(CAST (RIGHT(SCODE,5) AS INT)),0)
FROM propbut_pr
WHERE HCODE = @hProp
OPEN EmployeeData
FETCH NEXT FROM EmployeeData INTO @STITLEOut, @SEMPLOYEEOut, @DHOURLYRATEOut, @SWCCATEGORYOut,
@DTSTARTOut
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SCODE = @SCODE + 1
SET @SCODEOut = '!' + REPLICATE(0,5-LEN(CAST(@SCODE AS VARCHAR(12)))) + CAST(@SCODE AS VARCHAR(12))
INSERT INTO propbut_pr (HCODE, SCODE, STITLE, SEMPLOYEE, DHOURLYRATE, SWCCATEGORY, DTSTART)
VALUES (@hProp, @SCODEOut, @STITLEOut, @SEMPLOYEEOut, @DHOURLYRATEOut, @SWCCATEGORYOut,
@DTSTARTOut)
FETCH NEXT FROM EmployeeData INTO @STITLEOut, @SEMPLOYEEOut, @DHOURLYRATEOut, @SWCCATEGORYOut,
@DTSTARTOut
END
CLOSE EmployeeData
DEALLOCATE EmployeeData
FETCH NEXT FROM PropCode INTO @hProp
END
CLOSE PropCode
DEALLOCATE PropCode
While this probably isn't the most elegant solution, it works and for once a year so I think it will be sufficient. Unless, of course, someone has a better idea?
July 24, 2008 at 7:02 am
rbarryyoung (7/23/2008)
Well I don't have enough details to do anything exact, but assuming that they are only using 5 digits in that Varchar(12) field, then something like this ought to work:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Cast( (Select Max(Cast(Substring(SCODE, 2,12) as int))
From propbut_pr Where HCODE = @HCode)
+ 1000000001 as varchar(5))
, ...
This certainly looks like it would be a little less work to use. However, when I tried to run it, all it returned was !* without any numbers? If I changed to something like this, how would I maintain the leading zeros (ie !00003)?
July 24, 2008 at 9:27 am
Try something like this:
DECLARE @new AS INT
DECLARE @NewScode AS VARCHAR(150)
DECLARE @Len AS INT -- number of characters to be used
SET @Len = 5 -- assumed to be 5 but could be up to 12 per table definition
SET @new = (SELECT MAX(CAST(SUBSTRING(SCODE,2,12) AS INT)) + 1 FROM propbut_pr)
SET @NewScode = '000000000000' + CAST(@New AS VARCHAR(5))
SET @NewScode = '!' + RIGHT(@NewScode,@Len)
Just to check results.
SELECT @new AS 'New integer value', @NewScode AS 'New Varchar value'
My values
New integer valueNew Varchar value
4 !00004
if all you are EVER going to use is the ! plus 5 characters which is 99,999 employes at one location ...
almost as large as Wal Mart this should suffice
July 24, 2008 at 9:39 am
Is it possible for you to add an identity column to the table, without breaking the rest of the code?
If so, then an insert trigger could update the column based on the identity being cast to varchar.
If not, then the possibility exists that you could have a separate table with an identity column, and use the trigger to work on that.
create table propbut_prIDs (
ID int identity primary key,
DiscardVal char(1))
go
create trigger propbut_pr_ID on dbo.propbut_pr
after insert
as
insert into dbo.propbut_prIDs (discardval)
select 'x'
from inserted
update dbo.propbut_pr
set scode = '!' + right('0000000000' + cast(id as varchar(10)), 10)
from dbo.propbut_prIDs
inner join inserted
on ... appropriate columns for join ...
Something like that might work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 9:47 am
GSquared (7/24/2008)
Is it possible for you to add an identity column to the table, without breaking the rest of the code?If so, then an insert trigger could update the column based on the identity being cast to varchar.
If not, then the possibility exists that you could have a separate table with an identity column, and use the trigger to work on that.
I can't modify the table since it is maintained by the vendor app. If I make changes directly in SQL Server, the vendor app will work but if the vendor app needs to make changes in the future, my changes would be overwritten.
The other issue that would make this a challenge is that the ID starts over at 1 for each property so I would essentially have to create this ID table for each property. Or am I off base with that assumption?
July 24, 2008 at 9:52 am
Not sure what you mean by starting over from 1 for each property. Do you mean you have multiple tables that all need this type of handling? If so, then yes, you'd need an ID table for each one.
Another way to do this is to look up the highest value currently in use in the main table, and increment off of that. This will get slower and slower the more data you have in the row. It's generally a bad idea, but it does work on very small tables.
A third way to do this is to use a trigger to store the highest current value for each table, and use that when you do new inserts, then update it after each insert. This is pretty much how ID columns work. The trick here is to manage transactions correctly so that concurrent inserts cannot possibly grab the same seed number. This will perform better than the second option, require less disk space than the first option, but is the trickiest to code correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 10:11 am
rbarryyoung (7/23/2008)
Well I don't have enough details to do anything exact, but assuming that they are only using 5 digits in that Varchar(12) field, then something like this ought to work:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Cast( (Select Max(Cast(Substring(SCODE, 2,12) as int))
From propbut_pr Where HCODE = @HCode)
+ 1000000001 as varchar(5))
, ...
Hi all,
Not sure what the problem is with the above code, this should work 100%
IF you simple change the VARHCAR(5) to something like VARCHAR(12), the reason you get * is cause of a type lenght problem.
Otherwise I found this to work very well...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 24, 2008 at 11:41 am
That code will work. It's the second solution I mentioned in my prior post. The problem with it is that it gets slower and slower as the table grows. If you'll never have more than a couple thousand rows, and you keep the indexes reasonably defragged, it should work okay. At larger table sizes (and a few thousand rows is a really small table), it will begin to slow things down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 12:08 pm
jim.powers (7/24/2008)
rbarryyoung (7/23/2008)
Well I don't have enough details to do anything exact, but assuming that they are only using 5 digits in that Varchar(12) field, then something like this ought to work:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Cast( (Select Max(Cast(Substring(SCODE, 2,12) as int))
From propbut_pr Where HCODE = @HCode)
+ 1000000001 as varchar(5))
, ...
This certainly looks like it would be a little less work to use. However, when I tried to run it, all it returned was !* without any numbers? If I changed to something like this, how would I maintain the leading zeros (ie !00003)?
I would have to see it in the proc to be sure, but try this version:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Right(Cast( (Select Max(Cast(Substring(p2.SCODE, 2,12) as int))
From propbut_pr p2 Where p2.HCODE = propbut_pr.HCode) --**
+ 10000001 as varchar(12)), 5)
, ...
On the line with the "--**" comment, getting the second HCODE reference right is important and depends entirly on the context of the rest of the query and the proc.
Maintaining the leading zeroes is easy and automatic because of the leading one in 10000001.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 12:20 pm
If performance of this code:
Insert into propbut_pr (..., SCODE, ...)
Select ...
, '!' + Right(Cast( (Select Max(Cast(Substring(p2.SCODE, 2,12) as int))
From propbut_pr p2 Where p2.HCODE = propbut_pr.HCode) --**
+ 10000001 as varchar(12)), 5)
, ...
.. is a concern, here's what you can do. First there needs to at the very least be an index on HCODE. Then the performance degradation that GSquared speaks of will be determined by the ratio {Total_Records/Distinct_HCODE's}. As this ratio grows, so will the time it takes to execute. Note that the total size of the table should not make much difference as long as the number of different HCODES increases along with the total number of records.
Finally, this expression:
Select Max(Cast(Substring(p2.SCODE, 2,12) as int)) is problematic. It will perform much better if it can be changed to Select Cast(Substring(Max(p2.SCODE), 2,12) as int) but it depends entirely on how well-behaved the formatting of SCODE is. Since I couldn't assume that, I took the more reliable (though slower) route.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply