I'm a newbie to all things SQL and searched for an answer before posting. I have the following code which checks for applications in a webpage and covers the last 24 hrs. I need to change it so it checks for the last 4 hrs (rolling window) everytime it runs (hourly). Please advise. Tried adding some variables to restrict the final output but it did just that, give me the last 4 hrs while still checking the full 24 hr window. Changing the variables at the beginning of the code only makes it update whichever hrs are defined but still checking a 24 hr window.
/*Purpose: Run an hourly check of app counts*/
set nocount on
/* Housekeeping and Local Variable declarations
*/
declare @from datetime = DATEADD(hour,-29,GETDATE());
declare @to datetime = DATEADD(hour,-5,GETDATE());
create table #numbers (Number int, Day int, Applications int);
Insert into #numbers (Number, Day, Applications) Values (0,0,0);
Insert into #numbers (Number, Day, Applications) Values (1,0,0);
Insert into #numbers (Number, Day, Applications) Values (2,0,0);
Insert into #numbers (Number, Day, Applications) Values (3,0,0);
Insert into #numbers (Number, Day, Applications) Values (4,0,0);
Insert into #numbers (Number, Day, Applications) Values (5,0,0);
Insert into #numbers (Number, Day, Applications) Values (6,0,0);
Insert into #numbers (Number, Day, Applications) Values (7,0,0);
Insert into #numbers (Number, Day, Applications) Values (8,0,0);
Insert into #numbers (Number, Day, Applications) Values (9,0,0);
Insert into #numbers (Number, Day, Applications) Values (10,0,0);
Insert into #numbers (Number, Day, Applications) Values (11,0,0);
Insert into #numbers (Number, Day, Applications) Values (12,0,0);
Insert into #numbers (Number, Day, Applications) Values (13,0,0);
Insert into #numbers (Number, Day, Applications) Values (14,0,0);
Insert into #numbers (Number, Day, Applications) Values (15,0,0);
Insert into #numbers (Number, Day, Applications) Values (16,0,0);
Insert into #numbers (Number, Day, Applications) Values (17,0,0);
Insert into #numbers (Number, Day, Applications) Values (18,0,0);
Insert into #numbers (Number, Day, Applications) Values (19,0,0);
Insert into #numbers (Number, Day, Applications) Values (20,0,0);
Insert into #numbers (Number, Day, Applications) Values (21,0,0);
Insert into #numbers (Number, Day, Applications) Values (22,0,0);
Insert into #numbers (Number, Day, Applications) Values (23,0,0);
select count(distinct appl_no) as Applications,
datepart(day,ApplicationDate) as day,
datepart(hour,ApplicationDate) as hour
into #records
from apps_table
where ApplicationDate >= @from and ApplicationDate <= @To
group by datepart(day,ApplicationDate), datepart(hour,ApplicationDate)
order by datepart(day,ApplicationDate),datepart(hour,ApplicationDate)
;
update #numbers
set applications= rec.applications, day=rec.day
from #records rec
where Number = rec.hour
;
select *
from #numbers
drop table #records;
drop table #numbers;
/* Final Housekeeping
*/
set nocount off
Assuming I understand the question, I think you just need to change the values ofr the @From and @To variables:
declare @from datetime = DATEADD(hour,-4,GETDATE());
declare @to datetime = GETDATE();
I think I understand the question better now.
Try this instead:
/*Purpose: Run an hourly check of app counts*/
set nocount on
/* Housekeeping and Local Variable declarations
*/
declare @from datetime = DATEADD(hour,-4,GETDATE());
declare @to datetime = GETDATE();
select count(distinct appl_no) as Applications,
datepart(day,ApplicationDate) as day,
datepart(hour,ApplicationDate) as hour
into #records
from apps_table
where ApplicationDate >= @from and ApplicationDate <= @To
group by datepart(day,ApplicationDate), datepart(hour,ApplicationDate)
order by datepart(day,ApplicationDate),datepart(hour,ApplicationDate)
;
SELECT INTO #numbers (Number, Day, Applications)
set rec, applications, day
from #records;
select *
from #numbers
drop table #records;
drop table #numbers;
/* Final Housekeeping
*/
set nocount off