[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Need Advice Please - Append or Temp Table or Other?

Hello Experts,
                   Here is my scenario. Front End: ASP VB Script. Backend: MS SQL 2000(Version 8). I am new to MS SQL so please bare with me. I have a Table (tblC_REPORT) with the following columns: Entry_ID(PK), CLIENT_ID(FK), ACNT_NUM, DISPUTE_ORDER.
Multiple report items for each client are entered into this table and I need to populate the DISPUTE_ORDER column with an Increment, i.e. 1-100. The increment must be associated with the CLIENT_ID.

Example:

Entry_ID |CLIENT_ID | ACNT_NUM | DISPUTE_ORDER
----------------------------------------------------------------
  E01      |  C01        |   564858     |             1
  E02      |  C02        |   144545     |             2
  E03      |  C03        |   745825     |             3
  E04      |  D01        |   365458     |             1
  E05      |  D02        |   34558       |             2
**************************************************************************************
Each row is entered individually. I do realize I could create a session and enter the DISPUTE_ORDER via a form increment based on inserts for that session, however I want this done all server side within the database.

I have thought of two possible scenarios but do not know how to execute them.
1) Create a Temporary Table and fill the increment there then pull the information over to the C_REPORT table.
2) Make another Table with a DISPUTE_ORDER column and list my increments there then append those integers into C_REPORT.DISPUTE_ORDER where Null equal to CLIENT_ID.

I would like to achieve my results by using a trigger or a stored procedure, however I do appreciate any suggestions you all may have.
I have been a member for a while, but this is my first question I have asked at EE. I would like to take this chance and thank everyone for this awsome community.
Thankyou to everyone here in advance for your help.
I am on a deadline and this is an urgent matter so here is 500 points.



0
Snapp
Asked:
Snapp
1 Solution
 
Scott PletcherSenior DBACommented:
Please try this:


UPDATE tblC_REPORT
SET dispute_order = (
      SELECT COUNT(*)
      FROM tblC_REPORT rep2
      WHERE rep2.client_id = tblC_REPORT.client_id AND rep2.entry_id <= tblC_REPORT.entry_id
    )

0
 
SnappAuthor Commented:
Thank you very much Scott for your fast response. It works flawlessly!
Cheers :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now