sqlserver日报 月报 年报

–日报表

–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 )

–月报表

–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 )

–年报表

–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 )