1) SQL การคำนวณช่วงเวลา
DECLARE @startTime DATETIME = '13:00:00.0000000',
@endTime DATETIME = '14:30:00.0000000'
SELECT CAST(
RIGHT('00' + CAST(((DATEDIFF(SECOND,@startTime,@endTime)/ 3600%24)) AS VARCHAR),2)+'.'+
RIGHT('00' + CAST((DATEDIFF(SECOND,@startTime,@endTime) % 3600) / 60 AS VARCHAR),2)
) AS decimal(6,2))
Result = 1.30
(ref = https://social.msdn.microsoft.com/Forums/sqlserver/en-US/37e6e2a8-1bfb-4caf-a6a8-b3bb65fa974e/need-time-difference-in-hours-and-minutes?forum=transactsql)
2. PIVOT SQL
SELECT first_column AS <first_column_alias>
, [pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM
(<source_table>) AS <source_table_alias>
PIVOT
(
aggregate_function(<aggregate_column>)
FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS <pivot_table_alias>;
aggregate_function eg. SUM(), COUNT(), MAX()...
3. การเช็คเงื่อนไข IF DBNull.Value ใน VB.net ก็ทำบันทัดเดียวได้
เดิม
dim dr As DataRow
If dr.IsareaNull Then
txt_area.Text = Nothing
Else
txt_area.Text = dr.area
End If
ใหม่
Dim dr As SqlDataReader = cmd.ExecuteReader()
txt_area.Text = If(dr("area") Is DBNull.Value, "", dr("area"))
4. SQL select where with @parameter is Null
ลด Code ยาว ๆ ให้สั้นลง โดยใช้
Where (@parameter IS NULL) OR (buildingName Like '%' + @parameter + '%')
แทนการ IF Else