Calcular codigo de barras con T-SQL

2016-03-08T09:02:00.001-06:00

Este query consta de cuatro subconsultas, la primera mas profunda, es para normalizar la referencia obtenida del servidor, es decir, que tenga un minimo y maximo de 21 digitos, despues la que separa los digitos uno a uno y multiplicando por dos los digitos en lugares impares y dejando intactos los que estan en lugares pares, el siguiente modulo verifica que los numeros obtenidos de la operacion anterios sobre los digitos en posicion par no sean superiores o iguales a diez, en caso verdadero suma los dos valores de cada resultado para dejarlos en un solo digito, y por ultimo, la consulta superior se encarga de concatenar cada uno de los digitos obtenidos de esta ultima operacion y calcular el digito de control de errores, facil no?

Select
numCliente
,Rtrim(Convert(Char,Ref)) +
Rtrim
(Convert
(Char,
(Case
When ([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]) % 10 = 0
Then 0
Else (10 - ([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]) % 10)
End
)
)
) As Codigo_De_Barras
From
(
Select
numCliente
,Ref
, [1] = (Case When A >= 10 Then Convert(Int,(Left(A,1)) + Convert(Int,Right(A,1))) Else A End)
, [2] = B
, [3] = (Case When C >= 10 Then Convert(Int,(Left(C,1)) + Convert(Int,Right(C,1))) Else C End)
, [4] = D
, [5] = (Case When E >= 10 Then Convert(Int,(Left(E,1)) + Convert(Int,Right(E,1))) Else E End)
, [6] = F
, [7] = (Case When G >= 10 Then Convert(Int,(Left(G,1)) + Convert(Int,Right(G,1))) Else G End)
, [8] = H
, [9] = (Case When I >= 10 Then Convert(Int,(Left(I,1)) + Convert(Int,Right(I,1))) Else I End)
,[10] = J
,[11] = (Case When K >= 10 Then Convert(Int,(Left(K,1)) + Convert(Int,Right(K,1))) Else K End)
,[12] = L
,[13] = (Case When M >= 10 Then Convert(Int,(Left(M,1)) + Convert(Int,Right(M,1))) Else M End)
,[14] = N
,[15] = (Case When O >= 10 Then Convert(Int,(Left(O,1)) + Convert(Int,Right(O,1))) Else O End)
,[16] = P
,[17] = (Case When Q >= 10 Then Convert(Int,(Left(Q,1)) + Convert(Int,Right(Q,1))) Else Q End)
,[18] = R
,[19] = (Case When S >= 10 Then Convert(Int,(Left(S,1)) + Convert(Int,Right(S,1))) Else S End)
,[20] = T
,[21] = (Case When U >= 10 Then Convert(Int,(Left(U,1)) + Convert(Int,Right(U,1))) Else U End)
From
(
Select
numCliente
,Ref
,Right(Left(Ref, 1),1) * 2 As A
,Right(Left(Ref, 2),1) As B
,Right(Left(Ref, 3),1) * 2 As C
,Right(Left(Ref, 4),1) As D
,Right(Left(Ref, 5),1) * 2 As E
,Right(Left(Ref, 6),1) As F
,Right(Left(Ref, 7),1) * 2 As G
,Right(Left(Ref, 8),1) As H
,Right(Left(Ref, 9),1) * 2 As I
,Right(Left(Ref,10),1) As J
,Right(Left(Ref,11),1) * 2 As K
,Right(Left(Ref,12),1) As L
,Right(Left(Ref,13),1) * 2 As M
,Right(Left(Ref,14),1) As N
,Right(Left(Ref,15),1) * 2 As O
,Right(Left(Ref,16),1) As P
,Right(Left(Ref,17),1) * 2 As Q
,Right(Left(Ref,18),1) As R
,Right(Left(Ref,19),1) * 2 As S
,Right(Left(Ref,20),1) As T
,Right(Left(Ref,21),1) * 2 As U
From
(
Select distinct
numCliente
,numReferencia
,'09' +
(Case Len(numReferencia)
When 5 Then '00000000000000'
When 6 Then '0000000000000'
When 7 Then '000000000000'
When 8 Then '00000000000'
When 9 Then '0000000000'
End) +
Ltrim(Rtrim(Convert(Char,numReferencia))) As Ref
From
cxcobrar B
where
numReferencia is not null
and len(numReferencia) >= 5
and len(numReferencia) <= 9
) As Obtiene_Datos
) As Calcula_Valores
) As Obtiene_Verificador
Where
numCliente In
()

Monitor magnetotelurico

2013-01-14T15:09:00.003-06:00

Sistema de monitoreo magnetotelurico

Mi nombre es David Cuéllar, vivo en México, soy un desarrollador de aplicaciones móviles y he desarrollado un sistema de monitoreo magnetotelurico, que, según mis investigaciones, he
encontrado que puede ser muy util monitoreo sísmico y magnético, en un futuro, muy útil para la predicción de terremotos.

Este sistema es similar a la realizada por la NASA usando satélites, pero en contraste, mi sistema utilizará la red de telefonía celular, mediante la conversión de cada smarphone en una estación de monitoreo, obteniendo de este modo un sistema global.

Actualmente busco fondos para continuar con el desarrollo de este sistema, si deseas contribuir por favor ponte en contacto conmigo.

------------------------------------------
Magnetotelluric monitoring system

Regards

My name is David Cuéllar, I live in Mexico, I am a mobile application developer and I have developed a magnetotelluric monitoring system, which, according to my research, I have found that it can be very useful magnetic and seismic monitoring in the future, too useful for earthquake prediction.

This system is similar to that performed by NASA using satellites, but in contrast, my system will use the cellular telephone network, by converting each smarphone in a monitoring station, thereby obtaining an overall system.

Currently seeking funding to continue the development of this system, if you wish to contribute please contact me.

http://ciencia1.nasa.gov/science-at-nasa/2003/11aug_earthquakes/

David Cuellar Alfaro

Calcular codigo de barras con T-SQL

Monitor magnetotelurico