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

Need SQL

Answers (1)