Forum guidelines
AuthorQuestion
Need SQL
Posted on: 14 Mar 2013
  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?

 

"Walking on water and developing software from a specification are easy if both are frozen"If you find my post helpful, Mark it as "This is correct answer" or Rate the post.
AuthorReply
Re: Need SQL
Posted on: 16 Mar 2013  

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



Employers - Post Free Jobs

SPONSORED BY

Custom Software Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.