structures

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();
        });
      }
    });
  });
}

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

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'

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

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

 [SOURCE CODE]

[WARD]

[PRIORITY]

[PATIENT TYPE CODE]

[YEAR]

[SEX]

[REQ ITEM ORDER DATETIME]

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

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

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]	>>	 

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>

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' 
	);

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

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

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