March 15, 2010 at 2:55 pm
Hi All,
I need some help. I am needing to do a formula for a computed column. The formula needs to analyze a single column which has a numbered formula for transactions in a given month.
This numbering system generated from a stored procedure provides all types of information at a glance as well as provides a unique ID used in other tables.
[FONT="Arial Black"]0310B9S64[/FONT]
[*]Month and Date created
[*]Volume of transaction for specific action
[*]A Unique ID
chars 1-2 = Month
chars 3-4 = Year
chars 5-9 = Count
Each transaction looks at a table to get the last used number for a specific Month/Year, then increments cLastID value starting from the far right.
Once 8,9 reaches 99 then 7 increments an Alpha (B to C) and keeps working left. I have found that I can achieve unique id's requiring less length using this method.
What I need to do is create a count formula that will take cLastID and determine how many transactions it represents according to chars 5 - 9. I am assuming using the Exponent Theory will solve it but I need help in the language.
Thanks to all who accept the challenge.
March 15, 2010 at 3:17 pm
Are you using the full alphabet for this? If so, are you prepared to deal with "IDs" that are obscenities, racial slurs, sexual, blasphemous, etc?
If so, here's sample code for how to turn a string like that into a decimal number:
DECLARE @Vals CHAR(36);
SELECT @Vals = '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @String CHAR(5);
SELECT @String = 'B9S64';
SELECT SUBSTRING(@String, Number, 1), CHARINDEX(SUBSTRING(@String, Number, 1), @Vals) AS BaseVal, Number AS Position,
CHARINDEX(SUBSTRING(@String, Number, 1), @Vals) * POWER(26, 5-Number) AS Val
FROM Common.dbo.Numbers
WHERE Number BETWEEN 1 AND 5;
SELECT SUM(
CHARINDEX(SUBSTRING(@String, Number, 1), @Vals) * POWER(26, 5-Number)) AS NumericValue
FROM Common.dbo.Numbers
WHERE Number BETWEEN 1 AND 5;
This assumes the existence of a Numbers table. If you don't have one, you can create one, either on-the-fly in the query, or permanently, pretty easily. Search this site for Numbers or Tally, you'll find scripts for that.
- 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
March 15, 2010 at 3:28 pm
As an additional note, on the point of ending up with words or approximations of words (think words made from H8 (hate), or with 2 (to or too) in them, or a 1 for an L or I):
These accidental combinations are pretty much guaranteed to end in at least your job termination, if not a lawsuit, depending on who sees them.
It's impossible to avoid all of them.
If nobody will ever see the combinations, then just use numbers.
- 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
March 15, 2010 at 3:41 pm
This numbering system generated from a stored procedure provides all types of information at a glance as well as provides a unique ID used in other tables.
I can see instances where you could use an encoded value like this, but I would argue against storing it. I would argue even more against using it as a key value in your tables.
Using an integer identity would require less storage space and save you the overhead of using sequence tables and the headaches of having a primary key that is neither a natural or surrogate key. I just cringe when I see a primary key value that is created using an algorithm.
March 15, 2010 at 9:40 pm
Along with all the other problems people have stated (some having legal impact), the "code" also violates a couple of normal forms because it records 3 different pieces of information in a single column. It's cute but not effective. It's no more effective than someone storing obfuscated data in a CSV column and, because it's character based, you've actually increased your storage requirements and have severly complicated the creation of unique IDs. Further, (36^5) -1 is only a little over sixty million rows so you've also severly limited the range of your ID's.
I'll join the others in making the strong suggestion that you don't actually do this.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2010 at 6:42 am
Jeff, it's c. 60M rows per month, because of the prefix. That's probably enough for most business purposes (unless you're Amazon, Google, et al).
Totally agree with the rest of what you wrote.
One of the most basic "rules of thumb" in engineering is, "if it's clever enough to be complicated, you're doing it wrong".
- 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
March 16, 2010 at 10:54 am
Wow, thanks for all the replies.
Wanted to clear up a few things.
1) This numbering (SessionID) does not go into a Primary Key column.
2) No one sees it except for the system admins
3) On the first day of the month the count starts over e.g 0410A0A00
The SessionID is used to group 2 to 30 people who attended the same meeting e.g The attendee will have a ServiceID(PK), Date, Time, Meeting Room, Speaker, NmbrAttending, and the SessionID. If there were 12 attending, all would have their own ServiceID, but they would all have the same SessionID to distinguish that they all were in attendance of the same meeting. When it comes time to edit, delete or report then the SessionID can used more efficiently than using Date,Time,Speaker,Room in a WHERE clause.
There are approx 20 users on the system entering Group type meeting all day. I am using this method to organize records in the Services table. I am also tracking what ServiceID's and SessionID's they delete like Invoice/Check tracking.
What I am wanting is to have a computed column that would compute the column next to it how many it represents in decimal value.
My Table will be like:
fcMonthYear(char 4) fcLastID(Char 9) cntIdSum(bigint)
0310 B9S64
0410 A8N24
0510 A9D96
The stored procedure currently in use will be modified to look at this table for the Month/Year row and increment the last id. The computed column, I hope can be achieved so I can 1) visually see the results, 2) Use the sum for other verifications.
March 16, 2010 at 11:23 am
This numbering system generated from a stored procedure provides all types of information at a glance as well as provides a unique ID used in other tables.
I'll have to admit that I wrote my last post knowing that you did not explicitly say that this would be a primary key value. My assumption was based on the quote that I've included above. You saying that this value is used as a unique ID in other tables led me to the assumption that it was the primary key somewhere in one of the tables.
Your statement leads me to 2 assumptions. Either the value is a primary key or unique constraint, which in both cases is a bad idea for an encoded value like this since it is neither a natural key or surrogate key. Making it unique may in theory make it a candidate key, but like Jeff mentioned, using a column to represent multiple value breaks the normal forms.
The other option is that it is neither a primary key or a unique constraint. In this case, if you have the value in multiple tables, you are storing redundant data.
I'll stand firm on my first post that I would not do this. The beauty of working with relational data eliminates the need for storing such data. If you want to group people together by the sessions they attend, your data model should allow you to create a simple view that joins together the relevant tables to produce a single look at a sessions attributes (date, # of attendees, etc.) without having to decode a value. Admins or not, what you are trying to do breaks normalization rules.
Why don't you post your table DDL so we can take a look at it?
March 16, 2010 at 11:34 am
Why the complexity?
The SessionID is used to group 2 to 30 people who attended the same meeting e.g The attendee will have a ServiceID(PK), Date, Time, Meeting Room, Speaker, NmbrAttending, and the SessionID. If there were 12 attending, all would have their own ServiceID, but they would all have the same SessionID to distinguish that they all were in attendance of the same meeting. When it comes time to edit, delete or report then the SessionID can used more efficiently than using Date,Time,Speaker,Room in a WHERE clause.
Why not just have a numeric value for the session ID? Reference a sub-table with it, include the details in the sub-table (year, month, day, room, speaker), and don't store the number attending, calculate that at runtime.
That normalizes the data nicely, will make for fast updates, inserts, and selects, and is easy to maintain and code against.
And you don't have to include all the other columns in a Where clause, just the ID number for the session.
That'll be much more efficient, effective, easy, and fast, than what you're proposing.
- 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
March 16, 2010 at 11:55 am
Your like a mind reader Gus. That's exactly where I was headed. I was hoping to see the OPs existing DDL so we can help further. From OPs last post, it looks like there are other concerns that we can help with such as storing date values in character data types (month/year in char 4).
March 16, 2010 at 12:11 pm
This is the existing DDL on the Service Table.
BIG NOTE: I did not design this table. Taking steps to rework this.
CREATE TABLE [dbo].[Service] (
[ServiceId] [bigint] IDENTITY (1, 1) NOT NULL ,
[clientid] [int] NOT NULL ,
[SessionID] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Counselor] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FamilyType] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FamilyText] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumberAttending] [smallint] NULL ,
[Absent] [tinyint] NULL ,
[Provider] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[Time] [float] NULL ,
[TxPlanGoal] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceType] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumberClients] [smallint] NULL ,
[Topic] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Objective] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttitudeRating] [tinyint] NULL ,
[TxProgressRating] [tinyint] NULL ,
[BehaviorRating] [tinyint] NULL ,
[InvolvementRating] [tinyint] NULL ,
[Notes] [nvarchar] (2255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SessionTime] [smalldatetime] NULL ,
[ProgramNumber] [int] NULL ,
[CMtype] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMbilling] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DrugScreenTest] [tinyint] NULL ,
[DrugScreenResult] [tinyint] NULL ,
[PAYSRC] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMunits] [int] NULL ,
[CMunit_Measure] [smallint] NULL ,
[BillingCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[orig_grp_size] [int] NULL
) ON [PRIMARY]
GO
March 16, 2010 at 7:00 pm
GSquared (3/16/2010)
Jeff, it's c. 60M rows per month, because of the prefix. That's probably enough for most business purposes (unless you're Amazon, Google, et al).
Heh... Yep. I forgot about that. That also means you have to reset the count each month which probably means the use of a sequence table or RBAR MAX commands.
However, the OP did say that the 5 characters would be unique and that's what I was going by.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2010 at 7:07 pm
GSquared (3/16/2010)
Why the complexity?The SessionID is used to group 2 to 30 people who attended the same meeting e.g The attendee will have a ServiceID(PK), Date, Time, Meeting Room, Speaker, NmbrAttending, and the SessionID. If there were 12 attending, all would have their own ServiceID, but they would all have the same SessionID to distinguish that they all were in attendance of the same meeting. When it comes time to edit, delete or report then the SessionID can used more efficiently than using Date,Time,Speaker,Room in a WHERE clause.
Why not just have a numeric value for the session ID? Reference a sub-table with it, include the details in the sub-table (year, month, day, room, speaker), and don't store the number attending, calculate that at runtime.
That normalizes the data nicely, will make for fast updates, inserts, and selects, and is easy to maintain and code against.
And you don't have to include all the other columns in a Where clause, just the ID number for the session.
That'll be much more efficient, effective, easy, and fast, than what you're proposing.
Man, I have to agree with that. Not a slam on the OP but I have to also strongly agree with something else you said...
"if it's clever enough to be complicated, you're doing it wrong"
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2010 at 6:10 am
Jeff Moden (3/16/2010)
...I have to also strongly agree with something else you said...GSquared
"if it's clever enough to be complicated, you're doing it wrong"
One of my favorite rules. Knowledge induces simplicity. The more you know, the simpler things become.
- 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
March 17, 2010 at 8:26 am
GSquared (3/17/2010)
Jeff Moden (3/16/2010)
...I have to also strongly agree with something else you said...GSquared
"if it's clever enough to be complicated, you're doing it wrong"One of my favorite rules. Knowledge induces simplicity. The more you know, the simpler things become.
Heh... what a simple concept. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply