IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Dates compare on specific dates using SORT/JOIN/JCL


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Jan 24, 2019 12:03 am
Reply with quote

Hi Team,
I need a advise / best optimized solution to pick up and map the date as expected below .

File- F1: - Data pulled from our source table and way it has below date mapping on sales info.

Code:


---------------------------------------------------------------------------
DEPT-NBR   THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES 
---------------------------------------------------------------------------
1000       12/23/2019         12/24/2018         100           200
1000       12/24/2019         12/25/2018         200           150
1000       12/25/2019         12/26/2018         800          1000
1000       12/26/2019         12/27/2018         600           500



F2: Seperate input file on date to be compared on special dates with respect to the sales-amount calculation.

Code:


--------------------------------------------------
12/25/2019      12/25/2018
--------------------------------------------------





Output as expected :-


Code:

Output  as expected  :-

---------------------------------------------------------------------------
DEPT-NBR   THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES 
---------------------------------------------------------------------------
1000       12/23/2019         12/24/2018         100           200
1000       12/24/2019         12/25/2018         200           150
1000       12/25/2019         12/25/2018         800           150  <---------
1000       12/26/2019         12/27/2018         600           500



As per my knowledge , below is my thought to acheive this .

Join F1 & F2 on This-Year-date and populate date to be compared on file F1 as like below to form file - f3 on last column..

Code:


-------------------------------------------------------------------------------------
DEPT-NBR   THIS-YR-SALE-DATE LAST-YR SALE-DATE THIS-YR-SALES LAST-YR-SALES  SPL-LY-DATE
-------------------------------------------------------------------------------------
1000       12/23/2019         12/24/2018         100           200          12/24/2018
1000       12/24/2019         12/25/2018         200           150          12/25/2018   
1000       12/25/2019         12/26/2018         800          1000          12/25/2018
1000       12/26/2019         12/27/2018         600           500          12/27/2018



FROM F3 form/build the file F4 to pull the columns as like below

Code:

F4:-
-------------------------------------------------------------
DEPT-NBR   LAST-YR-DATE    LAST-YR-SALES
-------------------------------------------------------------
1000       12/24/2018       200
1000       12/25/2018       150
1000       12/26/2018      1000
1000       12/27/2018       500



Join F3 & F4 on Last-year-Date and populate the last year sales on F3 for matching and for non-match retain the as it is.

Please advise is ther any optimized way that we achieve it quite easily.

Thanks
Balaji K
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Jan 24, 2019 10:41 am
Reply with quote

What exactly are you trying to achieve with the date file?

You could JOIN both the inputs on LAST YR and write matched records to create something like this:

Code:
THIS YR         LAST YR       DEPT     LAST-YR-SALES
--------------------------------------------------
12/25/2019      12/25/2018    1000     150
--------------------------------------------------

Then match this with the original input on DEPT, THIS YR (a JOIN UNPAIRED,F1
- F1 being your original input) to get the final result.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Thu Jan 24, 2019 9:53 pm
Reply with quote

Arun Raj wrote:
What exactly are you trying to achieve with the date file?

You could JOIN both the inputs on LAST YR and write matched records to create something like this:

Code:
THIS YR         LAST YR       DEPT     LAST-YR-SALES
--------------------------------------------------
12/25/2019      12/25/2018    1000     150
--------------------------------------------------

Then match this with the original input on DEPT, THIS YR (a JOIN UNPAIRED,F1
- F1 being your original input) to get the final result.


Thanks Arun - I will try this , yes this is what i need. Thanks for reducing the swap process in this .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top