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
  #21  
Old 04-13-2006, 11:01 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


As long as you don't have duplicate records for the same date..I think that query is valid.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #22  
Old 04-13-2006, 11:06 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

Let me fire up Toad and try it.

Thanks
Reply With Quote
  #23  
Old 04-13-2006, 11:16 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

No, that returns 52 blank records for some reason. I don't think it's evaluating the WHERE clause correctly, and thus it is doing NULL - NULL.
Reply With Quote
  #24  
Old 04-13-2006, 11:21 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

Post some sample data from CARD_TABLE and the result you expect to get from the query based on the sample data.
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #25  
Old 04-13-2006, 11:24 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

Quote:
Originally Posted by robojam
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
Does this data help? These are the ones that I played around with earlier.
Reply With Quote
  #26  
Old 04-13-2006, 11:38 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

You need to group by CDate.
Code:
SELECT
  XA.CDATE,
  (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

GROUP BY XA.CDATE

   CDATE            DIFF(AS_REP)
01-DEC-2006 	272,331 - 16,652 = 255679 
01-NOV-2006 	226,842 - 17,353 = 209489
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #27  
Old 04-13-2006, 11:42 AM
robojam robojam is offline
Regular
 
Join Date: Mar 2004
Posts: 94
Default

That's definitely nearer as I get two records, which is what I expect, but it's still not evaluating the AS_REP values.

I think the WHERE ct.CDATE = XA.CDATE clause isn't evaluating correctly, but I'm not sure why.
Reply With Quote
  #28  
Old 04-13-2006, 11:44 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

Try it without the where part (from the main query) and see if you get the results you want. When you have the results you want then work on the where part.

This is needed.: WHERE ct.CDATE = XA.CDATE
__________________
win7 : vs 2008 : .Net 3.5
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
 
-->