How to run 2 queries every month

Question

How to run 2 queries every month

jayoub

SSCertifiable

Points: 6991
More actions
December 7, 2010 at 12:54 pm

#230168

I am a Newbi DBA and I manage a db that requires that i run two update queries around the first of every month. I have both queries saved (a senior dba wrote them) and all i do is copy and paste the queries one at at time into a query window and run them. The only glitch is that for both i have to add the the current Period (Period = YYYYMM example for December 2010 it would be 201012) to the query before i run it.
Is there a way i can configure it so that i can run something and a box would pop up asking for the period, we input the period and click ok and then the queries run. I beleive i would need a Stored Procedure, but not sure and i dont know how to get the Period in.
I could grab the period from the PERIOD table which alway has the most current PERIOD as the last record. In other words it would be Current period = 201012.
I can provide you the full query if needed.
Please let me know
Jeff

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Don't think we'll need the full query for this Jay.

You've got a two step process here that you're dealing with. First, you want to get rid of the need for the messagebox to enter in the value. You definately want to connect off the table with your current period since it's already available. This will make your life much easier.

The second piece you're looking for to do scheduling is SQL Agent. This will allow you to create a job with a task for Execute SQL. In there, put the call to your procedures, and set it up to run when you need it to.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jayoub SSCertifiable Points: 6991 More actions · Answer 2

Thanks for your reply

So you are saying that i would create an SQL agent job to run on the first of every month and put the query in the job. i would also get the period from the PERIOD table as well.

I can understand how to create the Job, but how would i Pull the period from the PERIOD table. The Period is at the end of the where clause.

I may get the query and put it in just for you to see what it does.

Jeff

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

jayoub1 (12/7/2010)
Thanks for your reply
So you are saying that i would create an SQL agent job to run on the first of every month and put the query in the job. i would also get the period from the PERIOD table as well.
I can understand how to create the Job, but how would i Pull the period from the PERIOD table. The Period is at the end of the where clause.
I may get the query and put it in just for you to see what it does.

Use a JOIN in your query to the table for the proper field instead of a parameter in the where clause.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jayoub SSCertifiable Points: 6991 More actions · Answer 4

I understand how to join tables, but dont know how it would help. Once the tables are joined what then.

Sorry, I am a newbi

Jeff

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

jayoub1 (12/7/2010)
I understand how to join tables, but dont know how it would help. Once the tables are joined what then.
Sorry, I am a newbi

Alright, let's see what you got. If you need some help check out the first link in my signature. I'll need more details to help you specifically.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jayoub SSCertifiable Points: 6991 More actions · Answer 6

The following are the two queries that i run after the accounting staff closes the books for the month/period. the queries where written by a DBA to change the check number for all the transactions that occured that month. There are two transaction type and one query for each type. So what i do is put in the PERIOD = 201011 for November that just closed and execute. The database is then updated.

I wonder how i can automate this process or make is so that the new network admin (who has no db experience) can just click and do it.

Let me know what you think.

•CHK2DATE:

update cmledg set chkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc ='0000000' and PERIOD = ‘YYYYMM’

I change the period to the period just closed in both queries

DEBIT2DATE

update cmledg set chkdesc='d' + right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc like 'd%' and period='YYYYMM'

Jeff

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

You'd do something like this:

updatec

setchkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2)

FROM

cmledge AS c

JOIN

<periodTable> AS pt

ONc.period = pt.period

wherechkdesc ='0000000'

Note there's some psuedocode in there so you'll have to edit it a bit for the periodtable component.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jayoub SSCertifiable Points: 6991 More actions · Answer 8

I see and will look closer at it.

Thanks for your help

Jeff

gkerscher Ten Centuries Points: 1319 More actions · Answer 9

Another option that will allow you to keep your current update statement unchanged is to declare a variable for 'YYYYMM'.

DECLARE @YYYYMM varchar (6)

--This is where you set the variable equal to the last record in the "periods" lookup table

Select Top 1 @YYYYMM = CurrentPeriod

From LookupTable L

Order by CurrentPeriod Desc

--Update Statement

update cmledg set chkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc ='0000000' and PERIOD = @YYYYMM