Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Dates compare on specific dates using SORT/JOIN/JCL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 134

PostPosted: Thu Jan 24, 2019 12:03 am    Post subject: Dates compare on specific dates using SORT/JOIN/JCL
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    Post subject: Reply to: Dates compare on specific dates using SORT/JOIN/JCL
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: 134

PostPosted: Thu Jan 24, 2019 9:53 pm    Post subject: Re: Reply to: Dates compare on specific dates using SORT/JOIN/JCL
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Compare fields with decimal chandan.inst SYNCSORT 6 Thu Apr 04, 2019 5:29 pm
No new posts Sort skiprec imagines DFSORT/ICETOOL 7 Thu Feb 28, 2019 1:30 am
No new posts How to convert hex to Displayable for... Log Thangavel DFSORT/ICETOOL 11 Fri Feb 22, 2019 9:33 am
No new posts Build SQL query using SORT balaji81_k DFSORT/ICETOOL 15 Mon Feb 18, 2019 9:56 am
No new posts Relational operator compare date rang... maxsubrat CLIST & REXX 14 Wed Feb 06, 2019 6:19 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us