Polysyncronism

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>

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

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

<html>

<head>

<title>JSON from SQL Server</title>

<hta:application

SINGLEINSTANCE="yes"

MAXIMIZEBUTTON="yes"

try {

self.resizeTo(800, 400);

} catch(e) {

}

</script>

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

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>

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>

<p></p>

</body>

</html>

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

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.

VBScript, Javascript for JSON from SQL Server

Sample net capacity SQL

Site Rebuilt