T-SQL – Page 2 – Polysyncronism

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)

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

Category: T-SQL

SQL Convert Time to Decimal

VBScript, Javascript for JSON from SQL Server