SQL Query or Linq query to get number of Events per Day.

SQL query or linq query to get the number of events per day for each and every record (Id), Event Jupiter, Earth and Mars comes under 7th Date so number of events is 3(considering time).

1. A event can start and end in the same day.

2. A event can start today and end after 3 days.

Table Name: **Events**

Id	Name	StartDateWithTime	EndDateWithTime
1	Sun	2017-11-28 00:00:00.000	2017-11-29 0:00:00.000
2	Moon	2017-11-28 00:00:00.000	2017-12-01 10:00:00.000
3	Earth	2017-11-07 00:00:00.000	2017-11-30 00:40:00.000
4	Jupiter	2017-11-07 00:00:00.000	2017-11-07 20:00:00.000
5	Mars	2017-11-07 10:00:00.000	2017-12-07

SQL query or linq query to get the number of events per day

sir given below the sql code for table and procedure

CREATE TABLE [dbo].[eventscnt](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL,
[StartDateWithTime] [datetime] NULL,
[EndDateWithTime] [datetime] NULL
)

2- Procedure

CREATE PROCEDURE [dbo].[GetRecord]
AS
Begin
create table #tbltemp
(
Createdate datetime,
Countevt int
)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
DECLARE @Count AS INT
Select @StartDate = MIN(StartDateWithTime) from eventscnt
--SET @StartDate = '2015-01-01'
--SET @EndDate = GETDATE()
select @EndDate = MAX(EndDateWithTime) from eventscnt
SET @CurrentDate = @StartDate
set @Count = 0
WHILE (@CurrentDate < @EndDate)
BEGIN
select @Count = Count(*) from eventscnt where @CurrentDate between StartDateWithTime and EndDateWithTime
insert into #tbltemp(Createdate,Countevt)values(@CurrentDate,@Count);
SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101);
END
select * from #tbltemp
END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
Accepted answer
sir given below the sql code for table and procedure

1-
CREATE TABLE [dbo].[eventscnt](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL,
[StartDateWithTime] [datetime] NULL,
[EndDateWithTime] [datetime] NULL
)
2- Procedure
CREATE PROCEDURE [dbo].[GetRecord]
AS
Begin
create table #tbltemp
(
Createdate datetime,
Countevt int
)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
DECLARE @Count AS INT
Select @StartDate = MIN(StartDateWithTime) from eventscnt
--SET @StartDate = '2015-01-01'
--SET @EndDate = GETDATE()
select @EndDate = MAX(EndDateWithTime) from eventscnt
SET @CurrentDate = @StartDate
set @Count = 0
WHILE (@CurrentDate < @EndDate)
BEGIN
select @Count = Count(*) from eventscnt where @CurrentDate between StartDateWithTime and EndDateWithTime
insert into #tbltemp(Createdate,Countevt)values(@CurrentDate,@Count);
SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101);
END
select * from #tbltemp
END
0
Laxmidhar Sahoo8y ago
sir I try to solve
0