1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 | <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> |
Sample net capacity SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | CREATE TABLE #Sample ( Status int, Month varchar(5), Capacity int ); Insert into #Sample (Status,Month,Capacity) values (1,'01/16',5) Insert into #Sample (Status,Month,Capacity) values (3,'01/16',2) Insert into #Sample (Status,Month,Capacity) values (1,'02/16',11) Insert into #Sample (Status,Month,Capacity) values (3,'02/16',20) Insert into #Sample (Status,Month,Capacity) values (1,'03/16',8) Insert into #Sample (Status,Month,Capacity) values (3,'03/16',12) Insert into #Sample (Status,Month,Capacity) values (1,'04/16',4) Insert into #Sample (Status,Month,Capacity) values (2,'04/16',10) Insert into #Sample (Status,Month,Capacity) values (3,'04/16',18) Insert into #Sample (Status,Month,Capacity) values (2,'05/16',14) Insert into #Sample (Status,Month,Capacity) values (3,'05/16',37) Insert into #Sample (Status,Month,Capacity) values (2,'06/16',4) Insert into #Sample (Status,Month,Capacity) values (3,'06/16',8) select * from #Sample SELECT a.status, a.month, a.capacity, b.capacity AS total_capacity, a.capacity - b.capacity AS net_capacity FROM #Sample a JOIN #Sample b ON (a.month = b.month) AND (b.status = 3) WHERE a.status IN (1,2); drop table #Sample |
A handy way of calculating based on a transaction file.
Site Rebuilt
I had some problems with my prior wordpress site and needed to rebuild.