Run query multiple times using different query values.

Question

Run query multiple times using different query values.

xxxxxx

Ten Centuries

Points: 1080
More actions
August 18, 2011 at 10:50 pm

#246552

I have a need to run a single query 20 times but each time slightly changing 2 of the WHERE values.
Can anyone offer some help how i can do this without writing the entire query 20 times?
Here is the query. I have to change the two ipadderss values each time it runs. I have a delimited CSV file with the 2 IPs. It need to run through 20 times each tmie reading the next 2 IPs in the CSV file. I have to be able ot package this to run as a schedule. Please help.
SELECT TOP 20 scr_dim_site.site_name
WHERE (NOT (scr_fct_web.days_since_epoch <
(SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))/86400)))
AND (NOT (scr_fct_web.days_since_epoch >
(SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))))/86400)))
AND (scr_dim_ipaddress.ipaddress >= '172.016.176.001') AND (scr_dim_ipaddress.ipaddress <= '172.016.183.254')
August 19, 2011 at 3:59 am

This was removed by the editor as SPAM
August 19, 2011 at 5:03 am

This was removed by the editor as SPAM
August 22, 2011 at 1:08 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I have to admit that I'm confused. How are you reading from a CSV file with that code? Or is that a part of the problem you're having?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xxxxxx Ten Centuries Points: 1080 More actions · Answer 2

Jeff Moden (8/18/2011)
I have to admit that I'm confused. How are you reading from a CSV file with that code? Or is that a part of the problem you're having?

Thata what im trying to work out. How to run the script numberous times each time using a value from a list in a seperate file

xxxxxx Ten Centuries Points: 1080 More actions · Answer 3

stewartc-708166 (8/19/2011)
you could consider either using BCP or BULK INSERT to import the flat files into a #Temp table, then use the values from this table as part of your select (by means of JOINs)

Thanks for the input however as im posting in the newbies section your suggestions have gone straight over my head lol. Could you possibly guide me on how to achieve this? Im happy to put the criteria into a table as it wont change very often (if at all).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

-- Let's see what the date arithmetic is for:

select DATEDIFF(mm,0,getdate()) -- months since day 0

select DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0) -- first day of last month

SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0)) -- first day of last month (1st July 2011) with 0 seconds added

SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0)))) -- difference in seconds between 1970-01-01 and 2011-07-01

SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0))))/86400 -- difference in days between 1970-01-01 and 2011-07-01 (15156 days)

SELECT DATEDIFF(dd,'1970-01-01 00:00:00',DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0)) -- difference in days between 1970-01-01 and 2011-07-01 (15156 days)

-- set up as variables

DECLARE @DaysLowerbound INT, @DaysUpperbound INT

SELECT @DaysLowerbound = DATEDIFF(dd,'1970-01-01 00:00:00',DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0))

SELECT @DaysUpperbound = DATEDIFF(dd,'1970-01-01 00:00:00',DATEADD(mm,DATEDIFF(mm,0,getdate()),0))

-- use variables in query

SELECT TOP 20 scr_dim_site.site_name

WHERE scr_fct_web.days_since_epoch BETWEEN @DaysLowerbound AND @DaysUpperbound

AND scr_dim_ipaddress.ipaddress BETWEEN '172.016.176.001' AND '172.016.183.254'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

xxxxxx Ten Centuries Points: 1080 More actions · Answer 5

stewartc-708166 (8/19/2011)
Does the following assist:
CREATE TABLE #IPRanges (
IPFrom CHAR(15),
IPTo CHAR(15)
)
BULK INSERT #IPRanges
FROM '\\Server\Share\FileName'
WITH (
FIELDTerminator = ',')
SELECT TOP 20 scr_dim_site.site_name
FROM scr_dim_site
JOIN #IPRanges ON scr_dim_ipaddress.ipaddress >= IPFrom AND scr_dim_ipaddress.ipaddress <= IPTo
WHERE (NOT (scr_fct_web.days_since_epoch <
(SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))/86400)))
AND (NOT (scr_fct_web.days_since_epoch >
(SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))))/86400)))
--AND (scr_dim_ipaddress.ipaddress >= '172.016.176.001') AND (scr_dim_ipaddress.ipaddress <= '172.016.183.254')

Thanks for the feedback but still not working how i need. Firstly i dont need the tabel to be temporary, in fact it works better for me if its a normal table so i created a table called IPRanges and populated that with column name IPFrom and IPTo and removed the #. It seems to work but only for the first row in the IPRanges table. Doesnt run it for any other rows after row 1. i need to run it using the IPFrom and IPTo values for each row.