300
Reputation: 1031
How to run SQL query repeatedly (in loop) with just two parameters changing for each run
I have a database table "table_name" in SQL Server 2012 and I am using SQL Server Management Studio version 10.
Data in table looks like:
id create_date update_date time_generated kpname machine_name value_type value
10706652 6/23/16 1:08 PM 6/23/16 1:08 PM 6/23/16 1:00 PM Win-RT7_Abc Onetwo_Location BRAABBCCZ02 response_time 18.015
10707166 6/23/16 1:13 PM 6/23/16 1:13 PM 6/23/16 1:05 PM Win-RT7_Abc Onetwo_Location BRAABBCCZ02 response_time 18.313
10707663 6/23/16 1:18 PM 6/23/16 1:18 PM 6/23/16 1:10 PM Win-RT7_Abc Onetwo_Location BRAABBCCZ02 response_time 18
10708157 6/23/16 1:23 PM 6/23/16 1:23 PM 6/23/16 1:15 PM Win-RT7_Abc Onetwo_Location BRAABBCCZ02 response_time 18.328
I have a dirty query that pulls data for past 6 weeks, for same day of the week and same time window of the day. The query is:
--wk1
select *
from table_name
where time_generated >= '2016-06-16 13:17:30'
and time_generated <= '2016-06-16 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
union
--wk2
select *
from table_name
where time_generated >= '2016-06-09 13:17:30'
and time_generated <= '2016-06-09 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
union
--wk3
select *
from table_name
where time_generated >= '2016-06-02 13:17:30'
and time_generated <= '2016-06-02 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
union
--wk4
select *
from table_name
where time_generated >= '2016-05-26 13:17:30'
and time_generated <= '2016-05-26 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
union
--wk5
select *
from table_name
where time_generated >= '2016-05-19 13:17:30'
and time_generated <= '2016-05-19 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
union
--wk6
select *
from table_name
where time_generated >= '2016-05-12 13:17:30'
and time_generated <= '2016-05-12 13:22:29'
and machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
Is there any way I can run this same query (may be in loop or something) for different values of "time_generated"? For example I want to run this query 12 times to get data between 13:17:30 to 14:17:29 over five minute windows.
So I want to run above query 12 times for following values of time generated (date part will remain same, only time part will change)
13:17:30 13:22:29
13:22:30 13:27:29
13:27:30 13:32:29
13:32:30 13:37:29
13:37:30 13:42:29
13:42:30 13:47:29
13:47:30 13:52:29
13:52:30 13:57:29
13:57:30 14:02:29
14:02:30 14:07:29
14:07:30 14:12:29
14:12:30 14:17:29
Please suggest how I can do this and apologies for a lengthy (and probably confusing) question. I tried my best to describe it clearly.
Upvotes: 2
Views: 3616
Answers (3)
M.Ali
Reputation: 69514
Declare Variables
Declare @Date DATE = '2016-06-16';
Declare @Stime TIME = '13:17:30';
Declare @Etime TIME = '13:22:29';
Query
select *
from table_name
where machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
and DATEPART(WEEKDAY,time_generated) = 5 -<-- WEEKDAY is Thursday
AND time_generated >= DATEADD(WEEK, -5,@Date) -<-- Last 6 weeks
and time_generated <= @Date
AND CAST(time_generated AS TIME ) >= @Stime -<-- Time Window Open
AND CAST(time_generated AS TIME ) <= @Etime; -<-- Time Window Close
Upvotes: 2
John Cappelletti
Reputation: 81930
No need to run in a loop or even multiple queries.
Just supply a few parameters
In this example, 1 query 72 subsets
Declare @DateStart Date = '2016-06-16'
Declare @WeeksBack Int = 5
Declare @TimeStart Time = '13:17:30'
Declare @TimeCount Int = 12
Declare @TimeSpan Int = 5
;With cteDate As (Select Wk=1,DateFrom = @DateStart Union All Select Wk=DF.Wk+1,DateFrom= DateAdd(DD, -7, df.dateFrom) From cteDate DF Where DF.DateFrom > DateAdd(WK,@WeeksBack*-1,@DateStart))
,cteTime as (Select RowNr=1,TimeFrom = @TimeStart Union All Select RowNr=Df.RowNr+1,TimeFrom= DateAdd(MINUTE, @TimeSpan, df.TimeFrom) From cteTime DF Where DF.RowNr < @TimeCount)
Select B.*,A.*
From table_name A
Join (Select WkName
,DateR1=cast(KeyDate as datetime)+cast(TimeR1 as DateTime)
,DateR2=cast(KeyDate as datetime)+cast(TimeR2 as DateTime)
From (Select WkName=concat('Week ',Wk),KeyDate=DateFrom from cteDate) A
Join (Select TimeR1=TimeFrom,TimeR2=DateAdd(SS,-1,DateAdd(MINUTE,@TimeSpan,TimeFrom)) from cteTime) B on 1=1
) B on time_generated Between DateR1 and DateR2
Where machine_name like 'BRAABBCCZ0%'
and kpname like '%Win-RT7_Abc Onetwo_%'
and value_type = 'response_time'
The Subquery produces the following
WkName DateR1 DateR2
Week 1 2016-06-16 13:17:30.000 2016-06-16 13:22:29.000
Week 2 2016-06-09 13:17:30.000 2016-06-09 13:22:29.000
Week 3 2016-06-02 13:17:30.000 2016-06-02 13:22:29.000
Week 4 2016-05-26 13:17:30.000 2016-05-26 13:22:29.000
Week 5 2016-05-19 13:17:30.000 2016-05-19 13:22:29.000
Week 6 2016-05-12 13:17:30.000 2016-05-12 13:22:29.000
Week 1 2016-06-16 13:22:30.000 2016-06-16 13:27:29.000
Week 2 2016-06-09 13:22:30.000 2016-06-09 13:27:29.000
Week 3 2016-06-02 13:22:30.000 2016-06-02 13:27:29.000
Week 4 2016-05-26 13:22:30.000 2016-05-26 13:27:29.000
Week 5 2016-05-19 13:22:30.000 2016-05-19 13:27:29.000
Week 6 2016-05-12 13:22:30.000 2016-05-12 13:27:29.000
Week 1 2016-06-16 13:27:30.000 2016-06-16 13:32:29.000
Week 2 2016-06-09 13:27:30.000 2016-06-09 13:32:29.000
Week 3 2016-06-02 13:27:30.000 2016-06-02 13:32:29.000
Week 4 2016-05-26 13:27:30.000 2016-05-26 13:32:29.000
Week 5 2016-05-19 13:27:30.000 2016-05-19 13:32:29.000
Week 6 2016-05-12 13:27:30.000 2016-05-12 13:32:29.000
Week 1 2016-06-16 13:32:30.000 2016-06-16 13:37:29.000
Week 2 2016-06-09 13:32:30.000 2016-06-09 13:37:29.000
Week 3 2016-06-02 13:32:30.000 2016-06-02 13:37:29.000
Week 4 2016-05-26 13:32:30.000 2016-05-26 13:37:29.000
Week 5 2016-05-19 13:32:30.000 2016-05-19 13:37:29.000
Week 6 2016-05-12 13:32:30.000 2016-05-12 13:37:29.000
Week 1 2016-06-16 13:37:30.000 2016-06-16 13:42:29.000
Week 2 2016-06-09 13:37:30.000 2016-06-09 13:42:29.000
Week 3 2016-06-02 13:37:30.000 2016-06-02 13:42:29.000
Week 4 2016-05-26 13:37:30.000 2016-05-26 13:42:29.000
Week 5 2016-05-19 13:37:30.000 2016-05-19 13:42:29.000
Week 6 2016-05-12 13:37:30.000 2016-05-12 13:42:29.000
Week 1 2016-06-16 13:42:30.000 2016-06-16 13:47:29.000
Week 2 2016-06-09 13:42:30.000 2016-06-09 13:47:29.000
Week 3 2016-06-02 13:42:30.000 2016-06-02 13:47:29.000
Week 4 2016-05-26 13:42:30.000 2016-05-26 13:47:29.000
Week 5 2016-05-19 13:42:30.000 2016-05-19 13:47:29.000
Week 6 2016-05-12 13:42:30.000 2016-05-12 13:47:29.000
Week 1 2016-06-16 13:47:30.000 2016-06-16 13:52:29.000
Week 2 2016-06-09 13:47:30.000 2016-06-09 13:52:29.000
Week 3 2016-06-02 13:47:30.000 2016-06-02 13:52:29.000
Week 4 2016-05-26 13:47:30.000 2016-05-26 13:52:29.000
Week 5 2016-05-19 13:47:30.000 2016-05-19 13:52:29.000
Week 6 2016-05-12 13:47:30.000 2016-05-12 13:52:29.000
Week 1 2016-06-16 13:52:30.000 2016-06-16 13:57:29.000
Week 2 2016-06-09 13:52:30.000 2016-06-09 13:57:29.000
Week 3 2016-06-02 13:52:30.000 2016-06-02 13:57:29.000
Week 4 2016-05-26 13:52:30.000 2016-05-26 13:57:29.000
Week 5 2016-05-19 13:52:30.000 2016-05-19 13:57:29.000
Week 6 2016-05-12 13:52:30.000 2016-05-12 13:57:29.000
Week 1 2016-06-16 13:57:30.000 2016-06-16 14:02:29.000
Week 2 2016-06-09 13:57:30.000 2016-06-09 14:02:29.000
Week 3 2016-06-02 13:57:30.000 2016-06-02 14:02:29.000
Week 4 2016-05-26 13:57:30.000 2016-05-26 14:02:29.000
Week 5 2016-05-19 13:57:30.000 2016-05-19 14:02:29.000
Week 6 2016-05-12 13:57:30.000 2016-05-12 14:02:29.000
Week 1 2016-06-16 14:02:30.000 2016-06-16 14:07:29.000
Week 2 2016-06-09 14:02:30.000 2016-06-09 14:07:29.000
Week 3 2016-06-02 14:02:30.000 2016-06-02 14:07:29.000
Week 4 2016-05-26 14:02:30.000 2016-05-26 14:07:29.000
Week 5 2016-05-19 14:02:30.000 2016-05-19 14:07:29.000
Week 6 2016-05-12 14:02:30.000 2016-05-12 14:07:29.000
Week 1 2016-06-16 14:07:30.000 2016-06-16 14:12:29.000
Week 2 2016-06-09 14:07:30.000 2016-06-09 14:12:29.000
Week 3 2016-06-02 14:07:30.000 2016-06-02 14:12:29.000
Week 4 2016-05-26 14:07:30.000 2016-05-26 14:12:29.000
Week 5 2016-05-19 14:07:30.000 2016-05-19 14:12:29.000
Week 6 2016-05-12 14:07:30.000 2016-05-12 14:12:29.000
Week 1 2016-06-16 14:12:30.000 2016-06-16 14:17:29.000
Week 2 2016-06-09 14:12:30.000 2016-06-09 14:17:29.000
Week 3 2016-06-02 14:12:30.000 2016-06-02 14:17:29.000
Week 4 2016-05-26 14:12:30.000 2016-05-26 14:17:29.000
Week 5 2016-05-19 14:12:30.000 2016-05-19 14:17:29.000
Week 6 2016-05-12 14:12:30.000 2016-05-12 14:17:29.000
Upvotes: 1
NickC
Reputation: 42
I decided to try to make this work using Dynamic SQL.
`SET NOCOUNT ON;
DECLARE
@x as varchar(2) = 0,
@sql as VARCHAR(8000),
@date1 as datetime = '2016-05-12 13:17:30',
@date2 as datetime = '2016-05-12 13:22:30'
WHILE @x < 60
BEGIN
SET @sql = 'SELECT *
FROM table_name
WHERE time_generated >= '''+CONVERT(varchar, @date1)+'''
AND time_generated <= ''' + CONVERT(varchar, @date2)+ '''
AND machine_name like ''BRAABBCCZ0%''
AND kpname like ''%Win-RT7_Abc Onetwo_%''
AND value_type = ''response_time'''
Execute(@sql)
SET @date1 = DATEADD(minute,5,@date1)
SET @date2 = DATEADD(minute,5,@date2)
SET @x = @x + 5
END
END`
This should hopefully give you your desired output. You can modify it to increase the Datepart as well if you wanted to run all 6 weeks at the same time.
Every time this query loops the time in incremented by +5 minutes and then the query is executed. You can play with the @x variable to adjust how many times you would like to loop. Hopefully this helps.
Upvotes: 1
Related Questions
- How to add a column with a default value to an existing table in SQL Server?
- How can I add a column that doesn't allow nulls in a Postgresql database?
- sql query to return differences between two tables
- What are the options for storing hierarchical data in a relational database?
- How to query MongoDB with "like"
- How can I do an UPDATE statement with JOIN in SQL Server?
- UPSERT *not* INSERT or REPLACE
- How can I delete using INNER JOIN with SQL Server?
- SELECT INTO a table variable in T-SQL
- SQL Server FOR EACH Loop