Hi Friends,
I have a requirement. I have followed few approach, pls assist me.
I have 2 tables T1 And T2. From table T1 I am Fetching DATE which is Integer format like 20080522, now i need i need to fetch date from T2(I need to extract from timestamp) and it gives me in the format: yyyy-mm-dd. (I need to fetch few more data from T2 but i want to use them after satisfying the condition. Condition is date of T2 > date of T1, if yes I need to find out :
1. Difference of timestamps (In and Out)
2. maximum Of the Difference
3. Minimum Of the Difference
4. AVG of of the Differences
5. total records
I have 2 options :-
1. fetch date from T1 move to Ws-variable in yyyy-mm-yy.
fetch date from T2' timestamp in format YYYY-mm-dd,
Compare them, if t2 > T1, fetch one more time T2 with required operstions Like Timestamp diff, MAX, MIN, AVG , Total Records processed. But If I do this How do I Discriminate or store the Modified filds to perform MAX, MIN or AVG.
2. During First Fetc of T2, i can perform All the required operations, but it will calculate for all the data and I want only after a particular date, i.e after last processing.
Please suggest, If I am not clear let me know.
1 more sloution that I can think is to Compare T1 and T2's date, if condition True, store calculate and store the fields in temporary table so that MAX, MIN and AVG function can be performed.
Here, I need to fetch date from table-1 and from Table-2(from Timestamp, IN or Out).
Problem is : T1 has ccyymmdd format and T2 has yyyy-mm-dd format.
Can I change ccyymmdd to ccyy-mm-dd and check the greater than condition from date fetched out of Timestamp???
If yes, then please tell me how to calculate the difference between 2 timestamps and their AVG, is it same like Integers/numbers or we need to break them in parts.
My requirement is : Once the date is compared and T2's date is greater than T1's date, then I have to calculate : 1) Difference of In and Out Timestamps, 2) MAX among that Difference, 3) MiN among that Difference,
4) AvG of that Difference, 5) Total number of records processed.
At first look, this does not appear to be a "single query" requirement.
You are right, it can not be single query ,
My issue will be fixed if this requirement met. (I am sorry I am unable to put sample data as system is building from the scratch, If this reply will not clear the requirement I will put some sample data).
Actually My client want to check how much time a transaction takes, he want to check from(or after) a particular date, i.e date after the system was run last time. Now this date is coming from a table T1, Integer format yyyymmdd(I will confirm the fields if required, not available right now).
It has to be compared with date fetched from the second table(from Timestamp IN or OUT, anyone ), Main operation is going to be on 2nd table but only for the data/records which mets the condition i.e Date of Table-2 > Date of Table-1 (after last transaction).
If condition is true for 1st transaction, then I have to calculate difference of times(IN and OUT) from Timestamp, same if true for 2nd transaction, 3rd , 4th and skip 5th if condition is not true and so on......
So, basically, in a query I want to extract dates from two tables, test the condition, if TRUE, calculate the Timestamp2 - Timestamp1(or vice versa), then the AVG , MAX and MIN of this Difference.
I have written nothing new to clear the question, just few words to clear my thought. Please help me, if need more input I will provide here.
I can try few steps but when I fetch date from 1st table, it is yyyymmdd and from second table yyyy-mm-dd, how to compare them or test them, Is a cobol W-S variable mandatory?
This query will convert the ordinary date to integer(yyyymmdd) format
Code:
select int(replace(char(current date, ISO),'-',''))
from sysibm.sysdummy1;
I did this because, converting integer to date might require a few substrings, concatenation & date manipulations stuff. This was much easier with less functions usage.
You can use the query like this to compare the dates(or integers)
Code:
select t2.Timestamp_IN_col, t2.Timestamp_out_Col
from t1, t2
where int(replace(char(t2.date_col, ISO),'-','')) > t1.Date_col
Thank your consistent reply.
You solution worked like Magic!
thank you very much.
I am proud to be a part of this forum, with such an experience and technical professionals.
This is the best forum I have ever seen.
Please Always give little part/time of your life to this forum.
I am new to the Mainframe. Just because the help of this forum I am able to get a good job On IBM Mainframes, in a good company. With lot of Developement opportunities.
Thanks a lot. I owe a lot to all you guys and this forum.
,
