I am trying to return a specific digit for each number in a data set, I wrote an SQL function, and now I need to be able to do calculations in a function for every number in the data set. Could you point me in the right direction? I do not know if I should create a temporary table and then join it if I just write the vb function in Reporting Services and do it, or I just need to start all over again.
Here is the function USE [CUDatabase] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_Check_Digit] ( @unique_NBR VARCHAR(MAX) ) RETURNS @Values TABLE ( check_digit int, unique_nbr int ) AS BEGIN -- set up working variables DECLARE @LEN AS INT DECLARE @INDEX AS INT DECLARE @CHAR AS VARCHAR(1) DECLARE @POSITION AS INT DECLARE @VALUE AS INT DECLARE @SUBTOTAL AS INT DECLARE @BASE AS INT DECLARE @CHECK_DIG AS INT SET @LEN = LEN(@MEMBER_NBR) SET @INDEX = 1 SET @POSITION = 0 SET @VALUE = 0 SET @SUBTOTAL = 0 SET @BASE =0 SET @CHECK_DIG = 0 -- iterate until we have no more characters to work with WHILE @index< =@len BEGIN SET @char = SUBSTRING(@unique_NBR,(@ len-@POSITION ),1) select @value = (SELECT scd.dig_mul_value FROM CUDatabase.DBO.sdcCheckDigit SCD WHERE SCD.dig_place = @index) set @value = @value * @char SET @index = @index + 1 SET @POSITION = @POSITION + 1 SET @SUBTOTAL = @VALUE + @SUBTOTAL END SET @BASE = ((@SUBTOTAL/10)+1)*10 IF @BASE -@SUBTOTAL = 10 SET @CHECK_DIG = 0 ELSE SET @CHECK_DIG = @ BASE-@SUBTOTAL INSERT INTO @Values (check_digit, unique_nbr) VALUES (CAST(@CHECK_DIG AS int),@unique_NBR) RETURN END GO
The table in the select statement of this function has the following meanings: dig_place dig_mul_value 1 7 2 3 3 1 4 7 5 3 6 1 7 7 8 3 9 1
Here is the data set, I need to go through each unique_nbr and return the check digit.
`SELECT I.D1NAME, IA.ADDRESS_ID, A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.CITY, A.STATE, A.ZIP_STR, TL.COMPANY_NAME, TL.COMPANY_DESCRIPTION, TL.EFFECTIVE_ENTRY_DATE, TL.AMOUNT, TL.ACCOUNT_NBR, TL.ACCT_DBRN FROM MEMBERSHIPPARTICIPANT MP JOIN INDIVIDUAL I ON I.INDIVIDUAL_ID = MP.INDIVIDUAL_ID AND I.DL_LOAD_DATE = MP.DL_LOAD_DATE JOIN INDIVIDUALADDRESS IA ON IA.INDIVIDUAL_ID = I.INDIVIDUAL_ID AND IA.IS_PRIMARY = 1 AND IA.DL_LOAD_DATE = I.DL_LOAD_DATE JOIN ADDRESS A ON A.ADDRESS_ID = IA.ADDRESS_ID AND A.DL_LOAD_DATE = IA.DL_LOAD_DATE JOIN (SELECT EFT.unique_NBR, EFT.ACCOUNT_NBR, EFT.ACH_SDC_NBR, EFT.COMPANY_NAME, EFT.COMPANY_DESCRIPTION, EFT.INDIVIDUAL_ID_NBR, EFT.INDIVIDUAL_NAME, EFT.XPTIMESTAMP, EFT.STANDARD_ENTRY_CLASS, EFT.ROUTING_NUMBER, EFT.ACCT_DBRN, EFT.AMOUNT, EFT.EFFECTIVE_ENTRY_DATE FROM EFTTRANSACTION EFT WHERE EFT.ROUTING_NUMBER = 999999999 AND EFT.STANDARD_ENTRY_CLASS IN ('WEB','TEL') AND EFT.EFFECTIVE_ENTRY_DATE >= '11/01/2012') TL ON T L.unique_NBR = MP.unique_NBR WHERE MP.DL_LOAD_DATE = (SELECT MAX(DL_LOAD_DATE) FROM MEMBERSHIPPARTICIPANT) AND MP.PARTICIPATION_TYPE = 101
Thanks for any help