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

help in designing sql


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Fri Apr 03, 2020 10:07 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Fri Apr 03, 2020 11:06 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Designing a panel in which input file... TSO/ISPF 4
No new posts Designing ISPF panel for CICS start/r... TSO/ISPF 6
No new posts Please help in designing the query DB2 4
This topic is locked: you cannot edit posts or make replies. ISPF-Panel designing manual required TSO/ISPF 1
No new posts Steps for designing online screen usi... IMS DB/DC 19
Search our Forums:

Back to Top