blank RESULT is 0 where CHM grup

PHOTO

Sun Jan 21 2024 16:45:59 GMT+0000 (Coordinated Universal Time)

Saved by @HUMRARE7 #ilink

UPDATE tbl_lab_setup_Test_Control 
SET Test_Blank_Result_Flag = 0 
WHERE
	Test_Code IN (
	SELECT
		tbl_lab_setup_Test.Test_Code 
	FROM
		dbo.tbl_lab_setup_Test_Control
		INNER JOIN dbo.tbl_lab_setup_Test ON tbl_lab_setup_Test_Control.Test_Code = tbl_lab_setup_Test.Test_Code 
	WHERE
		tbl_lab_setup_Test_Control.Test_Blank_Result_Flag = '1' 
	AND tbl_lab_setup_Test.Test_Group_Code = 'CH' 
	);

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

i-LINK@ Q-RY

@HUMRARE7

HIV interp 3 METHOD eSMEAR SMALL-FISH HIV3METHOD NEWเพื่อไชด์ใหม่ นับRERUN HIV3M STRUX INT. OUTLAB dif Barcode @ห้วยพลู OUTLAB dif Barcode @ห้วยพลู การดึง Ref. จาก SETUP TEST Concate_test_REsult-to-HI HIS eGFR set STAGE STAGE CASE WHEN {AGE,YEAR} < 18.0 THEN "-" Description For User Release@Report UPDATE Source_Definition eGFR with STAGE UACR A:C ratio OUT_LAB_LIST Update TAT blank RESULT is 0 where CHM grup

#sql #vba #ilink #lis #gqry

eGFR with STAGE

declare @value decimal(10,2)
set @value = (select 
CASE WHEN %CH003% = 0 THEN 0 
WHEN {AGE,YEAR} > 130.0 THEN 0 
WHEN {AGE,YEAR} < 18.0 THEN ((0.41 * {HEIGHT}) / %CH003%) 
WHEN {SEX} = "M" AND %CH003% <= 0.9 THEN ((141 * (POWER((%CH003% / 0.9), -0.411))) * POWER(0.993, {AGE,YEAR})) 
WHEN {SEX} = "M" AND %CH003% > 0.9 THEN ((141 * (POWER((%CH003% / 0.9), -1.209))) * POWER(0.993, {AGE,YEAR})) 
WHEN {SEX} = "F" AND %CH003% <= 0.7 THEN ((144 * (POWER((%CH003% / 0.7), -0.329))) * POWER(0.993, {AGE,YEAR})) 
WHEN {SEX} = "F" AND %CH003% > 0.7 THEN ((144 * (POWER((%CH003% / 0.7), -1.209))) * POWER(0.993, {AGE,YEAR})) ELSE 0 END)

SELECT CASE WHEN @value > 90.0 THEN "Stage G1"      
WHEN @value >= 60.00 AND @value <= 89.99 THEN "Stage G2"      
WHEN @value >= 45.00 AND @value <= 59.99 THEN "Stage G3a"      
WHEN @value >= 30.00 AND @value <= 44.99 THEN "Stage G3b"      
WHEN @value >= 15.00 AND @value <= 29.99 THEN "Stage G4"      
WHEN @value <  15.00 THEN "Stage G5"      
ELSE "N/A" END

#sql #vba #ilink #lis #gqry

 [SOURCE CODE]

[WARD]

[PRIORITY]

[PATIENT TYPE CODE]

[YEAR]

[SEX]

[REQ ITEM ORDER DATETIME]

#sql #vba #ilink #lis #gqry

CASE WHEN "%CH201%" = "<10" AND "%CH202%" = "<10" THEN "<30"
     WHEN "%CH201%" = "<10" AND "%CH202%" <> "" THEN "<30" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = "<10" THEN "50"    
     WHEN "%CH201%" = "10" AND "%CH202%" = "10" THEN "100" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = "50" THEN "20" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = "100" THEN "10" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = "200" THEN "5" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = "300" THEN "3" 
	 WHEN "%CH201%" = "10" AND "%CH202%" = ">300" THEN "<30" 
	 WHEN "%CH201%" = "30" AND "%CH202%" = "<10" THEN "150" 
	 WHEN "%CH201%" = "30" AND "%CH202%" = "10" THEN "300" 
	 WHEN "%CH201%" = "30" AND "%CH202%" = "50" THEN "60" 
	 WHEN "%CH201%" = "30" AND "%CH202%" = "100" THEN "30"
	 WHEN "%CH201%" = "30" AND "%CH202%" = "200" THEN "15"
	 WHEN "%CH201%" = "30" AND "%CH202%" = "300" THEN "10"
	 WHEN "%CH201%" = "30" AND "%CH202%" = ">300" THEN "<30"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "<10" THEN ">300"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "10" THEN ">300"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "50" THEN "160"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "100" THEN "80"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "200" THEN "40"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "300" THEN "27"
	 WHEN "%CH201%" = "80" AND "%CH202%" = "<30" THEN "<30"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "<10" THEN ">300"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "10" THEN ">300"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "50" THEN "300"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "100" THEN "150"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "200" THEN "75"
	 WHEN "%CH201%" = "150" AND "%CH202%" = "300" THEN "50"
	 WHEN "%CH201%" = "150" AND "%CH202%" = ">300" THEN "<30"
	 WHEN "%CH201%" = ">150" AND "%CH202%" <> "" THEN ">300"
 ELSE "N/A" END

#sql #vba #ilink #lis #gqry

SELECT

  LEFT(CONVERT(varchar,[REQ ITEM CHECKIN DATETIME], 120),16) as วันที่รับแล็บ
     ,LEFT(CONVERT(varchar,[ORDER DATETIME], 120),16) as วันที่สั่ง
     ,[HN]
     ,[LN]
     ,[FULLNAME] as [ชื่อ-สกุล]
     ,[YEAR] as ปี
     ,[SEX] as เพศ
     ,[WARD NAME] as จุดที่สั่ง
     ,[REQ ITEM GROUP CODE] as CODE
     ,[RES ITEM NAME] as รายการตรวจ
     ,[RES ITEM RESULT] as ผลการตรวจ
     ,[RES ITEM STATE] as state
     ,[ORDER COMMENT] as comment



  FROM [LAB_DB].[dbo].[view_lab_statistic_Result_List]

WHERE 
  [ORDER INACTIVE] ='n' 
     AND ([REQ ITEM GROUP CODE] ='SP' or [REQ ITEM GROUP CODE] ='PA')
     AND ([REQ STATE] ='a' or [REQ STATE] ='u')
	 
	 
	 --[REQ ITEM CHECKIN DATETIME]
	 --Checkin

https://www.mycompiler.io/view/52ZOIc77SmN

log cl900 TSH :empty


10:44:51.[952613]	>>	 

10:44:51.[962614]	>>	======================================================================

10:44:51.[962614]	>>	                                 SEARCHING FOR BARCODE: [240116032002]

10:44:51.[972615]	>>	======================================================================

10:44:51.[982615]	>>	 

10:44:52.[002616]	>>	 

10:44:52.[002616]	>>	======================================================================

10:44:52.[012618]	>>	         SEND DATA TO ANALYZER: [771D48E8-EEF1-4CB5-9809-67DA55A909F2]

10:44:52.[022618]	>>	======================================================================

10:44:52.[032619]	>>	 

10:44:52.[142625]	>>	S:	<VT>MSH|^~\&|||||20240116104452||QCK^Q02|49|P|2.3.1||||||ASCII|||<CR>MSA|AA|49|order<SP>accepted.|||0|<CR>ERR|0|<CR>QAK|SR|OK|<CR><FS><CR>

10:44:52.[152627]	>>	 

10:44:52.[162627]	>>	======================================================================

10:44:52.[172627]	>>	         SEND DATA TO ANALYZER: [771D48E8-EEF1-4CB5-9809-67DA55A909F2]

10:44:52.[172627]	>>	======================================================================

10:44:52.[182629]	>>	 

10:44:52.[312637]	>>	S:	<VT>MSH|^~\&|||||20240116104452||DSR^Q03|49|P|2.3.1||||||ASCII|||<CR>MSA|AA|49|order<SP>information.|||0|<CR>ERR|0|<CR>QAK|SR|OK|<CR>QRD|20240116104452|R|D|49|||RD|240116032002|OTH|||T|<CR>QRF||||||RCT|COR|ALL||<CR>DSP|1||2565011461|||<CR>DSP|2|||||<CR>DSP|3||นส.<SP>วรรณนิศา<SP>วรสินวิวัฒน์|||<CR>DSP|4||19891228000000|||<CR>DSP|5||F|||<CR>DSP|6|||||<CR>DSP|7|||||<CR>DSP|8|||||<CR>DSP|9|||||<CR>DSP|10|||||<CR>DSP|11|||||<CR>DSP|12|||||<CR>DSP|13|||||<CR>DSP|14|||||<CR>DSP|15|||||<CR>DSP|16|||||<CR>DSP|17|||||<CR>DSP|18|||||<CR>DSP|19|||||<CR>DSP|20|||||<CR>DSP|21||240116032002|||<CR>DSP|22|||||<CR>DSP|23||20240116104452|||<CR>DSP|24||N|||<CR>DSP|25|||||<CR>DSP|26||Serum|||<CR>DSP|27|||||<CR>DSP|28||ผู้ป่วยนอก|||<CR>DSP|29||FT3|||<CR>DSP|30||TSH|||<CR>DSC||<CR><FS><CR>

10:44:52.[422644]	>>	 




#####
#####

11:28:59.[428298]	>>	R:	<VT>MSH|^~\&|||||20240116113214||ORU^R01|60|P|2.3.1||||0||ASCII|||<CR>PID|24|2565011461|||นส.<SP>วรรณนิศา<SP>วรสินวิวัฒน์|||F|||ผู้ป่วยนอก||||||||||||||||||||<CR>OBR|24|240116032002|25|^|N|20240116104807|20240116104807|20240116104807||1^28||||20240116104807|Serum||ผู้ป่วยนอก||||||||5|||||||||||||||||||||||<CR>OBX|1|NM|FT3|FT3|3.66|pg/mL|-|N|||F||3.660317|20240116113214|||0|<CR><FS><CR>

11:28:59.[438299]	>>	 

11:28:59.[438299]	>>	======================================================================

11:28:59.[448301]	>>	         SEND DATA TO ANALYZER: [B4334B09-2A52-4FDA-AC32-DF924254F00A]

11:28:59.[458301]	>>	======================================================================

11:28:59.[468302]	>>	 

11:28:59.[578307]	>>	S:	<VT>MSH|^~\&|||||20240116112859||ACK^R01|60|P|2.3.1||||0||ASCII|||<CR>MSA|AA|60|get<SP>result.|||0|<CR><FS><CR>

11:28:59.[588309]	>>	 

11:28:59.[598310]	>>	======================================================================

11:28:59.[608310]	>>	SAVE DATA TO LIS: [240116032002] - [B4334B09-2A52-4FDA-AC32-DF924254F00A]

11:28:59.[608310]	>>	======================================================================

11:28:59.[618311]	>>	 

11:28:59.[648312]	>>	Insert LN: 2401160320 Barcode: 240116032002 Anacode: FT3 Test Code: CH059 Result: 3.66 Status:  Result Flag: - complete!!!

11:41:50.[978888]	>>

ERROR!!order PT/INR ONLY not PTT()

09:22:48.[810716]	>>	======================================================================

09:22:48.[815805]	>>	                                                GET DATA FROM ANALYZER

09:22:48.[821361]	>>	======================================================================

09:22:48.[826293]	>>	 

09:22:48.[831255]	>>	R:	<ENQ>

09:22:48.[848398]	>>	S:	<ACK>

09:22:48.[856375]	>>	---

09:22:48.[994730]	>>	R:	<STX>1H|\^&|||99^2.00|||||||P|1.00|20240118084708<CR><ETX>21<CR><LF>

09:22:49.[023133]	>>	S:	<ACK>

09:22:49.[032325]	>>	---

09:22:49.[054270]	>>	Mode [P]: Production Mode

09:22:49.[163194]	>>	R:	<STX>2P|1|||^^^<CR><ETX>CD<CR><LF>

09:22:49.[193582]	>>	S:	<ACK>

09:22:49.[203532]	>>	---

09:22:49.[347893]	>>	R:	<STX>3O|1|670219483304|||R<CR><ETX>F0<CR><LF>

09:22:49.[369834]	>>	S:	<ACK>

09:22:49.[380804]	>>	---

09:22:49.[512006]	>>	R:	<STX>4R|1|^^^1|57.0|sec||||F||||<CR><ETX>2D<CR><LF>

09:22:49.[549618]	>>	S:	<ACK>

09:22:49.[563122]	>>	---

09:22:49.[626420]	>>	Insert LN: 6702194833 Barcode: 670219483304 Anacode: 1 Test Code: OL011 Result: 57 Status:  Result Flag: - Flag1: F complete!!!

09:22:49.[886168]	>>	---

09:22:50.[001793]	>>	R:	<STX>5M|1|A|C<CR><ETX>BB<CR><LF>

09:22:50.[025731]	>>	S:	<ACK>

09:22:50.[033966]	>>	---

09:22:50.[176947]	>>	R:	<STX>6R|2|^^^2|5.23|INR||||F||||<CR><ETX>DD<CR><LF>

09:22:50.[206310]	>>	S:	<ACK>

09:22:50.[219075]	>>	---

09:22:50.[280086]	>>	Insert LN: 6702194833 Barcode: 670219483304 Anacode: 2 Test Code: HM045 Result: 5.23 Status:  Result Flag: - Flag1: F complete!!!

09:22:50.[437811]	>>	---

09:22:50.[560267]	>>	R:	<STX>7M|2|A|C<CR><ETX>BE<CR><LF>

09:22:50.[588627]	>>	S:	<ACK>

09:22:50.[601312]	>>	---

09:22:50.[736161]	>>	R:	<STX>0L|1|N<CR><ETX>03<CR><LF>

09:22:50.[770972]	>>	S:	<ACK>

09:22:50.[784905]	>>	---

09:22:50.[923674]	>>	R:	<EOT>

09:22:59.[346006]	>>	 

09:22:59.[351735]	>>	======================================================================

09:22:59.[356728]	>>	                                                GET DATA FROM ANALYZER

09:22:59.[362236]	>>	======================================================================

09:22:59.[367223]	>>	 

09:22:59.[372206]	>>	R:	<ENQ>

09:22:59.[390059]	>>	S:	<ACK>

09:22:59.[398150]	>>	---

09:22:59.[535139]	>>	R:	<STX>1H|\^&|||99^2.00|||||||P|1.00|20240118084719<CR><ETX>23<CR><LF>

09:22:59.[562065]	>>	S:	<ACK>

09:22:59.[571069]	>>	---

09:22:59.[595632]	>>	Mode [P]: Production Mode

09:22:59.[705522]	>>	R:	<STX>2P|1|||^^^<CR><ETX>CD<CR><LF>

09:22:59.[739065]	>>	S:	<ACK>

09:22:59.[747136]	>>	---

09:22:59.[886041]	>>	R:	<STX>3O|1|670219483304|||R<CR><ETX>F0<CR><LF>

09:22:59.[917131]	>>	S:	<ACK>

09:22:59.[931063]	>>	---

09:23:00.[064944]	>>	R:	<STX>4R|1|^^^3|43.0|sec||||F||||<CR><ETX>2A<CR><LF>

09:23:00.[080901]	>>	S:	<ACK>

09:23:00.[088882]	>>	---

09:23:00.[116805]	>>	---

09:23:00.[225514]	>>	R:	<STX>5M|1|A|D<CR><ETX>BC<CR><LF>

09:23:00.[254735]	>>	S:	<ACK>

09:23:00.[262844]	>>	---

09:23:00.[407672]	>>	R:	<STX>6R|2|^^^4|1.50|Ratio||||F||||<CR><ETX>F1<CR><LF>

09:23:00.[433602]	>>	S:	<ACK>

09:23:00.[441579]	>>	---

09:23:00.[463145]	>>	---

09:23:00.[586856]	>>	R:	<STX>7M|2|A|D<CR><ETX>BF<CR><LF>

09:23:00.[615360]	>>	S:	<ACK>

09:23:00.[623114]	>>	---

09:23:00.[767193]	>>	R:	<STX>0R|3|^^^5|28.7|Ref.T||||F||||<CR><ETX>98<CR><LF>

09:23:00.[790163]	>>	S:	<ACK>

09:23:00.[799105]	>>	---

09:23:00.[822734]	>>	---

09:23:00.[954203]	>>	R:	<STX>1M|3|A|D<CR><ETX>BA<CR><LF>

09:23:00.[984525]	>>	S:	<ACK>

09:23:00.[997605]	>>	---

09:23:01.[139234]	>>	R:	<STX>2L|1|N<CR><ETX>05<CR><LF>

09:23:01.[170408]	>>	S:	<ACK>

09:23:01.[181377]	>>	---

09:23:01.[302864]	>>	R:	<EOT>

09:33:26.[735700]	>>	 

09:33:26.[741439]	>>	======================================================================

09:33:26.[746656]	>>	                                                GET DATA FROM ANALYZER

09:33:26.[752218]	>>	======================================================================

09:33:26.[757308]	>>	 

09:33:26.[762294]	>>	R:	<ENQ>

09:33:26.[782471]	>>	S:	<ACK>

09:33:26.[790754]	>>	---

09:33:26.[927056]	>>	R:	<STX>1H|\^&|||99^2.00|||||||Q|1.00|20240118085746<CR><ETX>25<CR><LF>

09:33:26.[958920]	>>	S:	<ACK>

09:33:26.[965901]	>>	---

09:33:26.[988125]	>>	Mode [Q]: QC Result Mode, Ignore!!!

09:33:27.[100549]	>>	R:	<STX>2P|1|||<CR><ETX>B3<CR><LF>

09:33:27.[135208]	>>	S:	<ACK>

09:33:27.[154673]	>>	---

09:33:27.[295887]	>>	R:	<STX>3O|1|12351|||R<CR><ETX>7D<CR><LF>

09:33:27.[321569]	>>	S:	<ACK>

09:33:27.[328550]	>>	---

09:33:27.[460634]	>>	R:	<STX>4R|1|^^^1|14.7|sec||||F||||20240118085746<CR><ETX>FD<CR><LF>

09:33:27.[491131]	>>	S:	<ACK>

09:33:27.[498844]	>>	---

09:33:27.[630424]	>>	R:	<STX>5M|1|A|@<CR><ETX>B8<CR><LF>

09:33:27.[660085]	>>	S:	<ACK>

09:33:27.[668317]	>>	---

09:33:27.[808325]	>>	R:	<STX>6L|1|N<CR><ETX>09<CR><LF>

09:33:27.[832260]	>>	S:	<ACK>

09:33:27.[840673]	>>	---

09:33:27.[975506]	>>	R:	<EOT>

blank RESULT is 0 where CHM grup

UPDATE tbl_lab_setup_Test_Control 
SET Test_Blank_Result_Flag = 0 
WHERE
	Test_Code IN (
	SELECT
		tbl_lab_setup_Test.Test_Code 
	FROM
		dbo.tbl_lab_setup_Test_Control
		INNER JOIN dbo.tbl_lab_setup_Test ON tbl_lab_setup_Test_Control.Test_Code = tbl_lab_setup_Test.Test_Code 
	WHERE
		tbl_lab_setup_Test_Control.Test_Blank_Result_Flag = '1' 
	AND tbl_lab_setup_Test.Test_Group_Code = 'CH' 
	);

Browse more snippets >>