How to run 2 queries every month

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • I see and will look closer at it.

    Thanks for your help

    Jeff

  • 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

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

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