Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
help in designing sql

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 32
Location: India

PostPosted: Fri Apr 03, 2020 10:07 pm    Post subject: help in designing sql
Reply with quote

Need help in sql,

I have table which has 4 column

Vehicle Number - This column am using in where clause to fetch data.
Hold type - This is in select clause.
start date - define as timestamp.
end date - This is defined timestamp with null allow, I am fetching this column. if null, it should populate as '2099-12-31' in output.

here vehicle number and start date is unique key.

Requirement -
If the vehicle has 2 Hold type of the same Hold type(value), only 1 record will be returned. This will be the Hold with the latest end date, or no specified end date. If both Holds expire at the same time, the Hold applied first will be returned.

Written below sql, ignore syntax if any as I have re-written based on above example for understanding purpose. But I have executed query so logic wise it is right.

this query working based on difference of start date and end date, so working fine when two same record(hold type) and one of them having end date null.

But it gives wrong output( for same hold type) if start date for one record is 2015-01-01 and end date is 2020-05-01
second record having Start date 2017-01-01 and end date 2020-04-01

Here I am getting record having end date 2020-05-01 since difference between start date and date is more here. the expected record to be picked having end date '2020-04-01 (it has latest end date as per req).

Code:
select hold type, end date_tem

FROM
(
Select
hold type,
coalesce(DATE(End_date ),'2099-12-31') as END_DATE_temp ,
coalesce(DATE(End_date),'2099-12-31')-date(start_date) as DAYS_DIFF 
 -- (to find longest hold)

,row_number() over(PARTITION BY Table_name ORDER BY (coalesce(DATE(end_date),'2099-12-31')-date(start_date)) desc) as RNUM

from table_name
where
vehicle = 'abcd'
and (end_date >= current_timestamp or end_date is null)
)
WHERE RNUM=1;

Coded for you this time. Do it yourself in future.
Back to top
View user's profile Send private message

sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 801
Location: Maryland

PostPosted: Fri Apr 03, 2020 11:06 pm    Post subject:
Reply with quote

Nobody at this forum would read any code without code tags.

Many people also would not read any not-properly-aligned code, even with code tags.

Fix it.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Designing a panel in which input file... lokeshwar_manoharan TSO/ISPF 4 Tue Nov 18, 2008 10:15 pm
No new posts Designing ISPF panel for CICS start/r... Pedro TSO/ISPF 6 Sat Aug 16, 2008 10:15 am
No new posts Please help in designing the query Soundararajan DB2 4 Mon Nov 12, 2007 4:31 pm
This topic is locked: you cannot edit posts or make replies. ISPF-Panel designing manual required jaydeb TSO/ISPF 1 Tue Oct 30, 2007 3:22 pm
No new posts Steps for designing online screen usi... Bitneuker IMS DB/DC 19 Wed Jul 04, 2007 10:18 pm

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