dates in two seperate tables (subtracting & storing temporary) 1

[Delphiwhat]

Folks please help!! its driving me mad


I have 2 tables to store 'complaint information'

Table A only stores the latest complaint entry
Table B stores the history of the complaints (ie there could be 100 records with the same complaint_id_key)

eg table A fields are

complaint_ID_key, date_logged, Complaint_text


eg table B fields are

complaint_ID_key, date_logged, Complaint_text

Part 1
--------
Basically what i want to do is lookup table A to grab any complaint_id_key

then look up table B for this complaint_id_key and order the records by date - then grab the date of the record that was first logged.

Part2
-------

Subtract the latest date in table A from the earliest date in table B to give me a value in days. This will tell me
how long the complaint has been on the system

Any ideas would be greatly appreciated - i'm stumped I've tried everything but cant find a way of storing a temporary variable in SQL

J

 

[SantaMufasa]

Delphi,

I see a lot of "how" to solve your problem in your specifications, but I'm not sure "what" you want. I infer that "what" you want is to display how many days the complaint has been in the system.

You can accomplish this using either a PL/SQL function (or procedure) or a SQL SELECT. Since I don't know the execution context you need, I'll use SQL*Plus:

accept complaint prompt "Which complaint do you wish to age?: "
select curr.date_logged-hist.date_logged
from table_b hist,table_a curr
where curr.complaint_id_key = '&complaint'
  and hist.complaint_id_key = '&complaint'
  and hist.date_logged = (select min(date_logged)
				from table_b
				where complaint_id_key = '&complaint')
/

If you need the code in some other context, please advise. Also, let us know is this is what you wanted.

Dave
Sandy, Utah, USA @ 17:58 (24Nov03) GMT, 10:58 (24Nov03) Mountain Time

 

[Delphiwhat]

hi Dave

Yea basically you are right. I'm runing this SQL to populate/buld a datawindow in Powerbuild 7 language (basically the SQL statement builds the window upon execution). So my next question would be the final value from the SELECT STATEMENT can i pop this i into a temporary variable using SQL so that it can be used to populate each row.?????

The datawindow is just rows of records

 

[SantaMufasa]

Delphi,

Here is some revised code that closer approximates your PowerBuilder7 environment:

create or replace function Age_complaint (complaint in varchar2) return number is
	age_hold	number;
begin
	select curr.date_logged-hist.date_logged into age_hold
		from table_b hist,table_a curr
		where curr.complaint_id_key = complaint
		  and hist.complaint_id_key = complaint
		  and hist.date_logged = (select min(date_logged)
						from table_b
						where complaint_id_key = complaint);
	return age_hold;
exception
	when others then
		return null;
end;
/
var PowerBuild7ComplaintAge number
exec :PowerBuild7ComplaintAge := Age_complaint(1)
select :PowerBuild7ComplaintAge from dual;

The "exec" command represents the population of the variable that I presume you would use in your PowerBuilder.

If you have questions about the above code, please reply.

Dave
Sandy, Utah, USA @ 01:06 (25Nov03) GMT, 18:06 (24Nov03) Mountain Time

 

[Delphiwhat]

Hi Dave

Is there anyway of purely doing this code in SQL so that It can be executed in the construction of a PB Datawindow. I dont believe i can store a temporary variable just using SQL in a PB datawindow.

thanks for your help

jc

 

[SantaMufasa]

Sure, Delphi, the SQL*Plus variable I used PowerBuild7ComplaintAge) to illustrate proof-of-concept for the function, above, is optional. Instead, you can use/refer to the "Age_complaint(complain_id)" function in the PB datawindow just as you would any other Oracle built-in function such as SYSDATE, AVG(some_numeric_column), or SUBSTR(some_string, start, length).

Let us know if this is enough information to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:56 (03Dec03) GMT, 03:56 (03Dec03) Mountain Time)

 

[Delphiwhat]

Dave
PLEASE CUT AND PASTE INTO NOTEPAD TO GET THE FORMATTING CORRECT
Maybe I'm making things a little complex... I'll try to explain what i'm trying to do....
maybe this would make things clearer.
cheers
jc

I've two tables as follows

TABLE A
========
FIELDS & DATA

COMP_ID COMP_DATE COMMENT
55887 03/12/03 11:59:10 BLA BLA


TABLE B
========
FIELDS & DATA


COMP_ID COMP_DATE COMMENT

55887 02/12/03 01:15:23 BLA
55887 09/06/03 04:15:12 BLABLA
55887 03/10/03 06:35:23 BLABLABLA
55887 01/03/03 02:15:10 BLA
55887 16/11/03 02:15:15 BLA
55887 02/06/03 08:25:23 BLABLA



in table A only one record exists per unique COMP_ID at any one time

in table B several records can exists per unique COMP_ID at any one time this is because Table B is used to hold all
the old table A records.

So whats the expected output.... I am trying to make a sql query that I pop into a datawindow that will retrieve all fields for a particular record fromTable A and
include the COMP_DATE field from Table B where the COMP_DATE field is the earliest record.
So for the example tables above the expected result would be.....


COMP_ID COMP_DATE COMMENT COMP_DATE
55887 03/12/03 11:59:10 BLA BLA 01/03/03 02:15:10
|---------------------------------------------------------------| |-----------------------------------|
comes from TABLE A Comes From Table B

 

[SantaMufasa]

So, Delphi, I believe that the code from the last reply of November 24 show a SQL join that you can use to accomplish what appears in your most recent output specification. (I know nothing of PowerBuilder and its datawindows, so you'll need to adjust the code to fit your PB needs.) But tailoring this join should do what you want:

    select <your output fields from both tables>
        from table_b hist,table_a curr
        where curr.complaint_id_key = <desired complaint ID>
          and hist.complaint_id_key = <desired complaint ID>
          and hist.date_logged = (select min(date_logged)
                        from table_b
                        where complaint_id_key = complaint);

Also a p.s. for your future Tek-Tips formatting needs...If you want to post something in Tek-Tips that relies upon retained formatting/column alignment, instead of having people copy and paste into Notepad, you can instead make it happen right in the Tek-Tips reply by using Tek-Tips's on-board formatting capabilities. You can read more about the formatting by hitting the [Preview Post] button, then referring to the formatting codes at the bottom of that screen. (Fundamentally, you place the words "code" and "/code" within square brackets "[...]" to produce the effect you want.)

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:33 (03Dec03) GMT, 11:33 (03Dec03) Mountain Time)

 

[Delphiwhat]

Thanks Dave thats worked with slight mod. Thanks for all the helpful advice and tips for formatting. You have the patience of a saint!!!!

cheeers!!!

 

[carp]

That's why his first name is Santa!!