Check Digit Calculation
Nov 20, 2007
I'm needing help with the following check digit calculation.
I have a Table with the following columns
Newscan nchar(31)
checkdigit nchar (10)
Newscan ex value 012345670 600888081 307051111
I need to multiply each character by the following sequence starting on the left alternating the multiplyer skipping spaces
21212121
if the multiplyed number is greater than 9 I need to add the two digits together. Ex 2X9 = 18 for a saved value of 9
I then need to add the entire string together
divide that by 10 and take the right most character and subtract that from 10 to end up with a check digit number that I want to store in the column checkdigit.
I have a case statement that converts the alpha chars in the string to the correct numeric values but I'm lost after that on how to accomplish this.
Thanks in advance for any help or direction.
I tried to look at the bank routing check digit function but couldn't get to the code of it.
Bill
View 12 Replies
ADVERTISEMENT
Jul 23, 2005
create function dbo.iso6346_char_to_number(@char char)returns intasbeginreturncase upper(@char)when '0' then 0when '1' then 1when '2' then 2when '3' then 3when '4' then 4when '5' then 5when '6' then 6when '7' then 7when '8' then 8when '9' then 9when 'A' then 10when 'B' then 12when 'C' then 13when 'D' then 14when 'E' then 15when 'F' then 16when 'G' then 17when 'H' then 18when 'I' then 19when 'J' then 20when 'K' then 21when 'L' then 23when 'M' then 24when 'N' then 25when 'O' then 26when 'P' then 27when 'Q' then 28when 'R' then 29when 'S' then 30when 'T' then 31when 'U' then 32when 'V' then 34when 'W' then 35when 'X' then 36when 'Y' then 37when 'Z' then 38endendcreate function dbo.iso6346_check_digit(@string char(10))returns charasbeginreturn (dbo.iso6346_char_to_number(substring(@string,1,1) )*1+dbo.iso6346_char_to_number(substring(@string,2,1) )*2+dbo.iso6346_char_to_number(substring(@string,3,1) )*4+dbo.iso6346_char_to_number(substring(@string,4,1) )*8+dbo.iso6346_char_to_number(substring(@string,5,1) )*16+dbo.iso6346_char_to_number(substring(@string,6,1) )*32+dbo.iso6346_char_to_number(substring(@string,7,1) )*64+dbo.iso6346_char_to_number(substring(@string,8,1) )*128+dbo.iso6346_char_to_number(substring(@string,9,1) )*256+dbo.iso6346_char_to_number(substring(@string,10,1 ))*512)%11end
View 1 Replies
View Related
Jul 17, 2006
how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.
View 7 Replies
View Related
Sep 5, 2001
How can I return a 2 digit month into a variable for the months Jan thru September
SELECT DATEPART(mm, GETDATE()) ---> returns 9 for September.. I need 09 returned in order to properly build my target file name...
Thx,
BT
View 2 Replies
View Related
Sep 17, 2015
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
View 2 Replies
View Related
Jul 19, 2007
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
View 5 Replies
View Related
Apr 10, 2008
Hi,
I am trying to figure out and hope u guys help me.
I need a only 6 digit ID only eg
55 = 000055
405 = 000405
5544 = 005544
How i can do it in SQL server and which formula/datatype i must use?
Deaf can Do anything, except hear
View 9 Replies
View Related
Feb 18, 2007
Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem? Thank you
View 3 Replies
View Related
Jul 6, 2004
hey all
i am stuck with this little problem
I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'
is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?
thanks
View 6 Replies
View Related
Jul 23, 2005
Help, I'm stuck! I have a Customer table:ID Name-- ---------------------1234 Christopher's Surf Boards4321 Christina's NailsI have to build a Account table:ID GPID-- --------------------1234 CHRIST004321 CHRIST01I've built a function that gives me the six alphas and concatenates thetwo digits. The GPID in the account table is the first six alphas ofthe company name plus two digits to ensure uniqueness. (Don't ask why Ican't use the existing id -- the story is long and boring.) I built afunction that creates the alpha part and assigns the first 00. I builta second function that I thought would check the Account table as I wasinserting and increment the numbers if the account number alreadyexisted. It doesn't work. In the above table, both IDs come out asCHRIST00. Here's the second function:ALTER FUNCTION fnValidateID(@mString varchar(15))RETURNS varchar(15)ASBEGINDECLARE@sTemp varchar(15),@sInc varchar(2),@iInc intBEGINset @sTemp = @mStringwhile exists (select GPID from dbo.tempID where GPID = @sTemp)beginset @iInc = convert(int,right(@mString,2)) + 1set @sInc = convert(varchar(2),@iInc)if len(@sInc) < 2 set @sInc = '0' + @sIncset @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sIncend--NEXTENDRETURN @sTempENDMy first guess is that the code is okay, so far as it goes, but that Ican't check the values in the table at the same time I'm inserting intoit. (The Account table is empty, initially.) In any case, I can'tfigure out where I've gone wrong.Thanks.
View 8 Replies
View Related
Nov 19, 2007
I am sure this will be an easy one for you pros, but I am having a case of the Mondays. I am trying to retrieve a date in the format 2007.01.01. In trying to do this I either end up with 1 or null when trying to create the 01 part. Below is some test code for the month portion.
DECLARE @Tst CHAR(4)
DECLARE @tst1 CHAR(2)
SET @tst = DATEPART(Month, '1/1/2008')
SET @tst1 = '00'
--SET @tst = right(@tst1 + @tst, 2)
Select @tst, @tst1, @tst1 + @tst, right(@tst +tst, 2)
Result Set:
1 00 001 [blank]
What am I doing wrong here?
TIA
View 6 Replies
View Related
Sep 3, 2007
I have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.
This is what I have
Code:
SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;
which does what I want it to do except it uses the whole 9 digits.
Any help would be appreciated
View 4 Replies
View Related
Jul 23, 2005
I have a field in a table that contains ten digit value representing a datetime. Is there any way to convert it to default datetime formatThanks--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Apr 28, 2008
I want to build a string that contains today's date (without the time).
For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either
(1) 2008-04-28
or
(2) 20080428. (I always want the month to be represented by a 2 digit number)
Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.
So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)
The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.
Thanks for looking.
View 11 Replies
View Related
Oct 15, 2015
I am currently working on an app and have an issue with a table in the database. Â The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column. Â If necessary, I can change the datatype but would prefer not to. Â The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000. Â I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?
View 9 Replies
View Related
Mar 29, 2008
hi everyone,
I'm looking for statement how to select if length digit = 12. as an example,
data
---------------
123456789012
4567892100
I just want to select if the length of data = 12 digit then the answer is 123456789012
Your kindness really appreciated, I really need the answer as quickly
View 3 Replies
View Related
Sep 28, 2014
I am retrieving the current month and setting a variable accordingly.
Code:
DECLARE @currMonthNum INT
SET @currMonthNum = DATEPART(MM, GETDATE())
My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).
Here is my attempt to do this...!
Code:
IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.
I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).
how to display a zero in front of the months that require it?
View 4 Replies
View Related
Sep 12, 2014
There is column mobile_number in my table
I have to replace with star(*) to the middle six character from contact number of 10 digit.
For example
The Contact number is 9334459875
I have to display it as follows
93******75 first two digit and last two only.
View 1 Replies
View Related
Aug 22, 2007
Hi
I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.
How do I generate 13 digit UNIQUE random in sql procedure?
Advance thanks
View 20 Replies
View Related
Aug 22, 2007
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks
View 3 Replies
View Related
Mar 20, 2008
Using SQL Analyzer or .NET 2.0 programmatically, is there a way to get Transact SQL 2000 to not append a digit at the end of an identifier name when the result set has the same identifier multiple times (from a JOINed SELECT to various tables which have names in common and where I need to get all column names/values in one fell swoop)?
SELECT A.*, B.* from TableA A
INNER JOIN TableB B on (A.Name = B.Name)
The result set will have NAME1 and NAME2, of course (or perhaps it's NAME and NAME1 -- no matter -- I wish to get rid of the numeric suffic).
Thanks much in advance.
View 3 Replies
View Related
Mar 27, 2007
I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.
SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')
This works perfect when running in T-SQL, but when attempting to define a variable in the expression
REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?
Anyone run into a simliar issue or have a suggested work around?
View 7 Replies
View Related
Apr 20, 2006
I have run into something really annoying.
If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')
But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.
I know there is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049. Is there anything similar within SSIS?
Thanks
Sutha
View 3 Replies
View Related
May 19, 2015
How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.
Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'
Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'
View 9 Replies
View Related
Feb 27, 2015
How to convert 6 digit number to mm/yyyy.
View 5 Replies
View Related
May 20, 2015
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies
View Related
Nov 19, 2014
We are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.
View 9 Replies
View Related
May 4, 2008
Hi,
My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
My manager has handed me a task of converting an excel file she uses in to a web aplication.
While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.
The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).
So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
Since these calculations are required a produce a result in an online environment, what will be faster?
I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.
Any views that you can offer will be very helpful.
Thanks in advance.
PMA
View 6 Replies
View Related
Aug 3, 2007
I need to calculate the overall GPA for a student in a particular class.
YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000
The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.
Here's my sql:
select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA
from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725
order by
school.schname,
student,
grade,
class
View 3 Replies
View Related
May 28, 2008
Dimensions:
DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode
Period = 1
Product = 1
Status Code = InStock
Period = 1
Product = 2
Status Code = InStock
Period = 2
Product = 1
Status Code = OutOfStock
Period = 2
Product = 2
Status Code = InStock
In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)
The measure = 1 / 2 or 50%. TIA
View 2 Replies
View Related
Oct 9, 2007
This is a smple data in table1
sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...
I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value
select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1
thanks
View 3 Replies
View Related
Apr 2, 2008
My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.
There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)
A 1300
B 3000
C 400
So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)
A 1000
B 2600
C 400
So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2
I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.
The design for creating the above sample tables is below
/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)
CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)
insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')
insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/
View 5 Replies
View Related
Feb 19, 2008
I´m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:
Code Snippet
WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month
When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).
View 11 Replies
View Related
