Category: Code

SQL Convert Time to Decimal

Conversion from Time to Decimal

IF OBJECT_ID('Work_Summary') IS NOT NULL DROP TABLE Work_Summary
IF OBJECT_ID('Job_Details') IS NOT NULL DROP TABLE Job_Details
IF OBJECT_ID('Product') IS NOT NULL DROP TABLE Product

CREATE TABLE Job_Details (
Job_Number int PRIMARY KEY
)

insert into Job_Details (Job_Number) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)

CREATE TABLE Product (
Product_Code char(8) PRIMARY KEY
)

insert into Product (Product_Code) values ('PC_1001'),('PC_1002'),('PC_1003'),('PC_1004'),('PC_1005'),('PC_1006'),('PC_1007'),('PC_1008'),('PC_1009'),('PC_1010'),('PC_1011'),('PC_1012'),('PC_1013'),('PC_1014'),('PC_1015')

CREATE TABLE Work_Summary (
Invoice_No      INT NOT NULL IDENTITY PRIMARY KEY,
Invoice_Date      DATE,
Order_Quantity      INT,
Job_man_hours  TIME,
Job_Desc      CHAR (40),
Labour_Cost_Per_Hour   DECIMAL(9,2),
Job_Number INT REFERENCES Job_Details (Job_Number),
Product_Code CHAR (8) REFERENCES Product (Product_Code),
Total_Cost DECIMAL (9,2)
)



SET IDENTITY_INSERT Work_Summary ON


INSERT INTO Work_Summary 
(Invoice_No,Invoice_Date,Order_Quantity,Job_man_hours,Job_Desc,Labour_Cost_Per_Hour,Job_Number,Product_Code)
VALUES      (1,'2017/05/18',2,'1:20:00','Description',100.00,1,'PC_1001'),
            (2,'2017/05/18',6,'2:30:00','Description',75.00,2,'PC_1002'),
            (3,'2017/05/18',7,'3:10:00','Description',50.00,3,'PC_1003'),
            (4,'2017/05/18',1,'4:35:00','Description',20.00,4,'PC_1004'),
            (5,'2017/05/18',9,'5:50:00','Description',15.00,5,'PC_1005'),
            (6,'2017/05/18',11,'6:30:00','Description',10.00,6,'PC_1006'),
            (7,'2017/05/18',1,'2:00:10','Description',18.95,7,'PC_1007'),
            (8,'2017/05/18',6,'2:30:50','Description',19.99,8,'PC_1008'),
            (9,'2017/05/18',8,'3:00:25','Description',40.00,9,'PC_1009'),
            (10,'2017/05/18',9,'1:30:18','Description',30.00,10,'PC_1010'),
            (11,'2017/05/18',14,'2:00:10','Description',20.95,11,'PC_1011'),
            (12,'2017/05/18',3,'2:30:11','Description',9.99,12,'PC_1012'),
            (13,'2017/05/18',6,'3:00:45','Desription',199.99,13,'PC_1013'),
            (14,'2017/05/18',8,'3:30:34','Description',200.00,14,'PC_1014'),
            (15,'2017/05/18',9,'4:00:54','Description',500.00,15,'PC_1015')

			
SET IDENTITY_INSERT Work_Summary OFF

update Work_Summary set Total_Cost = Labour_Cost_Per_Hour * ((CAST(DATEPART(hh, Job_man_hours) AS float) +
		CAST(DATEPART(mi, Job_man_hours) AS float) / 60 +
		CAST(DATEPART(ss, Job_man_hours) AS float) / 3600)) where invoice_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)

select * from Work_Summary where invoice_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)

Outlook Search with Wildcard

This post was inspired by a stackoverflow – VBA Search in Outlook question.

Here is an example that let’s you do a wildcard search using the Items Restrict Method

Option Explicit
 
Sub Search_Inbox()
 
Dim myOlApp As New Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim filteredItems As Outlook.Items
Dim itm As Object
Dim Found As Boolean
Dim strFilter As String
 
 
Set objNamespace = myOlApp.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
 
strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%sketch%'"
 
Set filteredItems = objFolder.Items.Restrict(strFilter)
 
If filteredItems.Count = 0 Then
    Debug.Print "No emails found"
    Found = False
Else
    Found = True
    ' this loop is optional, it displays the list of emails by subject.
    For Each itm In filteredItems
     Debug.Print itm.Subject
    Next
End If
    
 
'If the subject isn't found:
If Not Found Then
    'NoResults.Show
Else
   Debug.Print "Found " & filteredItems.Count & " items."
    
End If
 
'myOlApp.Quit
Set myOlApp = Nothing
 
End Sub

 

VBScript, Javascript for JSON from SQL Server

<meta http-equiv="x-ua-compatible" content="ie=9" /> 
<html>
      <head>
            <title>JSON from SQL Server</title>
            <hta:application
                  SINGLEINSTANCE="yes"
                  MAXIMIZEBUTTON="yes"
            >

            <script language="javascript">
                  try {
                    self.resizeTo(800, 400);
                   } catch(e) {
                   }
            </script>
            
      
<script language="VBscript">
	
Dim strSQL
    
Sub btnShowJSON()
  ProcessJSON(fnQueryData)
End Sub

function RStoJSON(rs)
    dim sFld,fld,sFlds
    dim sRec,sRecs
    dim sRecordSet
    dim lRecCnt

    sRecordSet = ""
    sRecs = ""
    lRecCnt = 0
    if rs.EOF or rs.BOF then
        RStoJSON = "null"
    else
        do while not rs.EOF and not rs.BOF
            lRecCnt = lRecCnt + 1
            sFlds = ""
            for each fld in rs.Fields
                sFld = """" & fld.Name & """:""" & fld.Value&"" & """"
                sFlds = sFlds & GetDelim(sFlds <> "","fld") & sFld
            next 
            sRec = "{" & sFlds & "}"
            sRecs = sRecs & GetDelim(sRecs <> "", "rec") & sRec
            rs.MoveNext
        loop
        sRecordSet = "( {""Records"": [" & vbCrLf & sRecs & vbCrLf & "], " 
        sRecordSet = sRecordSet & """RecordCount"":""" & lRecCnt & """ } )"
        RStoJSON = sRecordSet
    end if
end Function

' Return comma depending on condition and type fld or rec '
function GetDelim(bResult,sType)
    if bResult Then
        If sType = "fld" then
        GetDelim = ","
        End If
        If sType = "rec" Then
        GetDelim = "," & vbCrLf
        End if
    else
        GetDelim = ""
    end if
end function

Function fnQueryData()

Dim strJSON

Dim oCn,oRs1,strSQL

Set oCn = CreateObject( "ADODB.Connection" )
Set oRs1 = CreateObject( "ADODB.Recordset"  ) 

oCn.ConnectionString = "PROVIDER=SQLOLEDB" & ";Server=JEFFLD-HP\JD01SQL;Database=AdventureWorks2012;Trusted_Connection=Yes;"

oCn.open

strSQL = "select top 5 firstname,lastname,emailaddress from person.person P join person.emailaddress E on E.BusinessEntityID=P.BusinessEntityID"

oRs1.Open strSQL, oCn


strJSON = RStoJSON(oRS1)

' Clean up database resources
oRs1.Close
oCn.Close    
    
fnQueryData = strJSON    
    
End Function




</script>

<script language="javascript" type="text/javascript">
		
			function ProcessJSON(sJSON)
			{
			var rs = eval(sJSON);
			if ( rs ) { // has a non-null value
			  // get the record count
			  var str = rs.RecordCount + " Users:<br/>";
			  // get the data from the records
			  str = "<table border=1>"
			  for ( var recno = 0 ; recno < rs.RecordCount ; recno++ ) {
			    str += "<tr>";
			    str += "<td>"+rs.Records[recno].firstname + "</td>";
			    str += "<td>"+rs.Records[recno].lastname + "</td>";
			    str += "<td>"+rs.Records[recno].emailaddress + "</td>";
			    str += "</tr>";
			  }
			  str+= "</table>"
			  document.getElementById('userlist').innerHTML = str;
			  document.getElementById('json').innerHTML = sJSON;
			} else { // rs = null
			    str = "No users found"
			}
			}

	
</script>
             

</head>          
<body>
    <input type="button" name="btnShowJSON" id="btnShowJSON" value="Show JSON" onclick="btnShowJSON()"></input>
    <p></p>
    <div id="userlist"></div>
    <div id="json"></div>
</body>

</html>