Need sql query

Transact-SQL
1

Hi,

I have a scenario where I need to have this data. I have few columns of data with EnteredDateTime column.

I need to write a query where I have Begin Data and End date as a parameter. I also have Begin Time parameter and hours of length parameter which could be 2 or 3 or so based on 24 hour format.

I need to retrieve a data with each data provided in date range parameter and also create column based on Begin time with that time slot period in 24 hour format.

My Data looks like this

Name EnteredDateTime Status

A 02/01/2017 10:00 Active

B 02/02/2017 12:00 Active

C 02/01/2017 04:00 Active

D 02/01/2017 14:00 Active

E 02/02/2017 15:00 Active

F 02/02/2017 23:00 Active

G 02/03/2017 06:00 Active

H 02/03/2017 18:00 Active

Now I need data in this manner as I pass parameter date range between 01/25/2017 to 02/20/2017 and I pass my begin time as 06:00 for every 2 hours time slot.

Date 06:00-08:00 08:00-10:00 10:00-12:00 12:00-14:00 ......So on until 04:00-06:00

02/01/2017 Count Count Count Count Count

02/02/2017 count Count Count Count Count

02/03/2017 Count Count Count Count Count

I really appreciate if someone can answer me.

Thanks a lot.

2

Just to be sure (because it can make all the difference in the world), are you saying that the "BEGIN" time and the period of each time slot will be variable or are you saying that the times will always be 06:00 up to an not including 18:00 in 2 hour slots?

3

Have your frontend program show correct column names and filter out last 4 columns if time interval is 3 hours.
Otherwise you'd have to write dynamic sql, which is to be avoided where ever you can.

Give this a try:

declare @start_hh time=cast('06:00' as time);
declare @start_dt datetime=cast('2017-01-25' as datetime)+cast(@start_hh as datetime);
declare @end_dt datetime=cast('2017-02-20' as datetime)+cast(@start_hh as datetime);
declare @time_step int=2;

with tally_date(start_dt,end_dt) /* 1000 days (aprox 3 year) */
  as (select dateadd(day,row_number() over(order by n1.n)-1,@start_dt) as start_dt
            ,dateadd(day,row_number() over(order by n1.n),@start_dt) as end_dt
        from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as n1(n)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as n2(n)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as n3(n)
     )
select cast(b.entereddatetime as date) as dt
      ,sum(case when b.entereddatetime>=dateadd(hour,0*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,1*@time_step,a.start_dt) then 1 else 0 end) as period_1
      ,sum(case when b.entereddatetime>=dateadd(hour,1*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,2*@time_step,a.start_dt) then 1 else 0 end) as period_2
      ,sum(case when b.entereddatetime>=dateadd(hour,2*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,3*@time_step,a.start_dt) then 1 else 0 end) as period_3
      ,sum(case when b.entereddatetime>=dateadd(hour,3*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,4*@time_step,a.start_dt) then 1 else 0 end) as period_4
      ,sum(case when b.entereddatetime>=dateadd(hour,4*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,5*@time_step,a.start_dt) then 1 else 0 end) as period_5
      ,sum(case when b.entereddatetime>=dateadd(hour,5*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,6*@time_step,a.start_dt) then 1 else 0 end) as period_6
      ,sum(case when b.entereddatetime>=dateadd(hour,6*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,7*@time_step,a.start_dt) then 1 else 0 end) as period_7
      ,sum(case when b.entereddatetime>=dateadd(hour,7*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,8*@time_step,a.start_dt) then 1 else 0 end) as period_8
      ,sum(case when b.entereddatetime>=dateadd(hour,8*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,9*@time_step,a.start_dt) then 1 else 0 end) as period_9
      ,sum(case when b.entereddatetime>=dateadd(hour,9*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,10*@time_step,a.start_dt) then 1 else 0 end) as period_10
      ,sum(case when b.entereddatetime>=dateadd(hour,10*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,11*@time_step,a.start_dt) then 1 else 0 end) as period_11
      ,sum(case when b.entereddatetime>=dateadd(hour,11*@time_step,a.start_dt) and b.entereddatetime<dateadd(hour,12*@time_step,a.start_dt) then 1 else 0 end) as period_12
  from tally_date as a
       inner join yourtable as b
               on b.entereddatetime>=a.start_dt
              and b.entereddatetime<a.end_dt
 where a.end_dt<@end_dt+2
 group by cast(b.entereddatetime as date)
;
4

I can't agree with that. It's a powerful tool that shouldn't be avoided if needed and can greatly simplify code where it might be avoided by doing something more complicated.. Even MS thinks so or they'd have not made sp_ExecuteSQL do its thing so nicely.

I think the whole notion of avoiding SQL started because of things like not writing the correct kind of Dynamic SQL to prevent SQL Injection and because a lot of people thinks it's a performance problem because they don't know about sp_ExecuteSQL, which will cache execution plans just like regular code does.

5

Heh... on that note, we still need an answer for the questions I asked above so we can figure out whether or not it needs to be dynamic SQL. :wink:

6

Dynamic sql also has security implications which at times can become more contentious than the SQL itself.

7

Other than what I already stated about SQL Injection, what are you specifically talking about?