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?

brunda k · Answer

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 Begin Set @var3 = DateAdd(hh 2 @var1) IF (@var3 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