current position:Home>SQL Server daily, monthly and annual reports

SQL Server daily, monthly and annual reports

2022-01-27 02:28:34 Danshui Yu

– A daily report

–SELECT DATEADD(HOUR,DATEDIFF(HOUR,‘1970-01-01 00:00:00’,StartTime),‘1970-01-01 00:00:00’) as StartTime,
–SUM( CASE TypeTask WHEN 1 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as QSingleWeight,
–count( CASE TypeTask WHEN 1 THEN TypeTask ELSE NULL END ) as QSingleWeightCount,
–SUM( CASE TypeTask WHEN 3 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as XSingleWeight,
–count( CASE TypeTask WHEN 3 THEN TypeTask ELSE NULL END ) as XSingleWeightCount,
–SUM((EndVolumeEndDensity) -(StartVolumeStartDensity)) as SingleWeight,
–count( CASE TypeTask WHEN 2 THEN null ELSE TypeTask END ) as SingleWeightCount
–FROM table
–WHERE (StartTime BETWEEN ‘2020-01-01 00:00:00’ AND ‘2021-10-30 23:59:00’) and type in (1,3)
–GROUP BY DATEDIFF(HOUR, ‘1970-01-01 00:00:00’, StartTime )

– Monthly report

–SELECT DATEADD(DAY,DATEDIFF(DAY,‘1970-01-01 00:00:00’,StartTime),‘1970-01-01 00:00:00’) as StartTime,
–SUM( CASE TypeTask WHEN 1 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as QSingleWeight,
–count( CASE TypeTask WHEN 1 THEN TypeTask ELSE NULL END ) as QSingleWeightCount,
–SUM( CASE TypeTask WHEN 3 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as XSingleWeight,
–count( CASE TypeTask WHEN 3 THEN TypeTask ELSE NULL END ) as XSingleWeightCount,
–SUM((EndVolumeEndDensity) -(StartVolumeStartDensity)) as SingleWeight,
–count( CASE TypeTask WHEN 2 THEN null ELSE TypeTask END ) as SingleWeightCount
–FROM table
–WHERE (StartTime BETWEEN ‘2020-01-01 00:00:00’ AND ‘2021-10-30 23:59:00’) and type in (1,3)
–GROUP BY DATEDIFF(DAY, ‘1970-01-01 00:00:00’, StartTime )

– Annual report

–SELECT DATEADD(MONTH,DATEDIFF(MONTH,‘1970-01-01 00:00:00’,StartTime),‘1970-01-01 00:00:00’) as StartTime,
–SUM( CASE TypeTask WHEN 1 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as QSingleWeight,
–count( CASE TypeTask WHEN 1 THEN TypeTask ELSE NULL END ) as QSingleWeightCount,
–SUM( CASE TypeTask WHEN 3 THEN (EndVolumeEndDensity) -(StartVolumeStartDensity) ELSE NULL END ) as XSingleWeight,
–count( CASE TypeTask WHEN 3 THEN TypeTask ELSE NULL END ) as XSingleWeightCount,
–SUM((EndVolumeEndDensity) -(StartVolumeStartDensity)) as SingleWeight,
–count( CASE TypeTask WHEN 2 THEN null ELSE TypeTask END ) as SingleWeightCount
–FROM table
–WHERE (StartTime BETWEEN ‘2020-01-01 00:00:00’ AND ‘2021-10-30 23:59:00’) and type in (1,3)
–GROUP BY DATEDIFF(MONTH, ‘1970-01-01 00:00:00’, StartTime )

copyright notice
author[Danshui Yu],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270228327992.html

Random recommended