Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another
Subtracting one queried value from another Subtracting one queried value from another
Subtracting one queried value from another
Go Back  Xtreme Visual Basic Talk > > > Subtracting one queried value from another


Reply
 
Thread Tools Display Modes
  #1  
Old 04-12-2006, 10:35 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Question Subtracting one queried value from another


I'm looking for the fastest way to run a query that gets two different values from an Oracle table, and subtracts one from the other. The first is determined by querying a field that identifies one type of data, and the second by querying the same field for another type of data. The two individual queries are:

SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= '01-Dec-2001'
AND CDATE >= '01-Nov-2001'
AND TNAME = 'TRSTXN'

and

SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= '01-Dec-2001'
AND CDATE >= '01-Nov-2001'
AND TNAME = 'TRS554N'

These two queries will each return two rows, as there is one value on the first of each month. What I need is a query that would return two rows, but would give me the value of AS_REP from the first query minus AS_REP from the second query.

I have been trying to write subqueries, but I can't seem to get it right.
Reply With Quote
  #2  
Old 04-12-2006, 12:27 PM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What sub queries have you tried? Basically you are going to have to create an alias column that holds the value of FieldInQuestionA - FieldInQuestionB.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 04-12-2006, 12:33 PM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

I tried:

SELECT XA.CDATE, XA.TNAME, XA.AS_REP - GAS.AS_REP
FROM
(
SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= '01-Dec-2001'
AND CDATE >= '01-Nov-2001'
AND TNAME = 'TRSTXN'
), XA
(
SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= '01-Dec-2001'
AND CDATE >= '01-Nov-2001'
AND TNAME = 'TRS554N') GAS

but I am getting 4 rows with this instead of the 2 that I would like to get. I'm just puzzling over what I need to do to get two rows.
Reply With Quote
  #4  
Old 04-12-2006, 01:09 PM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, little confused, why are you selecting from the same table twice with the same conditions twice? Why not use one query but do this instead:

Code:
 AND TNAME IN ('TRSTXN', 'TRS554N')
Or use the OR statement (TNAME = <this> OR TNAME = <that>)

The only condition here that is changing is what value the TNAME field should be, so just include that in one query definition
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 04-12-2006, 01:19 PM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

I'm trying to subtract the value of AS_REP where TNAME = 'TRS554N' from the value of AS_REP where TNAME = 'TRSTXN'.

For example, using the SQL above, I should get 2 values back for Nov, and 2 values for Dec. The end result that I am looking for is a single value of AS_REP for Nov and a single value for Dec. This single value of AS_REP should be the TRSTXN value of AS_REP minus the TRS554N value of AS_REP.

When I queried the database I found the following values:

CDATE TNAME AS_REP
01-Nov-2006 TRSTXN 226,842
01-Dec-2006 TRSTXN 272,331
01-Nov-2006 TRS554N 17,353
10-Dec-2006 TRS554N 16,652

What I am interested in is the single value for Nov that is represented by:

226,842 - 17,353

and the single value for Dec that is represented by:

272,331 - 16,652

Does this clarify?

Thanks
Reply With Quote
  #6  
Old 04-12-2006, 07:25 PM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Hmmmm.....

Well if there are 15 records, do you need the subtracted median of those 15 records, what if that produces a negative number? The problem is, your example is limited in scope. This assumes the query will always return only two records and when subtracting <fieldA> from <fieldA> you always get a positive number or something actually usable. Your table is going to grow and you are going to have more records.

What is the overall goal here? To get an average of a numeric field? To get the median or averge of a particular field? etc
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 04-13-2006, 07:10 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

The query will be built in a VB.NET application, and will grab a date range (month and year) as input, so the range of dates could be one or more months. The table will always have a record that matches the WHERE clause, as there is another process in place that checks that the table has been updated on the release date for this data.

What we need is the AS_REP value for TRSTXN minus the AS_REP value for TRS554N.

TRS554N is a subset of TRSTXN, so this will never evaluate to a negative number.

The overall goal is thus:

We receive these two values (amongst others) each month into the table CARD_TABLE. We know the value for each of the two items that we are interested in, but what we are interested in is TRSTXN minus TRS554N, as this is not a value that we receive in the table. We want to grab this value programmatically so that it can be displayed in a web page. The web page is used to create charts, and those charts can be created across a wide range of dates according to the user's needs, so the input date range is variable.

Sorry if I'm not explaining this too well. Please let me know if I need to add anything to this.

Thanks for the help!
Reply With Quote
  #8  
Old 04-13-2006, 09:59 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK at runtime do you have the date ranges you need? Or do you have to scan an entire table to pull out all records in the table and only evaluate a specific date range, or all dates in the table? My question is when you need to return this single value do you already have an exclusive subset of data to work with?

From a relational table point of view, I am trying to narrow down when this runs what grouping of data is given to you. Because if you have 100 records and you want to get a specific date range within this subset and evaluate specific records within it, its going to be a messy query since there is no relational or logic breakdown of "only use record x and record y", etc
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 04-13-2006, 10:06 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

At run time the date range will be set by two date pickers on the web page. If the text boxes are validated for correct date format, then the start and end dates should be used in the query. As we don't know the date range until runtime, we will have to run against the entire table. The data is monthly and goes back to 1992, so the maximum number of records that we should expect to return (from a table of approximately 20,000 records) is around 160 of TRSTXN and 160 of TRS554N.

The problem with querying the table in this way is that the table was not designed for this purpose. We can't alter the structure of the table as a lot of other applications query this table, but they are not trying to calculate one value minus another for any given date.
Reply With Quote
  #10  
Old 04-13-2006, 10:25 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, well lets try to piece this together. Given a date range you want to return ONLY records that fall between THAT date range AND equal either TRSTXN or TRS554N. So a base definition could be:

Code:
SELECT
    NameField, DateRange,NumericToSubtract
FROM
    T1
WHERE
    DateRange 
BETWEEN
    TO_DATE('YourPicker1)
AND
    TO_DATE('YourPicker2')
AND
    NameField IN ('TRSTXN','TRS554N')
This subset returns the records between the given dates and that only contain the two text markers we need. But this is just a subset of workable data, now we need to be able to scan within the given range

Lets say this query produces 10 records. 5 records have TRSTXN as the 'NameField' value and 5 have 'TRS554N' as the 'NameField' value. Now the goal is to evaluate these 10 records and produce a final subtraction or value?

The problem is see here is TRSTXN and TRS554N are NOT TWO FIELDS in the same record. This is ONE field, ONE DISTINCT VALUE per record, which could be one of two values...hmmm
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 04-13-2006, 10:37 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

That is the problem I kept coming back to - the fact that they are not two separate fields. I was trying to think of a way of tying the two relevant values together, as they would have the same CDATE value, but I just couldn't think how to write the query.
Reply With Quote
  #12  
Old 04-13-2006, 10:43 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

This assumes the CDate value IS the same, there is no guarantee of that. If I choose the date range of 01/01/2006 to 05/05/2006 the query returns ALL records that fall between this range, there is no guarantee that a record with text value 'TRSTXN' will have a corresponding 'TRS554N' record with the same exact date value, it could fall anywhere in this date range.

I always hate to say there is no T-SQL or P-SQL way to do things, but here I don't see how its feasible.

Oracle is a heavy duty database engine and I think you can still pull this off, but I'm struggling to think of how. You might have to stick your subset of data into a temporary table and iterate this temp table via a cursor and literally determine what the values are in each field.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #13  
Old 04-13-2006, 10:45 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

If this was the intention, you need to relate TRSTXN and TRS554N in some way. If there is no logical connection, and these are just two potential text values in one static field definition, you can't do any sort of logical comparison against them..but I realize this wasn't a design requirement or something you have control over currently
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #14  
Old 04-13-2006, 10:47 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

That is one thing that we can guarantee with this - there is always an equivalent TRS554N for each TRSTXN for any given date - this is something that is validated when the table is populated, and no other process has privileges to update or delete these records.
Reply With Quote
  #15  
Old 04-13-2006, 10:48 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

See if this will work for you?
Code:
SELECT
  XA.CDATE, 
  XA.TNAME, 
  (SELECT ct.AS_REP
  FROM CARD_TABLE ct
  WHERE ct.CDATE = XA.CDATE AND ct.TNAME = 'TRSTXN')
  - (SELECT ct.AS_REP
    FROM CARD_TABLE ct
    WHERE ct.CDATE = XA.CDATE AND ct.TNAME = 'TRS554N')

FROM CARD_TABLE XA
WHERE XA.CDATE <= '01-Dec-2001' AND XA.CDATE >= '01-Nov-2001'
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #16  
Old 04-13-2006, 10:49 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, so if TRS554N exists with a date value of 01/01/2006 there will always be a TRSTXN date value with the same date value of 01/01/2006?

OK, so if we group by date, there will ALWAYS be two records for the given date, correct?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #17  
Old 04-13-2006, 10:52 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

IUnknown:

The goal of this query is to return ONE value (or an alias numeric field within the resultset if thats the only way), which is the subtraction value of the corresponding date record . So we need to return a subset of data, and from this data return an alias field that contains a numeric value.



That's where I'm struggling and trying to piece it together

See the comments from an above post:

Quote:
CDATE TNAME AS_REP
01-Nov-2006 TRSTXN 226,842
01-Dec-2006 TRSTXN 272,331
01-Nov-2006 TRS554N 17,353
10-Dec-2006 TRS554N 16,652

What I am interested in is the single value for Nov that is represented by:

226,842 - 17,353

and the single value for Dec that is represented by:

272,331 - 16,652
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #18  
Old 04-13-2006, 10:52 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

Yes, that can be guaranteed 100%
Reply With Quote
  #19  
Old 04-13-2006, 10:54 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

Code:
SELECT
  (SELECT ct.AS_REP
  FROM CARD_TABLE ct
  WHERE ct.CDATE = XA.CDATE AND ct.TNAME = 'TRSTXN')
  - (SELECT ct.AS_REP
    FROM CARD_TABLE ct
    WHERE ct.CDATE = XA.CDATE AND ct.TNAME = 'TRS554N')

FROM CARD_TABLE XA
WHERE XA.CDATE <= '01-Dec-2001' AND XA.CDATE >= '01-Nov-2001'
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #20  
Old 04-13-2006, 10:55 AM
MKoslof's Avatar
MKoslofSubtracting one queried value from another MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Actually, wait a minute, IUnknown might have it.....
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another Subtracting one queried value from another
Subtracting one queried value from another
Subtracting one queried value from another
 
Subtracting one queried value from another
Subtracting one queried value from another
 
-->