SELECT FORMAT (b.time, ‘yyyy-MM-dd’) as date, SUM(b.total) as total
FROM tblBill b JOIN tblStatus s ON b.statusID = s.statusID
WHERE b.statusID = 4
GROUP BY FORMAT (b.time, ‘yyyy-MM-dd’)
Order by FORMAT (b.time, ‘yyyy-MM-dd’) asc
<%
Gson gsonObj = new Gson();
Map<Object, Object> map = null;
List<Map<Object, Object>> list = new ArrayList<Map<Object, Object>>();
String dataPoints = null;
try {
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
Connection connection = DriverManager.getConnection(“jdbc:sqlserver://localhost:1433;databaseName=NestF;instanceName=SQLEXPRESS”, “sa”, “123”);
Statement statement = connection.createStatement();
String xVal, yVal;
ResultSet resultSet = statement.executeQuery(“SELECT FORMAT (b.time, ‘yyyy-MM-dd’) as date, SUM(b.total) as total FROM tblBill b JOIN tblStatus s ON b.statusID = s.statusID WHERE b.statusID = 4 GROUP BY FORMAT (b.time, ‘yyyy-MM-dd’) Order by FORMAT (b.time, ‘yyyy-MM-dd’) asc”);
while (resultSet.next()) {
xVal = resultSet.getString(“date”);
yVal = resultSet.getString(“total”);
map = new HashMap<Object, Object>();
map.put(“x”, java.util.Date.parse(xVal));
map.put(“y”, Float.parseFloat(yVal));
list.add(map);
dataPoints = gsonObj.toJson(list);
}
connection.close();
} catch (SQLException e) {
}
%>
.
