Professional Web Applications Themes

bouncing between servers, table entries - Microsoft SQL / MS SQL Server

We have 2 production servers we run our nightly jobs on for our sales system. One of the thing I have to do is, after the last table is loaded, to run a job that give me a snapshot as of last night's run of what's been ordered, how much, and who it goes to. YTD totals, daily totals, and what's available to ship today. The table's first column is a datetime, and it would have yesterday's date in it. Because we bounce between production servers one day and the next, the table needs to reside on one server, and ...

  1. #1

    Default bouncing between servers, table entries

    We have 2 production servers we run our nightly jobs on for our sales
    system.

    One of the thing I have to do is, after the last table is loaded, to run a
    job that give me a snapshot as of last night's run of what's been ordered,
    how much, and who it goes to. YTD totals, daily totals, and what's available
    to ship today.

    The table's first column is a datetime, and it would have yesterday's date
    in it.

    Because we bounce between production servers one day and the next, the table
    needs to reside on one server, and be updated from both of them as the job
    that runs on alternate days writes to that table.

    The only problem is, is that we occasionally have to re-run jobs in the
    morning, that would entail replacing the table that's out there with
    another, revised one. If we do this, it'd create 2 entries for that same
    day.

    The code I use in SQL to write to the alternate box is:

    insert into [server1].database1.dbo.[daily_order_file]

    a Re-run would cause two rows with 8-18-2003 to be created, with possibly
    differeing data.

    Is there a way to do a check for the date on my new table, and if its there
    to simply do an update of every field on the table with the fields from the
    revised data? If it doesn't exist, I'd want to write out the record.

    The output of this would be pulled into a report output.

    Thanks,

    Steve


    Steve Guest

  2. #2

    Default Re: bouncing between servers, table entries

    Can the following be put in the pseudocode you posted, say as a subselect?

    Here's the code I'm using to write my record:

    The date fields are already declared.

    If today is 8/20/2003, the following date variables would be:

    lastmonthfirst = 7/1/2003 12:00AM
    thismonthfirst = 8/1/2003 12:00AM
    yesterday = 8/20/2003 12:00AM
    daybefore = 8/19/2003 12:00AM
    future1 = 9/1/2003 12:00AM


    insert into [dbserver].mydb.dbo.[_daily_orders]
    select
    ord_date,
    ytd_total_qty =
    (SELECT
    SUM(order_qty)
    FROM sales_order_review_2003
    WHERE
    ord_date <= O.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')),
    ytd_total_val =
    ((SELECT
    SUM(order_val)
    FROM sales_order_review_2003
    WHERE
    ord_date <= O.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')) -
    (SELECT
    SUM(excise_tax_val)
    FROM sales_order_review_2003
    WHERE
    ord_date <= O.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES'))),
    ytd_future_orders_qty =
    (SELECT
    SUM(order_qty - ship_qty)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')),
    ytd_future_orders_val =
    (SELECT
    SUM(order_val - ship_val)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')),
    daily_total_orders_qty = sum(order_qty),
    daily_total_orders_val = sum(order_val),
    daily_future_orders_qty =
    (SELECT
    SUM(order_qty - ship_qty)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')) -
    (SELECT
    SUM(order_qty - ship_qty)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date < o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')),
    daily_future_orders_val =
    (SELECT
    SUM(order_val - ship_val)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')) -
    (SELECT
    SUM(order_val - ship_val)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date < o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')),
    daily_current_orders_qty = -- total daily orders - daily future orders
    sum(order_qty) -
    ((SELECT
    SUM(order_qty - ship_qty)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')) -
    (SELECT
    SUM(order_qty - ship_qty)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date < o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES'))),
    daily_current_orders_val = -- total daily orders - daily future orders
    sum(order_val) -
    ((SELECT
    SUM(order_val - ship_val)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date <= o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')) -
    (SELECT
    SUM(order_val - ship_val)
    FROM sales_order_review_2003
    WHERE
    req_ship_date >= future1 and
    ord_date < o.ord_date and
    (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES'))),
    sum(excise_tax_val) as excise_tax_val,
    reg_order_qty = sum(order_qty),
    reg_ship_qty = sum(ship_qty),
    converted_order_qty = convert(real,sum(order_qty)),
    converted_ship_qty = convert(real,sum(ship_qty)),
    shipqty_divided_by_ordqty =
    convert(real,sum(ship_qty))/convert(real,sum(order_qty)),
    times_excise_tax_val =
    (convert(real,sum(ship_qty))/convert(real,sum(order_qty)) *
    sum(excise_tax_val)),
    sched_delivery_qty = sum(delv_note_qty) - sum(ship_qty),
    sched_delivery_val = sum(delv_note_val) - sum(ship_val)
    from
    sales_order_review_2003 as o
    WHERE
    ord_date between daybefore and yesterday
    and (sales_grp <> 'MIS' or sales_rep_cd <> 'SAMPLES')
    GROUP BY ord_date
    order by ord_date

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    possibly 
    >
    > Yes, that is possible. Here is an outline:
    >
    > IF EXISTS (SELECT * FROM tbl WHERE datecol = tonight)
    > BEGIN
    > UPDATE tbl
    > SET ....
    > WHERE datecol = date
    > END
    > ELSE
    > BEGIN
    > INSERT tbl (...)
    > SELECT ...
    > END
    >
    > Now if you insert more than one row at a time, you be have to write
    > complex join condition, which is particularly tricky if a re-run could
    > lead to a new number of rows. In this case, it might be better to use
    > this simple strategy:_
    >
    > BEGIN TRANSACTION
    > DELETE tbl WHERE datecol = datecol
    > INSERT tbl SELECT...
    > COMMIT TRANSACTION
    >
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Steve Guest

  3. #3

    Default Re: bouncing between servers, table entries

    [posted and mailed]

    Steve Chatham (com) writes: 

    Give than long SELECT statement I would be very tempted to delete the
    rows in the targate table first. Not the least because this is a remote
    table.

    Else I would insert the data into temp table, and use the INSERT/UPDATE
    scheme I suggested from the temp table only.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Missing Table Entries
    By Jerim79 in forum MySQL
    Replies: 2
    Last Post: December 30th, 02:58 AM
  2. Age old table bouncing question
    By Nicholas M in forum Macromedia Dynamic HTML
    Replies: 3
    Last Post: September 18th, 10:33 AM
  3. Replies: 1
    Last Post: July 10th, 11:08 AM
  4. Multiple Sub Table Entries
    By sbowker in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 27th, 10:21 PM
  5. Form - table entries
    By Jim Kennedy in forum Microsoft Access
    Replies: 3
    Last Post: July 10th, 06:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139