UPDATE Source_Definition

PHOTO

Wed Aug 16 2023 09:40:38 GMT+0000 (Coordinated Universal Time)

Saved by @HUMRARE7 #sql #vba

UPDATE dbo.tbl_lab_setup_Source_Definition
SET Source_Definition_Name = CASE

			WHEN Source_Definition_Code = '000'	  THEN		'ไม่ทราบฝ่าย'
			WHEN Source_Definition_Code = '001'   THEN      'ความดัน'
			WHEN Source_Definition_Code = '002'   THEN      'เบาหวาน'
			WHEN Source_Definition_Code = '003'   THEN      'โรคปอด'
			WHEN Source_Definition_Code = '004'   THEN      'จุดซักประวัติ VIP'
			WHEN Source_Definition_Code = '005'   THEN      'ฝ่ายทันตสาธารณสุข'
			WHEN Source_Definition_Code = '006'   THEN      'หน้าห้องฉุกเฉิน'
			WHEN Source_Definition_Code = '007'   THEN      'ห้อง LAB'
			WHEN Source_Definition_Code = '008'   THEN      'ฝ่าย Computer'
			WHEN Source_Definition_Code = '009'   THEN      'ตึกผู้ป่วยใน'
			WHEN Source_Definition_Code = '010'   THEN      'จุดซักประวัติOPD1'
			WHEN Source_Definition_Code = '011'   THEN      'ห้อง Emergency Room (ER)'
			WHEN Source_Definition_Code = '012'   THEN      'ฝ่าย X-Ray'
			WHEN Source_Definition_Code = '013'   THEN      'ฝ่ายเวชระเบียน'
			WHEN Source_Definition_Code = '014'   THEN      'ห้องตรวจโรค'
			WHEN Source_Definition_Code = '015'   THEN      'CUPS'
			WHEN Source_Definition_Code = '016'   THEN      'ห้องจ่ายเงินผู้ป่วยนอก'
			WHEN Source_Definition_Code = '017'   THEN      'ห้องคลอด'
			WHEN Source_Definition_Code = '018'   THEN      'ห้องจ่ายกลาง'
			WHEN Source_Definition_Code = '019'   THEN      'ห้องตรวจโรค1'
			WHEN Source_Definition_Code = '020'   THEN      'ห้องตรวจโรค2'
			WHEN Source_Definition_Code = '021'   THEN      'ห้องตรวจโรค 3'
			WHEN Source_Definition_Code = '022'   THEN      'ห้องงานสุขศึกษาประชาสัมพันธ์'
			WHEN Source_Definition_Code = '023'   THEN      'จุดชักประวัติ ARV'
			WHEN Source_Definition_Code = '024'   THEN      'โรงครัว'
			WHEN Source_Definition_Code = '025'   THEN      'ห้องทำหัตถการผู้ป่วยนอก'
			WHEN Source_Definition_Code = '026'   THEN      'จุดซักประวัติOPD2'
			WHEN Source_Definition_Code = '027'   THEN      'ห้องตรวจโรค 4'
			WHEN Source_Definition_Code = '028'   THEN      'ห้องตรวจโรค 5'
			WHEN Source_Definition_Code = '029'   THEN      'ห้องตรวจโรค 6'
			WHEN Source_Definition_Code = '030'   THEN      'ห้องจ่ายยาผู้ป่วยนอก'
			WHEN Source_Definition_Code = '031'   THEN      'จุดเรียกคิวสูตินารีเวช'
			WHEN Source_Definition_Code = '032'   THEN      'ห้องตรวจสูตินารีเวช'
			WHEN Source_Definition_Code = '033'   THEN      'ห้องตรวจโรคหัวใจ'
			WHEN Source_Definition_Code = '034'   THEN      'ห้องตรวจสุขภาพเด็กดี'
			WHEN Source_Definition_Code = '035'   THEN      'งานป้องกันโรค'
			WHEN Source_Definition_Code = '036'   THEN      'ห้องตรวจครรภ์'
			WHEN Source_Definition_Code = '037'   THEN      'ห้องตรวจจิตเวช'
			WHEN Source_Definition_Code = '038'   THEN      'NB (ทารกแรกเกิด)'
			WHEN Source_Definition_Code = '039'   THEN      'ห้องตรวจโรค7'
			WHEN Source_Definition_Code = '040'   THEN      'ห้องฉีดยา'
			WHEN Source_Definition_Code = '041'   THEN      'แพทย์แผนไทย'
			WHEN Source_Definition_Code = '042'   THEN      'กายภาพบำบัด'
			WHEN Source_Definition_Code = '043'   THEN      'Asthma'
			WHEN Source_Definition_Code = '044'   THEN      'ห้องตรวจโรค9'
			WHEN Source_Definition_Code = '045'   THEN      'ห้องตรวจโรค ARV'
			WHEN Source_Definition_Code = '046'   THEN      'HHC'
			WHEN Source_Definition_Code = '047'   THEN      'วัยทอง'
			WHEN Source_Definition_Code = '048'   THEN      'ฝ่ายส่งเสริมสุขภาพ'
			WHEN Source_Definition_Code = '049'   THEN      'ห้องตรวจโรค10'
			WHEN Source_Definition_Code = '050'   THEN      'จุดเรียกคิวคลีกนิคพิเศษ'
			WHEN Source_Definition_Code = '051'   THEN      'จุดเรียกคิว OPD'
			WHEN Source_Definition_Code = '052'   THEN      'Exit Nurse'
			WHEN Source_Definition_Code = '053'   THEN      'ห้องฟ้าใส'
			WHEN Source_Definition_Code = '054'   THEN      'ห้องผ่าตัด'
			WHEN Source_Definition_Code = '055'   THEN      'ห้องตรวจโรค8'
			WHEN Source_Definition_Code = '056'   THEN      'จุดซักประวัติตรวจสุขภาพประจำปี'
			WHEN Source_Definition_Code = '057'   THEN      'คลินิกผู้สูงอายุ'
			WHEN Source_Definition_Code = '058'   THEN      'ห้องจ่ายยาคลินิกพิเศษ'
			WHEN Source_Definition_Code = '059'   THEN      'ห้องตรวจธาลัสซีเมียในเด็ก'
			WHEN Source_Definition_Code = '060'   THEN      'คลิกนิคไข้หวัด'
			WHEN Source_Definition_Code = '061'   THEN      'Bloodbank'
			WHEN Source_Definition_Code = '062'   THEN      'จุดซักประวัติกุมารเวชกรรม'
			WHEN Source_Definition_Code = '063'   THEN      'จุดเรียกคิวกุมารเวชกรรม'
			WHEN Source_Definition_Code = '064'   THEN      'ห้องอัลตราชาวด์'
			WHEN Source_Definition_Code = '065'   THEN      'ห้องตรวจคลินิกพิเศษ1'
			WHEN Source_Definition_Code = '066'   THEN      'ห้องตรวจคลินิกพิเศษ2'
			WHEN Source_Definition_Code = '067'   THEN      'ห้องตรวจคลินิกพิเศษ3'
			WHEN Source_Definition_Code = '068'   THEN      'ห้องตรวจคลินิกพิเศษ4'
			WHEN Source_Definition_Code = '069'   THEN      'ห้องตรวจคลินิกพิเศษ5'
			WHEN Source_Definition_Code = '070'   THEN      'ห้องตรวจคลินิกพิเศษ6'
			WHEN Source_Definition_Code = '071'   THEN      'ห้องตรวจคลินิกพิเศษ7'
			WHEN Source_Definition_Code = '072'   THEN      'ห้องตรวจคลินิกพิเศษ8'
			WHEN Source_Definition_Code = '073'   THEN      'จุดเรียกคิวเบาหวาน+ความดัน'
			WHEN Source_Definition_Code = '074'   THEN      'จุดซักประวัติผู้ป่วยนอก2(ผู้ป่วยนัด)'
			WHEN Source_Definition_Code = '075'   THEN      'จุดซักประวัติผู้ป่วยนอก3(ผู้ป่วยมีไข้)'
			WHEN Source_Definition_Code = '076'   THEN      'คลินิกลดพุง'
			WHEN Source_Definition_Code = '077'   THEN      'จุดเรียกคิวผู้สูงอายุ'
			WHEN Source_Definition_Code = '078'   THEN      'Teen friendly'
			WHEN Source_Definition_Code = '079'   THEN      'ห้องตรวจส่งเสริม'
			WHEN Source_Definition_Code = '080'   THEN      'ตึกเด็ก'
			WHEN Source_Definition_Code = '081'   THEN      'ตึกชาย'
			WHEN Source_Definition_Code = '082'   THEN      'ตึกหญิง'
			WHEN Source_Definition_Code = '083'   THEN      'ตึกสูติ-นารีเวช'
			WHEN Source_Definition_Code = '084'   THEN      'ตึกศัลยกรรม'
			WHEN Source_Definition_Code = '085'   THEN      'จุดเรียกคิวศัลยกรรม'
			WHEN Source_Definition_Code = '086'   THEN      'จุดเรียกคิวศัลยกรรมเด็ก'
			WHEN Source_Definition_Code = '087'   THEN      '70 ปีไม่มีคิว'
			WHEN Source_Definition_Code = '088'   THEN      'ห้องวางแผนครอบครัว'
			WHEN Source_Definition_Code = '089'   THEN      'โรคเรื้อรัง'
			WHEN Source_Definition_Code = '090'   THEN      'ห้องตรวจผู้ป่วยCAPD'
			WHEN Source_Definition_Code = '091'   THEN      'ห้องตรวจผู้ป่วยHD'
			WHEN Source_Definition_Code = '092'   THEN      'จุดซักประวัติไตวายเรื้อรัง'
			WHEN Source_Definition_Code = '093'   THEN      'คลินิคพบแพทย์ส่งเสริม'
			WHEN Source_Definition_Code = '094'   THEN      'คลินิคWarfarin'
			WHEN Source_Definition_Code = '095'   THEN      'กลุ่มเสี่ยง-ทางเดินหายใจ'
			WHEN Source_Definition_Code = '096'   THEN      'ศัลยกรรมกระดูก'
			WHEN Source_Definition_Code = '097'   THEN      'ตึกผู้ป่วยพิเศษชั้น6'
			WHEN Source_Definition_Code = '098'   THEN      'จุดซักประวัติธาลัสซีเมียเด็ก'
			WHEN Source_Definition_Code = '099'   THEN      'คลินิกฝังเข็ม'
			WHEN Source_Definition_Code = '100'   THEN      'ERเสริม'
			WHEN Source_Definition_Code = '101'   THEN      'ตึกศัลยกรรมกระดูก'
			WHEN Source_Definition_Code = '102'   THEN      'ตึกICU'
			WHEN Source_Definition_Code = '103'   THEN      'ห้องผ่าตัดเล็ก'
			WHEN Source_Definition_Code = '104'   THEN      'จุดเรียกคิวConsultนอกเวลา'
			WHEN Source_Definition_Code = '105'   THEN      'ห้องให้คำปรึกษา'
			WHEN Source_Definition_Code = '106'   THEN      'ตึกCOHORT'
			WHEN Source_Definition_Code = '107'   THEN      'คลิกนิกARI'
			WHEN Source_Definition_Code = '108'   THEN      'ARI ศูนย์พักคอย'
			WHEN Source_Definition_Code = '109'   THEN      'ห้องตรวจARI'
			WHEN Source_Definition_Code = '110'   THEN      'คลินิกกัญชา(พฤหัสบดี)'
			WHEN Source_Definition_Code = '111'   THEN      'ตึกผู้ป่วยพิเศษชั้น5'
			WHEN Source_Definition_Code = '112'   THEN      'จุดซักประวัติortho'
			WHEN Source_Definition_Code = '113'   THEN      'คลินิควัคซีน Covid 19'
			WHEN Source_Definition_Code = '114'   THEN      'ห้องตรวจเบาหวาน'
			WHEN Source_Definition_Code = '115'   THEN      'ห้องตรวจความดันโลหิตสูง'
			WHEN Source_Definition_Code = '116'   THEN      'ห้องตรวจโรคไตวายเรื้อรัง'
			WHEN Source_Definition_Code = '117'   THEN      'จุดซักประวัติCAPD'
			WHEN Source_Definition_Code = '118'   THEN      'จุดซักประวัติPalliative Care'
			WHEN Source_Definition_Code = '119'   THEN      'ห้องจ่ายยาTeleMedicine'
			WHEN Source_Definition_Code = '120'   THEN      'ตึกCOHORT2(ICUเก่า)'
			WHEN Source_Definition_Code = '121'   THEN      'ตึกCOHORT3(ชั้น6)'
			WHEN Source_Definition_Code = '122'   THEN      'ห้องยาศูนย์พักคอย'
			WHEN Source_Definition_Code = '123'   THEN      'ห้องจ่ายยา NCD'
			WHEN Source_Definition_Code = '124'   THEN      'ห้องจ่ายเงิน NCD'
			WHEN Source_Definition_Code = '125'   THEN      'ห้องจ่ายยาผู้ป่วยใน'
			WHEN Source_Definition_Code = '126'   THEN      'จุดซักประวัติคลินิควัคซีนCovid 19'
			WHEN Source_Definition_Code = '127'   THEN      'จุดซักประวัติ Self Isolation'
			WHEN Source_Definition_Code = '128'   THEN      'ห้องตรวจ Self Isolation'
			WHEN Source_Definition_Code = '129'   THEN      'จุดซักประวัติ Home Isolation'
			WHEN Source_Definition_Code = '130'   THEN      'ห้องตรวจ Home Isolation'
			WHEN Source_Definition_Code = '131'   THEN      'ห้องจำหน่ายผู้ป่วย Home Isolatio'
			WHEN Source_Definition_Code = '132'   THEN      'จุดซักประวัติERบ่ายเสริม'
			WHEN Source_Definition_Code = '133'   THEN      'ห้องตรวจสุขภาพจิตเด็กและวัยรุ่น'
			WHEN Source_Definition_Code = '134'   THEN      'จุดซักประวัติสุขภาพจิต'
			WHEN Source_Definition_Code = '135'   THEN      'จุดซักประวัติฝากครรภ์'
			WHEN Source_Definition_Code = '136'   THEN      'ห้องให้คำปรึกษาสุขภาพจิต'
			WHEN Source_Definition_Code = '137'   THEN      'ห้องรอฉีดยา'
			WHEN Source_Definition_Code = '138'   THEN      'ห้องตรวจพัฒนาการเด็ก'
			WHEN Source_Definition_Code = '139'   THEN      'CTscan'
			WHEN Source_Definition_Code = '999'   THEN      'กลับบ้าน'
			WHEN Source_Definition_Code = '01'    THEN      'ตึกชาย'
			WHEN Source_Definition_Code = '02'    THEN      'ตึกหญิง'
			WHEN Source_Definition_Code = '03'    THEN      'ตึกเด็ก'
			WHEN Source_Definition_Code = '05'    THEN      'ตึกคลอด'
			WHEN Source_Definition_Code = '06'    THEN      'ตึกผู้ป่วยพิเศษชั้น6'
			WHEN Source_Definition_Code = '07'    THEN      'ตึกสูติ-นารีเวช'
			WHEN Source_Definition_Code = '08'    THEN      'ตึกศัลยกรรม'
			WHEN Source_Definition_Code = '09'    THEN      'ตึกศัลยกรรมกระดูก'
			WHEN Source_Definition_Code = '10'    THEN      'ตึกICU'
			WHEN Source_Definition_Code = '11'    THEN      'ตึกติดเชื้อ'
			WHEN Source_Definition_Code = '12'    THEN      'ตึกผู้ป่วยพิเศษชั้น5'
			WHEN Source_Definition_Code = '13'    THEN      'ตึกCOHORT3(ชั้น6)'
			WHEN Source_Definition_Code = '14'    THEN      'ตึกCOHORT2(ICUเก่า)'
			WHEN Source_Definition_Code = '15'    THEN      'CIสังขะ_HI'
			WHEN Source_Definition_Code = '16'    THEN      'CIกระเทียม_KT'
			WHEN Source_Definition_Code = '17'    THEN      'CIทับทัน_TA'
			WHEN Source_Definition_Code = '18'    THEN      'CIเทพรักษา_TR'
			WHEN Source_Definition_Code = '19'    THEN      'CIขอนแตก_KK'
			WHEN Source_Definition_Code = '20'    THEN      'CIพระแก้ว_PK'
			WHEN Source_Definition_Code = '21'    THEN      'CIดม_DM'
			WHEN Source_Definition_Code = '22'    THEN      'CIสะกาด_SK'
			WHEN Source_Definition_Code = '23'    THEN      'CIตาคง_TK'
			WHEN Source_Definition_Code = '24'    THEN      'CIตาตุม+ตาแตรว_TT'
			WHEN Source_Definition_Code = '25'    THEN      'CIบ้านจารย์_BJ'
			WHEN Source_Definition_Code = '26'    THEN      'CIชบ_CP'
			WHEN Source_Definition_Code = '27'    THEN      'CIโดง_DO'
			WHEN Source_Definition_Code = '28'    THEN      'ตึกAKT'
			WHEN Source_Definition_Code = '29'    THEN      'ตึกCOHORT4'
			WHEN Source_Definition_Code = '30'    THEN      'ทารกแรกเกิด(Neonatal ward)'
			
			
	ELSE Source_Definition_Name -- ถ้าไม่ตรงกับเงื่อนไขใด ๆ ให้เก็บค่าเดิม
END,
Source_Group_Code = CASE

			WHEN Source_Definition_Code = '000'	  THEN	'OPD'
            WHEN Source_Definition_Code = '001'   THEN  'OPD'
            WHEN Source_Definition_Code = '002'   THEN  'OPD'
            WHEN Source_Definition_Code = '003'   THEN  'OPD'
            WHEN Source_Definition_Code = '004'   THEN  'OPD'
            WHEN Source_Definition_Code = '005'   THEN  'OPD'
            WHEN Source_Definition_Code = '006'   THEN  'OPD'
            WHEN Source_Definition_Code = '007'   THEN  'OPD'
            WHEN Source_Definition_Code = '008'   THEN  'OPD'
            WHEN Source_Definition_Code = '009'   THEN  'IPD'
            WHEN Source_Definition_Code = '010'   THEN  'OPD'
            WHEN Source_Definition_Code = '011'   THEN  'OPD'
            WHEN Source_Definition_Code = '012'   THEN  'OPD'
            WHEN Source_Definition_Code = '013'   THEN  'OPD'
            WHEN Source_Definition_Code = '014'   THEN  'OPD'
            WHEN Source_Definition_Code = '015'   THEN  'OPD'
            WHEN Source_Definition_Code = '016'   THEN  'OPD'
            WHEN Source_Definition_Code = '017'   THEN  'IPD'
            WHEN Source_Definition_Code = '018'   THEN  'OPD'
            WHEN Source_Definition_Code = '019'   THEN  'OPD'
            WHEN Source_Definition_Code = '020'   THEN  'OPD'
            WHEN Source_Definition_Code = '021'   THEN  'OPD'
            WHEN Source_Definition_Code = '022'   THEN  'OPD'
            WHEN Source_Definition_Code = '023'   THEN  'OPD'
            WHEN Source_Definition_Code = '024'   THEN  'OPD'
            WHEN Source_Definition_Code = '025'   THEN  'OPD'
            WHEN Source_Definition_Code = '026'   THEN  'OPD'
            WHEN Source_Definition_Code = '027'   THEN  'OPD'
            WHEN Source_Definition_Code = '028'   THEN  'OPD'
            WHEN Source_Definition_Code = '029'   THEN  'OPD'
            WHEN Source_Definition_Code = '030'   THEN  'OPD'
            WHEN Source_Definition_Code = '031'   THEN  'OPD'
            WHEN Source_Definition_Code = '032'   THEN  'OPD'
            WHEN Source_Definition_Code = '033'   THEN  'OPD'
            WHEN Source_Definition_Code = '034'   THEN  'OPD'
            WHEN Source_Definition_Code = '035'   THEN  'OPD'
            WHEN Source_Definition_Code = '036'   THEN  'OPD'
            WHEN Source_Definition_Code = '037'   THEN  'OPD'
            WHEN Source_Definition_Code = '038'   THEN  'IPD'
            WHEN Source_Definition_Code = '039'   THEN  'OPD'
            WHEN Source_Definition_Code = '040'   THEN  'OPD'
            WHEN Source_Definition_Code = '041'   THEN  'OPD'
            WHEN Source_Definition_Code = '042'   THEN  'OPD'
            WHEN Source_Definition_Code = '043'   THEN  'OPD'
            WHEN Source_Definition_Code = '044'   THEN  'OPD'
            WHEN Source_Definition_Code = '045'   THEN  'OPD'
            WHEN Source_Definition_Code = '046'   THEN  'OPD'
            WHEN Source_Definition_Code = '047'   THEN  'OPD'
            WHEN Source_Definition_Code = '048'   THEN  'OPD'
            WHEN Source_Definition_Code = '049'   THEN  'OPD'
            WHEN Source_Definition_Code = '050'   THEN  'OPD'
            WHEN Source_Definition_Code = '051'   THEN  'OPD'
            WHEN Source_Definition_Code = '052'   THEN  'OPD'
            WHEN Source_Definition_Code = '053'   THEN  'OPD'
            WHEN Source_Definition_Code = '054'   THEN  'IPD'
            WHEN Source_Definition_Code = '055'   THEN  'OPD'
            WHEN Source_Definition_Code = '056'   THEN  'OPD'
            WHEN Source_Definition_Code = '057'   THEN  'OPD'
            WHEN Source_Definition_Code = '058'   THEN  'OPD'
            WHEN Source_Definition_Code = '059'   THEN  'OPD'
            WHEN Source_Definition_Code = '060'   THEN  'OPD'
            WHEN Source_Definition_Code = '061'   THEN  'OPD'
            WHEN Source_Definition_Code = '062'   THEN  'OPD'
            WHEN Source_Definition_Code = '063'   THEN  'OPD'
            WHEN Source_Definition_Code = '064'   THEN  'OPD'
            WHEN Source_Definition_Code = '065'   THEN  'OPD'
            WHEN Source_Definition_Code = '066'   THEN  'OPD'
            WHEN Source_Definition_Code = '067'   THEN  'OPD'
            WHEN Source_Definition_Code = '068'   THEN  'OPD'
            WHEN Source_Definition_Code = '069'   THEN  'OPD'
            WHEN Source_Definition_Code = '070'   THEN  'OPD'
            WHEN Source_Definition_Code = '071'   THEN  'OPD'
            WHEN Source_Definition_Code = '072'   THEN  'OPD'
            WHEN Source_Definition_Code = '073'   THEN  'OPD'
            WHEN Source_Definition_Code = '074'   THEN  'OPD'
            WHEN Source_Definition_Code = '075'   THEN  'OPD'
            WHEN Source_Definition_Code = '076'   THEN  'OPD'
            WHEN Source_Definition_Code = '077'   THEN  'OPD'
            WHEN Source_Definition_Code = '078'   THEN  'OPD'
            WHEN Source_Definition_Code = '079'   THEN  'OPD'
            WHEN Source_Definition_Code = '080'   THEN  'IPD'
            WHEN Source_Definition_Code = '081'   THEN  'IPD'
            WHEN Source_Definition_Code = '082'   THEN  'IPD'
            WHEN Source_Definition_Code = '083'   THEN  'IPD'
            WHEN Source_Definition_Code = '084'   THEN  'IPD'
            WHEN Source_Definition_Code = '085'   THEN  'OPD'
            WHEN Source_Definition_Code = '086'   THEN  'OPD'
            WHEN Source_Definition_Code = '087'   THEN  'OPD'
            WHEN Source_Definition_Code = '088'   THEN  'OPD'
            WHEN Source_Definition_Code = '089'   THEN  'OPD'
            WHEN Source_Definition_Code = '090'   THEN  'OPD'
            WHEN Source_Definition_Code = '091'   THEN  'OPD'
            WHEN Source_Definition_Code = '092'   THEN  'OPD'
            WHEN Source_Definition_Code = '093'   THEN  'OPD'
            WHEN Source_Definition_Code = '094'   THEN  'OPD'
            WHEN Source_Definition_Code = '095'   THEN  'OPD'
            WHEN Source_Definition_Code = '096'   THEN  'IPD'
            WHEN Source_Definition_Code = '097'   THEN  'IPD'
            WHEN Source_Definition_Code = '098'   THEN  'OPD'
            WHEN Source_Definition_Code = '099'   THEN  'OPD'
            WHEN Source_Definition_Code = '100'   THEN  'OPD'
            WHEN Source_Definition_Code = '101'   THEN  'IPD'
            WHEN Source_Definition_Code = '102'   THEN  'IPD'
            WHEN Source_Definition_Code = '103'   THEN  'IPD'
            WHEN Source_Definition_Code = '104'   THEN  'OPD'
            WHEN Source_Definition_Code = '105'   THEN  'OPD'
            WHEN Source_Definition_Code = '106'   THEN  'IPD'
            WHEN Source_Definition_Code = '107'   THEN  'OPD'
            WHEN Source_Definition_Code = '108'   THEN  'OPD'
            WHEN Source_Definition_Code = '109'   THEN  'OPD'
            WHEN Source_Definition_Code = '110'   THEN  'OPD'
            WHEN Source_Definition_Code = '111'   THEN  'IPD'
            WHEN Source_Definition_Code = '112'   THEN  'OPD'
            WHEN Source_Definition_Code = '113'   THEN  'OPD'
            WHEN Source_Definition_Code = '114'   THEN  'OPD'
            WHEN Source_Definition_Code = '115'   THEN  'OPD'
            WHEN Source_Definition_Code = '116'   THEN  'OPD'
            WHEN Source_Definition_Code = '117'   THEN  'OPD'
            WHEN Source_Definition_Code = '118'   THEN  'OPD'
            WHEN Source_Definition_Code = '119'   THEN  'OPD'
            WHEN Source_Definition_Code = '120'   THEN  'IPD'
            WHEN Source_Definition_Code = '121'   THEN  'IPD'
            WHEN Source_Definition_Code = '122'   THEN  'OPD'
            WHEN Source_Definition_Code = '123'   THEN  'OPD'
            WHEN Source_Definition_Code = '124'   THEN  'OPD'
            WHEN Source_Definition_Code = '125'   THEN  'OPD'
            WHEN Source_Definition_Code = '126'   THEN  'OPD'
            WHEN Source_Definition_Code = '127'   THEN  'OPD'
            WHEN Source_Definition_Code = '128'   THEN  'OPD'
            WHEN Source_Definition_Code = '129'   THEN  'OPD'
            WHEN Source_Definition_Code = '130'   THEN  'OPD'
            WHEN Source_Definition_Code = '131'   THEN  'OPD'
            WHEN Source_Definition_Code = '132'   THEN  'OPD'
            WHEN Source_Definition_Code = '133'   THEN  'OPD'
            WHEN Source_Definition_Code = '134'   THEN  'OPD'
            WHEN Source_Definition_Code = '135'   THEN  'OPD'
            WHEN Source_Definition_Code = '136'   THEN  'OPD'
            WHEN Source_Definition_Code = '137'   THEN  'OPD'
            WHEN Source_Definition_Code = '138'   THEN  'OPD'
            WHEN Source_Definition_Code = '139'   THEN  'OPD'
            WHEN Source_Definition_Code = '999'   THEN  'OPD'
            WHEN Source_Definition_Code = '01'    THEN  'IPD'
            WHEN Source_Definition_Code = '02'    THEN  'IPD'
            WHEN Source_Definition_Code = '03'    THEN  'IPD'
            WHEN Source_Definition_Code = '05'    THEN  'IPD'
            WHEN Source_Definition_Code = '06'    THEN  'IPD'
            WHEN Source_Definition_Code = '07'    THEN  'IPD'
            WHEN Source_Definition_Code = '08'    THEN  'IPD'
            WHEN Source_Definition_Code = '09'    THEN  'IPD'
            WHEN Source_Definition_Code = '10'    THEN  'IPD'
            WHEN Source_Definition_Code = '11'    THEN  'IPD'
            WHEN Source_Definition_Code = '12'    THEN  'IPD'
            WHEN Source_Definition_Code = '13'    THEN  'IPD'
            WHEN Source_Definition_Code = '14'    THEN  'IPD'
            WHEN Source_Definition_Code = '15'    THEN  'IPD'
            WHEN Source_Definition_Code = '16'    THEN  'IPD'
            WHEN Source_Definition_Code = '17'    THEN  'IPD'
            WHEN Source_Definition_Code = '18'    THEN  'IPD'
            WHEN Source_Definition_Code = '19'    THEN  'IPD'
            WHEN Source_Definition_Code = '20'    THEN  'IPD'
            WHEN Source_Definition_Code = '21'    THEN  'IPD'
            WHEN Source_Definition_Code = '22'    THEN  'IPD'
            WHEN Source_Definition_Code = '23'    THEN  'IPD'
            WHEN Source_Definition_Code = '24'    THEN  'IPD'
            WHEN Source_Definition_Code = '25'    THEN  'IPD'
            WHEN Source_Definition_Code = '26'    THEN  'IPD'
            WHEN Source_Definition_Code = '27'    THEN  'IPD'
            WHEN Source_Definition_Code = '28'    THEN  'IPD'
            WHEN Source_Definition_Code = '29'    THEN  'IPD'
            WHEN Source_Definition_Code = '30'    THEN  'IPD'
	ELSE Source_Group_Code -- ถ้าไม่ตรงกับเงื่อนไขใด ๆ ให้เก็บค่าเดิม
END

WHERE Source_Definition_Code IN (
    '000','001','002','003','004','005','006','007','008','009','010','011','012','013','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','036','037','038','039','040','041','042','043','044','045','046','047','048','049','050','051','052','053','054','055','056','057','058','059','060','061','062','063','064','065','066','067','068','069','070','071','072','073','074','075','076','077','078','079','080','081','082','083','084','085','086','087','088','089','090','091','092','093','094','095','096','097','098','099','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','138','139','999','01','02','03','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30' -- รายการ Source_Definition_Code ที่ต้องการอัปเดต
    -- เพิ่มรายการ Source_Definition_Code อื่น ๆ ต่อไป
	
);

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

javascript - Import sql file in node.js and execute against PostgreSQL - Stack Overflow

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

sql.How to get Quarter’s Start and End Date for a given date in Sql Server

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

ping and send Whatsapp message

Function Ping(strip)
Dim objshell, boolcode
Set objshell = CreateObject("Wscript.Shell")
boolcode = objshell.Run("ping -n 1 -w 1000 " & strip, 0, True)
If boolcode = 0 Then
    Ping = True
Else
    Ping = False
End If
End Function
Sub PingSystem()
Dim strip As String
Dim strPhoneNumber As String
Dim strMessage As String
Dim strPostData As String
Dim IE As Object

strPhoneNumber = Sheets("DATA").Cells(2, 1).Value

For introw = 2 To ActiveSheet.Cells(65536, 2).End(xlUp).Row
    strip = ActiveSheet.Cells(introw, 2).Value
    If Ping(strip) = True Then
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)
        ActiveSheet.Cells(introw, 3).Value = "Online"
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)
        
'Send Whatsapp Message
        strMessage = "Ping " & ActiveSheet.Cells(introw, 1).Value & " " & ActiveSheet.Cells(introw, 2).Value & " is Online"
        
'IE.navigate "whatsapp://send?phone=phone_number&text=your_message"
        strPostData = "whatsapp://send?phone=" & strPhoneNumber & "&text=" & strMessage
        Set IE = CreateObject("InternetExplorer.Application")
        IE.navigate strPostData
        Application.Wait Now() + TimeSerial(0, 0, 3)
        SendKeys "~"

        Set IE = Nothing
        
    Else
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)
        ActiveSheet.Cells(introw, 3).Value = "Offline"
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6
        
'Send Whatsapp Message
        strMessage = "Ping " & ActiveSheet.Cells(introw, 1).Value & " " & ActiveSheet.Cells(introw, 2).Value & " is Offline"
        
'IE.navigate "whatsapp://send?phone=phone_number&text=your_message"
        strPostData = "whatsapp://send?phone=" & strPhoneNumber & "&text=" & strMessage
        Set IE = CreateObject("InternetExplorer.Application")
        IE.navigate strPostData
        Application.Wait Now() + TimeSerial(0, 0, 3)
        SendKeys "~"
        Set IE = Nothing
    End If
Next
End Sub

storing selected items from listbox for sql where statement

dim tempo = listbox1.SelectedItems
dim students as string = ""
for each selected in tempo
students = selected.ToString & "," & students
next

SQL How to Sum SELECT

Use Sum aggregate function with case statement as below, if you want sum of those t.value based on t.clock then do group by on that column so that you will get clock while sum of values.

select sum(case when  i.hostid='223344'   and t.itemid = '0223344' then t.value end) as FirstValue,sum(case when  i.hostid='112233' and t.itemid = '0112233' then t.value end) as SecondValue
from hosts h, items i,history_uint t
where i.hostid=h.hostid and t.itemid=i.itemid
If it is based on t.clock then

   select t.clock, sum(case when  i.hostid='223344'   and t.itemid = '0223344' then t.value end) as FirstValue,sum(case when  i.hostid='112233' and t.itemid = '0112233' then t.value end) as SecondValue
    from hosts h, items i,history_uint t
    where i.hostid=h.hostid and t.itemid=i.itemid
    group by t.clock
    0

You can try this below logic-

SELECT i.hostid,t.itemid,t.value as OneValues, t.clock as time
FROM hosts h 
INNER JOIN items i 
    ON i.hostid=h.hostid
    AND i.hostid IN ('223344','112233')
    AND i.itemid IN ('0223344','0112233')
INNER JOIN  history_uint t 
    ON t.itemid=i.itemid

mysql - ALTER TABLE to add a edit primary key - Stack Overflow

ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);

#sql #database #querying-data

SQL Select all columns with GROUP BY one column

https://stackoverflow.com/a/39816161/6942743

select 
				   a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 day'::interval, 'DD-MM-YYYY')  jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
				   a.plafond_or,
				   a.penjaminan nilai_penjaminan,
				   c.is_konvensional,
				   0 nilai_asuransi_1,
					0 nilai_asuransi_2,
				  (a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
				 CASE
				        when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
				 END umur,
				  '--' note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							0 komisi_asuransi
							--(ijp cogar - feebase cogar) x persen komisi

						

                from 
                  v_tr_transaksi_suretyship a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_obligee f on a.id_obligee = f.id_obligee
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	 
	            where 
				--( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND
				--d.id_reference is null and
				--a.flag_deletion is null and
	              -- d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				  a.flag_deletion is null and 		
					d.flag_delete is null and 
					( 
						( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
							( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
						) OR  
						
						( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
					)  
					
	          
				 union all
				 select 
					a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_bank_cabang as nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
					a.plafond_or,
					a.penjaminan nilai_penjaminan,
					c.is_konvensional, 
					0 nilai_asuransi_1,
					0 nilai_asuransi_2,
					(a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
					case
					    when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
					end    
					 umur,
				    '--' note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							0 komisi_asuransi
                from 
                  v_tr_transaksi_suretyship a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	 
	            where
				--	( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND				
				--d.id_reference is null and
				--	a.flag_deletion is null and
	             --  d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				 a.flag_deletion is null and 		
				d.flag_delete is null and 
				( 
					( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
						( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
					) OR  
					
					( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
				) and 
				c.id_mt_suretyship != 9
	              
	                union all
				 select 
					a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_bank_cabang as nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
					a.plafond_or,
					a.penjaminan nilai_penjaminan,
					c.is_konvensional, 
					nilai_asuransi_1,
					nilai_asuransi_2,
					(a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
					case
					    when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
					end    
					 umur,
				    a.catatan note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							nilai_asuransi_2 * 5/100 komisi_asuransi
                from 
                  v_tr_transaksi_suretyship_asuransi a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	          left join (
                    SELECT id_transaksi_suretyship, 
                            sum(nilai_asuransi_1)  nilai_asuransi_1, 
                            sum(nilai_asuransi_2)  nilai_asuransi_2 FROM (
                                SELECT
                                    id_transaksi_suretyship,
                                        CASE 
                                                    WHEN  
                                                        id_asuransi = 1
                                                    THEN
                                                        nilai_asuransi
                                                    ELSE
                                                        0
                                                END AS nilai_asuransi_1,
                                        CASE 
                                                    WHEN  
                                                        id_asuransi = 2
                                                    THEN
                                                        nilai_asuransi
                                                    ELSE
                                                        0
                                                END AS nilai_asuransi_2 
                                FROM
                                tr_transaksi_suretyship_ext	 
                            ) ss GROUP BY id_transaksi_suretyship
                ) i ON a.id_transaksi_suretyship = i.id_transaksi_suretyship
	            where
				--	( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND				
				--d.id_reference is null and
				--	a.flag_deletion is null and
	             --  d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				 a.flag_deletion is null and 		
				d.flag_delete is null and 
				( 
					( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
						( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
					) OR  
					
					( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
				) and 
				c.id_mt_suretyship = 9

How to Search and Destroy Non-SARGable Queries on Your Server - Data with Bert

-- From https://github.com/bertwagner/SQLServer/blob/master/Non-SARGable%20Execution%20Plans.sql
-- This script will check the execution plan cache for any queries that are non-SARGable.
-- It does this by finding table and index scans that contain a scalar operators

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
 
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan],
   sc.value('(.//Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS [Schema], 
   sc.value('(.//Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS [Table], 
   sc.value('(.//Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS [Column] ,
   CASE WHEN s.exist('.//TableScan') = 1 THEN 'TableScan' ELSE 'IndexScan' END AS [ScanType],
   sc.value('(@ScalarString)[1]', 'varchar(128)') AS [ScalarString]
FROM 
	sys.dm_exec_cached_plans AS cp
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
	CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
	CROSS APPLY stmt.nodes('.//RelOp[TableScan or IndexScan]') AS scan(s)
	CROSS APPLY s.nodes('.//ScalarOperator') AS scalar(sc)
WHERE
    s.exist('.//ScalarOperator[@ScalarString]!=""') = 1 
    AND sc.exist('.//Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
	AND sc.value('(@ScalarString)[1]', 'varchar(128)') IS NOT NULL

Get the field type for each column in a table

SELECT column_name as 'Column Name', data_type as 'Data Type'
FROM information_schema.columns
WHERE table_name = 'hrStaff'

mysql - Disable ONLY_FULL_GROUP_BY - Stack Overflow

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SQL Server: Extract Table Meta-Data (description, fields and their data types) - Stack Overflow

SELECT
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = tableProp.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type,
    ColumnDescription = colDesc.ColumnDescription
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties tableProp 
    ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
        AND tableProp.minor_id = 0
        AND tableProp.name = 'MS_Description' 
LEFT JOIN (
    SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription
    FROM sys.columns sc
    INNER JOIN sys.extended_properties colProp
        ON colProp.major_id = sc.object_id
            AND colProp.minor_id = sc.column_id
            AND colProp.name = 'MS_Description' 
) colDesc
    ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
        AND colDesc.name = col.COLUMN_NAME
WHERE tbl.table_type = 'base table'
--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null

sql - How to list active connections on PostgreSQL? - Stack Overflow

SELECT * FROM pg_stat_activity;

Rolling up multiple rows into a single row and column for SQL Server data

SELECT 
   SS.SEC_NAME,
   STUFF((SELECT '; ' + US.USR_NAME 
          FROM USRS US
          WHERE US.SEC_ID = SS.SEC_ID
          ORDER BY USR_NAME
          FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1

Browse more snippets >>