Need SQL

Question

  Hi,
         What I need is a SQL which will bring data something like below image, parameter to       SQL will be start date time and end date time.

      Output: count with time interval

 Problem is, I could not understand how should I make legends like below which takes 2 hours interval?

 

Answer 1

CREATE TABLE [dbo].[Sample]( [Data] [int] NULL, [Time] [smalldatetime] NULL) ON [PRIMARY]

CREATE TABLE [dbo].[DataGraph]( [Data] [int] NULL, [FromTime] [smalldatetime] NULL, [ToTime] [smalldatetime] NULL) ON [PRIMARY]
Declare @var1 smalldatetime
Declare @var2 smalldatetime
Declare @var3 smalldatetime
SET @var1 = (Select Top(1) [Time] from Sample order by [Time] Asc)
SET @var2 = (Select Top(1) [Time] from Sample order by [Time] desc)
While @var1 < @var2
Begin
Set @var3 = DateAdd(hh, 2, @var1)
IF (@var3 < @var2)
BEGIN
Insert into DataGraph 
select sum(Data) As DT,@var1,@var3 from Sample where CONVERT(varchar, [Time], 108) between CONVERT(varchar, @var1, 108)  AND CONVERT(varchar, @var3, 108)
Set @var1 = @var3
END
ELSE
BEGIN
Insert into DataGraph
select sum(Data) As DT,@var1,@var3 from Sample where CONVERT(varchar, [Time], 108) between CONVERT(varchar, @var1, 108) AND CONVERT(varchar, @var2, 108)
Set @var1 = @var2
END
End