????????SQL?????????????????????????????

????????????????????????????????

CREATE TABLE [dbo].[PerHalfHour]([CallInTime] [smalldatetime] NULL, [Result] [int] NULL);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:20:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:38:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:43:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:49:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:07:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:07:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:10:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:16:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:41:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:44:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:52:00', 1);

?????????????

SELECT CallInTime_Hour,
       CallInTime_Minute,
       Count(*) AS CallInTime_Count
FROM   (SELECT Datepart(HOUR, CallInTime)             AS CallInTime_Hour,
               Datepart(MINUTE, CallInTime) / 30 * 30 AS CallInTime_Minute
			   /*??SQL ??/??=?? ??????????*/
        FROM   [PerHalfHour]
        WHERE  CallInTime >= '2014-01-01'
               AND CallInTime < '2014-01-02') t
GROUP  BY CallInTime_Hour,
          CallInTime_Minute
ORDER  BY CallInTime_Hour,
          CallInTime_Minute

??????

??????????????24???????????????????????

CREATE TABLE [dbo].[Sequence_HalfHour]([sequence_time] [nvarchar](20) NULL,[sequence_hour] [int] NULL,[sequence_minute] [int] NOT NULL);
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'00:00:00~00:29:59', 0, 0);
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'00:30:00~00:59:59', 0, 30);
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'01:00:00~01:29:59', 1, 0);
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'01:30:00~01:59:59', 1, 30);
...
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'23:00:00~23:29:59', 23, 0);
INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'23:30:00~23:59:59', 23, 30);

?????????Left Join??????OK?

SELECT t0.sequence_time            AS ???,
       Isnull(CallInTime_Count, 0) AS ??
FROM   sequence_halfhour t0
       LEFT JOIN (SELECT CallInTime_Hour,
                         CallInTime_Minute,
                         Count(*) AS CallInTime_Count
                  FROM   (SELECT Datepart(HOUR, CallInTime)             AS CallInTime_Hour,
                                 Datepart(MINUTE, CallInTime) / 30 * 30 AS CallInTime_Minute
                          /*??SQL ??/??=?? ??????????*/
                          FROM   [PerHalfHour]
                          WHERE  CallInTime >= '2014-01-01'
                                 AND CallInTime < '2014-01-02') t
                  GROUP  BY CallInTime_Hour,
                            CallInTime_Minute) t1
         ON t0.sequence_hour = t1.CallInTime_Hour
            AND t0.sequence_minute = t1.CallInTime_Minute

?????

?????

???????Result?????SQL?????????