SQL编程将4行合并为1并添加3列

Question

我有一张叫AllData的桌子。该表有一个名为UTCTimethat的列，该列的时间以每15分钟为单位，开始时间和结束时间如下：

DECLARE @StartTime datetime,
        @EndTime datetime,
        @Timeframe int,
        @ByHour bit = 1;

SET @Timeframe = CAST(700 * RAND() AS int);
SET @Timeframe = CASE WHEN @Timeframe = 0 THEN 1 ELSE @Timeframe END

SET @StartTime = dateadd(hh, -1 * @Timeframe, CAST(CONVERT(varchar(14), getutcdate(), 121) + '00' AS datetime));
SET @EndTime = dateadd(hh, @Timeframe, @StartTime);

UTCTimethat

2013-06-24 23:00:00.000
2013-06-24 23:15:00.000
2013-06-24 23:30:00.000
2013-06-24 23:45:00.000

另一列称为Values

31.2775
37.2275
37.595
35.0975

问题是:我想检查在每小时的时间跨度中是否有NULL或0值！基本上，我想要一个查询，显示一个新的列是1小时的时间和它旁边的两列NoData，零数据就像这样

1 hourTime                                         NoData  ZeroData

2013-06-24 23:00:00.000 - 2013-06-24 23:45:00.000  0       0

NoData和null值是0，因为根据values列，我没有ZeroData或0数据。任何帮助都是非常感谢的。

Answer 1

可能有更好的方法来做到这一点，但这里有一种方法：

select cast(datepart(yyyy, UTCTimethat) as varchar(4)) + '-' +
   cast(datepart(mm, UTCTimethat) as varchar(2)) + '-' +
   cast(datepart(dd, UTCTimethat) as varchar(2)) + ' ' +
   cast(datepart(hh, UTCTimethat) as varchar(2)) + ':00:000 - ' +
   cast(datepart(yyyy, UTCTimethat) as varchar(4)) + '-' +
   cast(datepart(mm, UTCTimethat) as varchar(2)) + '-' +
   cast(datepart(dd, UTCTimethat) as varchar(2)) + ' ' +
   cast(datepart(hh, UTCTimethat) as varchar(2)) + ':45:000' as HourTime,
   sum(case when Values is null then 1 else 0 end) as NoData,
   sum(case when Values = 0 then 1 else 0 end) as ZeroData
from AllData
where isnull(Values, 0) = 0
group by cast(datepart(yyyy, UTCTimethat) as varchar(4)) + '-' +
   cast(datepart(mm, UTCTimethat) as varchar(2)) + '-' +
   cast(datepart(dd, UTCTimethat) as varchar(2)) + ' ' +
   cast(datepart(hh, UTCTimethat) as varchar(2)) + ':00:000 - ' +
   cast(datepart(yyyy, UTCTimethat) as varchar(4)) + '-' +
   cast(datepart(mm, UTCTimethat) as varchar(2)) + '-' +
   cast(datepart(dd, UTCTimethat) as varchar(2)) + ' ' +
   cast(datepart(hh, UTCTimethat) as varchar(2)) + ':45:000'