public static DataTable GetPM25ChartsByYear(string year, string seqId)
{
string sql = @"SELECT MAX(EPAir1) AS [max],AVG(EPAir1) AS
[avg],MIN(EPAir1) AS [min],DATEPART(MONTH,EPAddTime) AS [month] FROM
dbo.BASE_EPRecord
WHERE DATEPART(YEAR,EPAddTime)='{0}' AND EPDevSeq='{1}'
GROUP BY DATEPART(MONTH,EPAddTime)
ORDER BY [month] ASC";
return DbHelperSQL.GetDataTable(string.Format(sql, DateTime.Parse(year).Year, seqId));
}
public static DataTable GetPM25ChartsByMonth(string month, string seqId)
{
string sql = @"SELECT MAX(EPAir1) AS [max],AVG(EPAir1) AS
[avg],MIN(EPAir1) AS [min],CAST(CONVERT(varchar(100), EPAddTime, 23) AS
DATETIME) AS addTime FROM dbo.BASE_EPRecord
WHERE DATEPART(MONTH,EPAddTime)={0} AND DATEPART(YEAR,EPAddTime)='{1}' AND EPDevSeq='{2}'
GROUP BY CONVERT(varchar(100), EPAddTime, 23)
ORDER BY addTime ASC";
return DbHelperSQL.GetDataTable(string.Format(sql, DateTime.Parse(month).Month, DateTime.Parse(month).Year, seqId));
}
public static DataTable GetPM25ChartsByDate(string date, string seqId)
{
string sql = @"SELECT MAX(EPAir1) AS [max],AVG(EPAir1) AS
[avg],MIN(EPAir1) AS [min],CAST(CONVERT(varchar(100), EPAddTime, 23)+'
'+CAST(DATEPART(hh,EPAddTime) AS VARCHAR(10))+':00:00' AS DATETIME) AS
addTime FROM dbo.BASE_EPRecord
WHERE CONVERT(varchar(100), EPAddTime, 23)='{0}' AND EPDevSeq='{1}'
GROUP BY CONVERT(varchar(100), EPAddTime, 23)+' '+CAST(DATEPART(hh,EPAddTime) AS VARCHAR(10))+':00:00'
ORDER BY addTime ASC";
return DbHelperSQL.GetDataTable(string.Format(sql, date, seqId));
}