Feeds:
Posts
Comments

Archive for November, 2012

02010504060703080911101312141716151918

Read Full Post »

Create Function Dbo.Part
(@Value Varchar(8000)
,@Part Int
,@Sep Char(1)=’-‘
)Returns Varchar(8000)
As Begin
Declare @Start Int
Declare @Finish Int
Set @Start=1
Set @Finish=CharIndex(@Sep,@Value,@Start)
While (@Part>1 And @Finish>0)Begin
Set @Start=@Finish+1
Set @Finish=CharIndex(@Sep,@Value,@Start)
Set @Part=@Part-1
End
If @Part>1 Set @Start=Len(@Value)+1 — Not found
If @Finish=0 Set @Finish=Len(@Value)+1 — Last token on line
Return SubString(@Value,@Start,@Finish-@Start)
End

 

DECLARE @MRDNUMBER VARCHAR(10)
DECLARE @STRQUERY VARCHAR(MAX),@STROD VARCHAR(100),@STROS VARCHAR(100)
DECLARE @RECVALUE VARCHAR(50)
DECLARE @RECCNT INT,@CNT VARCHAR(2)

SET @MRDNUMBER=’1665773′

SET @RECVALUE = (SELECT TOP 1 FN_OD_FMETHOD FROM MR_FUNDUS WHERE FN_MRD_NUMBER=@MRDNUMBER)

SET @RECCNT = (Select Count(Value) FROM DBO.FN_SPLIT(@RECVALUE,’#’))

SET @CNT=1
SET @STRQUERY = ”
SET @STROD = ”
SET @STROS = ”

WHILE (@RECCNT > 0)

BEGIN
SET @STROD = @STROD + ‘ODMETHOD’ + @CNT + ‘+’ + CHAR(39) + ‘,’ + CHAR(39) + ‘+’
SET @STROS = @STROS + ‘OSMETHOD’ + @CNT + ‘+’ + CHAR(39) + ‘,’ + CHAR(39) + ‘+’

SET @STRQUERY = @STRQUERY + ‘,(SELECT MPM_PARAMETER_VALUE FROM MR_PARAMETER_MASTER1 WHERE MPM_PARAMETER_CD=DBO.PART(FN_OD_FMETHOD,’ + @CNT +’ ,’ + CHAR(39) + ‘#’ + CHAR(39) + ‘)) As ODMETHOD’ + @CNT
SET @STRQUERY = @STRQUERY + ‘,(SELECT MPM_PARAMETER_VALUE FROM MR_PARAMETER_MASTER1 WHERE MPM_PARAMETER_CD=DBO.PART(FN_OS_FMETHOD,’ + @CNT +’ ,’ + CHAR(39) + ‘#’ + CHAR(39) + ‘)) As OSMETHOD’ + @CNT

SET @RECCNT = @RECCNT – 1
SET @CNT = @CNT + 1
END
SET @STROD = (SELECT SUBSTRING(@STROD,1,LEN(@STROD)-5))
SET @STROS = (SELECT SUBSTRING(@STROS,1,LEN(@STROS)-5))

SET @STRQUERY = ‘SELECT FN_MRD_NUMBER,
FN_OD_MCLARITY,
FN_OD_TYPE as ODTYPE,
FN_OD_SHAPE,FN_OD_COLOR,FN_OD_CUP,FN_OD_CD_RATIO,FN_OD_MARGIN,FN_OD_PRETINA,FN_OD_NRR_RIM,
FN_OD_N_VESSELS,FN_OD_VITREOUS,FN_OD_V_ABNORMAL,FN_OD_DISC,
FN_OD_DISC_ABNORMALITY,FN_OD_MACULA,FN_OD_MACULA_ABNORMAL,
FN_OD_RVESSELS,FN_OD_RVESSELS_ABNORMAL,FN_OD_POSTERIOR_POLE,FN_OD_POSTERIOR_POLE_ABNORMALITY,
FN_OS_FMETHOD as OSMETHOD,
FN_OS_MCLARITY,
FN_OS_TYPE,FN_OS_SHAPE,FN_OS_COLOR,FN_OS_CUP,
FN_OS_CD_RATIO,FN_OS_MARGIN,FN_OS_PRETINA,
FN_OS_NRR_RIM,FN_OS_N_VESSELS,FN_OS_VITREOUS,
FN_OS_V_ABNORMAL,FN_OS_DISC,FN_OS_DISC_ABNORMALITY,FN_OS_MACULA,FN_OS_MACULA_ABNORMAL,FN_OS_RVESSELS,FN_OS_RVESSELS_ABNORMAL,FN_OS_POSTERIOR_POLE,
FN_OS_POSTERIOR_POLE_ABNORMALITY,FN_CRT_DATE’ + @STRQUERY + ‘ FROM DBO.MR_FUNDUS WHERE FN_MRD_NUMBER=’ + CHAR(39) + @MRDNUMBER + CHAR(39)
SET @STRQUERY = ‘SELECT TOP 1 FN_MRD_NUMBER,ISNULL(‘ + @STROD + ‘,’+ CHAR(39) + ‘–‘ + CHAR(39) + ‘) AS ODMETHOD,
FN_OD_MCLARITY,
ODTYPE,
FN_OD_SHAPE,FN_OD_COLOR,FN_OD_CUP,FN_OD_CD_RATIO,FN_OD_MARGIN,FN_OD_PRETINA,FN_OD_NRR_RIM,
FN_OD_N_VESSELS,FN_OD_VITREOUS,FN_OD_V_ABNORMAL,FN_OD_DISC,
FN_OD_DISC_ABNORMALITY,FN_OD_MACULA,FN_OD_MACULA_ABNORMAL,
FN_OD_RVESSELS,FN_OD_RVESSELS_ABNORMAL,FN_OD_POSTERIOR_POLE,FN_OD_POSTERIOR_POLE_ABNORMALITY,
ISNULL(‘ + @STROS + ‘,’+ CHAR(39) + ‘–‘ + CHAR(39) + ‘) AS OSMETHOD,
FN_OS_MCLARITY,
FN_OS_TYPE,FN_OS_SHAPE,FN_OS_COLOR,FN_OS_CUP,
FN_OS_CD_RATIO,FN_OS_MARGIN,FN_OS_PRETINA,
FN_OS_NRR_RIM,FN_OS_N_VESSELS,FN_OS_VITREOUS,
FN_OS_V_ABNORMAL,FN_OS_DISC,FN_OS_DISC_ABNORMALITY,FN_OS_MACULA,FN_OS_MACULA_ABNORMAL,FN_OS_RVESSELS,FN_OS_RVESSELS_ABNORMAL,FN_OS_POSTERIOR_POLE,
FN_OS_POSTERIOR_POLE_ABNORMALITY,FN_CRT_DATE FROM (‘ + @STRQUERY + ‘)V ORDER BY FN_CRT_DATE DESC’

EXECUTE(@STRQUERY)

 

Read Full Post »