Hi all,
We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.
The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.
I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.
example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.
Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.
Thanks for your time. 1 2238
rdraider (rd******@sbcgl obal.net) writes:
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
So I need a way to combine these 2 log entries into a unique occurance.
The ordernr and syscreated could be used to link records. syscreated
always appears to be the same for the 2 log entries down to the second.
Selcode can change from NULL to a number of different values or back to
NULL.Status is either 'A' for approved or 'O' for open. An order can
have many log entries during its life. The selcode may be changed
several times for the same order.
Ideally, I would like a result that links 2 log entries and shows the
status changed from 'A' to 'O' when selcode changed.
Could this do:
SELECT a.ordernr, a.syscreated,
oldselcode = a.old_value, newselcode = a.new_value,
oldstatus = b.old_value, newstatus = b.new_value
FROM ordlog a
JOIN ordlog b ON a.ordernr = b.ordernr
AND datediff(second s, a.syscreated, b.syscreated) <= 1
WHERE a.varname = 'selcode'
AND b.varname = 'status'
AND coalesce(a.old_ value, '') <coalesce(a.new _value, '')
AND a.old_value = 'A'
AND b.new_value = 'B'
Note: this is an untested query.
If the does not return the expected results, I suggest that you post:
o CREATE TABLE statments for the involved table(s).
o INSERT statements with sample data.
o The desired output given the sample.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Reply via newsgroup |
last post by:
Folks,
When performing an update in mysql (using PHP), can I find out how many
records were matched?
mysql_affected_rows() won't work... and I have the following problem
that I thought I could resolve with a simple function:
Example:
I have 50records - I want to update a selection of the recods - some,
|
by: mark |
last post by:
Access2000
How do I write a query that combines the CTC field from each record below into
one record? I need to concatenate the CTC field with a separator, like below:
BattID VehicleID STDATE STTIME CTC
LKO500HF 00000000 10/27/2003 4:13:51 AM 4
LKO500HF 00000000 10/27/2003 5:13:51 AM 5
LKO500HF 00000000 10/27/2003 10:13:51 AM 6
LKO500HF 00000000 10/27/2003 11:13:51 AM 4
|
by: henryopinion |
last post by:
Please help. I've been ramming my head on my keyboard from trying to
figure this problem out.
In a query, I have two tables. One of the tables (table 1) has a unique
identifier and other fields regarding the characteristics of that
record. In the other table (table 2), I have another list of unique
identifiers only. All unique identifiers in this table (table 2)are
contained in the first table. My goal is to exclude any records that
|
by: jhutchings |
last post by:
Hello everyone,
I have a database where I collect shipment data from various tables.
However, I have a problem. Whenever I want to see shipping data for
orders that were set to ship on or before a certain date (in this case
January 30th) the database will return 2 rows for an order as you can
see below.
Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped:
141285 1 1/30/2006 31 10 S15F-55
|
by: musicloverlch |
last post by:
I have a table with multiple records in it. I am being forced to
combine multiple records into one record in order to be uploaded to a
website, and I only get one record per client. How can I
systematically combine multiple records into 1?
The table looks like this
Client ID Service Status
1 Doc Pending
1 Test Pending
| |
by: mindwarp |
last post by:
Hi,
When a user submits / posts data my php script Inserts data into my
database.
If they refresh the script or click back and click submit again I get
duplicate record.
Is there an easy solution to this?
Or do I need to create a hidden field which contains some sort of
|
by: wvmbark |
last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.
Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
|
by: bluereign |
last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script below. My problem is if I don’t use the WHERE clause in my script below, the script can query up to 3 records with the same “LoanNo” due to the fact that there can be up to 3 “TypeCodes” in each Table (Borrower, Co-Borrower 1 and Co-Borrower 2...
|
by: Thomas Arthur Seidel |
last post by:
Hello to all,
I have a small or big problem with a customer data base, where during a change of
system we might have created duplicate records. This should be easy to find, you
might think, but, we are talking about roughly 10000 records or less in a total
volume of 1 MIO records or more.
I have considered a strategy: The station ID and a field with something like a
sequence number are supposed to be unique during that period. The...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |