Styjun

This is the table I have

| prdntVrsnNum | matlNum | wrkCtrCd | ritmValFromDt | versnValFromDt | parmVldFromDt | rtgTypeCd | rtgGrpCd | rtgNodeNum | charVal |
| ------------ | ------- | -------- | ------------- | -------------- | ------------- | --------- | -------- | ---------- | ------- |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20170407 | N | 50020937 | 1 | 7 |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20170407 | N | 50020937 | 1 | 7 |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20180101 | N | 50020937 | 1 | 7 |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20180101 | N | 50020937 | 1 | 7 |
| V003 | 197774 | TOU-A16 | 20181210 | 20181207 | 20190107 | N | 50018492 | 6 | 1 |
| V003 | 197774 | TOU-A16 | 20181210 | 20181207 | 20190107 | N | 50018492 | 6 | 1 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20141211 | N | 50018966 | 1 | 5 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20151227 | N | 50018966 | 1 | 4.5 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20141211 | N | 50018966 | 1 | 5 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20151227 | N | 50018966 | 1 | 4.5 |

Desired Result:

TOU-A16/V003/197774/20181210
TO-HCH2/V001/96228/20180101
LI-LIN2/V001/66850/20180101

Requirements/Conditions:

Having prdntVrsnNum, matlNum, wrkCtrCd as key columns,

1. Delete duplicates according to rtgTypeCd, rtgGrpCd, rtgNodeNum, parmVldFromDt, charVal




2. If no records found, do nothing.




3. If one record found, take ritmValFromDt for concatenation



4. 
   

   If multiple records found, see how many records have parmVldFromDt >= ritmValFromDt

   
   
   

   4.1. If one record found, then use ritmValFromDt for concatenation.

   
   
   

   4.2. If multiple records found, then use parmVldFromDt for concatenation.

   
   

Intermediate result which I am using for explanation:

SELECT distinct * from mytable;

| prdntVrsnNum | matlNum | wrkCtrCd | ritmValFromDt | versnValFromDt | parmVldFromDt | rtgTypeCd | rtgGrpCd | rtgNodeNum | charVal |
| ------------ | ------- | -------- | ------------- | -------------- | ------------- | --------- | -------- | ---------- | ------- |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20170407 | N | 50020937 | 1 | 7 |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20180101 | N | 50020937 | 1 | 7 |
| V003 | 197774 | TOU-A16 | 20181210 | 20181207 | 20190107 | N | 50018492 | 6 | 1 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20141211 | N | 50018966 | 1 | 5 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20151227 | N | 50018966 | 1 | 4.5 |

From the above table, now I should reduce it to the following final table, which I will use for concatenation.

| prdntVrsnNum | matlNum | wrkCtrCd | ritmValFromDt | versnValFromDt | parmVldFromDt | rtgTypeCd | rtgGrpCd | rtgNodeNum | charVal |
| ------------ | ------- | -------- | ------------- | -------------- | ------------- | --------- | -------- | ---------- | ------- |
| V001 | 96228 | TO-HCH2 | 20170407 | 20170407 | 20180101 | N | 50020937 | 1 | 7 |
| V003 | 197774 | TOU-A16 | 20181210 | 20181207 | 20190107 | N | 50018492 | 6 | 1 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20141211 | N | 50018966 | 1 | 5 |
| V001 | 66850 | LI-LIN2 | 20180101 | 20141211 | 20151227 | N | 50018966 | 1 | 4.5 |

EDIT: Further explanation:

• 
  V001|96228|TO-HCH2 comes under condition 4.2.


• 
  V003|197774|TOU-A16 comes under condition 4.1.


• 
  V001|66850|LI-LIN2 I think this condition comes by choosing ritmValFromDt since that's the greater one and we don't have any record for this combination having parmVldFromDt >= ritmValFromDt But I'm not certain about this though.. :(

DB-Fiddle: https://www.db-fiddle.com/f/qZLyGdyv2spYe3ZokZhYAP/1

What I have tried so far:

SELECT
DISTINCT
CONCAT(wrkCtrCd, '/', prdntVrsnNum , '/', matlNum , '/', ritmValFromDt)
AS outputCol
FROM mytable;

| outputCol |
| ---------------------------- |
| TO-HCH2/V001/96228/20170407 |
| TOU-A16/V003/197774/20181210 |


SELECT
DISTINCT
CONCAT(wrkCtrCd, '/', prdntVrsnNum , '/', matlNum , '/',
 CASE
 WHEN parmVldFromDt >= ritmValFromDt THEN parmVldFromDt
 ELSE ritmValFromDt
 END)
AS outputCol
FROM mytable;

| outputCol |
| ---------------------------- |
| TO-HCH2/V001/96228/20170407 |
| TO-HCH2/V001/96228/20180101 |
| TOU-A16/V003/197774/20190107 |

NOTE

1. Please be as vendor neutral as possible when using SQL. This is not in my hands. I am not querying from source, so my hands are tied. Some vendor specific functions may be available, but this is not a guarantee.




2. In the db-fiddle, MySQL (and the version used) is for illustration purpose only. As stated previously, I am not querying from an RDBMS, I am querying from a grid, so I have to adjust with what is available and how it is available in the grid.

I don't think you have defined your conditions correctly. In particular, you for the last condition, you seem to want the latest date.

I would go for window functions for this. I think the logic is:

SELECT CONCAT(wrkCtrCd, '/', prdntVrsnNum , '/', matlNum , '/',
 (CASE WHEN cnt = 1 OR cnt_gt = 1
 THEN ritmValFromDt
 ELSE parmVldFromDt
 END)
 ) AS outputCol,
 ritmValFromDt,parmVldFromDt
FROM (SELECT t.*,
 COUNT(*) OVER (PARTITION BY prdntVrsnNum, matlNum, wrkCtrCd) as cnt,
 SUM(gt_flag) OVER (PARTITION BY prdntVrsnNum, matlNum, wrkCtrCd) as cnt_gt,
 ROW_NUMBER() OVER (PARTITION BY prdntVrsnNum, matlNum, wrkCtrCd, gt_flag ORDER BY parmVldFromDt DESC) as seqnum
 FROM (SELECT DISTINCT prdntVrsnNum, matlNum, wrkCtrCd, ritmValFromDt, versnValFromDt, parmVldFromDt,
 (CASE WHEN parmVldFromDt >= ritmValFromDt THEN 1 ELSE 0 END) as gt_flag
 FROM mytable
 ) t
 ) t
WHERE (cnt_gt > 0 AND parmVldFromDt >= ritmValFromDt AND seqnum = 1) OR
 (cnt_gt = 0);

Here is a db<>fiddle.

I came up with an answer I tried on the fiddle. Not sure if it will work in my actual system (have to test it out now). I am not sure if this is the best/only way to achieve it either.

select
distinct
concat(distinctTable.wrkCtrCd, '/', 
 distinctTable.prdntVrsnNum, '/', 
 distinctTable.matlNum, '/',
 case when countTable.cnt = 1 then min(distinctTable.ritmValFromDt)
 else max(distinctTable.parmVldFromDt)
 end
 ) as outputCol
from
(
 select
 tbl.prdntVrsnNum,
 tbl.matlNum,
 tbl.wrkCtrCd,
 count(*) as cnt
 from
 (select distinct * from mytable) as tbl
 group by
 tbl.prdntVrsnNum,
 tbl.matlNum,
 tbl.wrkCtrCd
) as countTable
inner join (select distinct * from mytable) as distinctTable
on countTable.prdntVrsnNum = distinctTable.prdntVrsnNum
and countTable.matlNum = distinctTable.matlNum
and countTable.wrkCtrCd = distinctTable.wrkCtrCd
group by
 distinctTable.prdntVrsnNum,
 distinctTable.matlNum,
 distinctTable.wrkCtrCd;

| outputCol |
| ---------------------------- |
| TO-HCH2/V001/96228/20180101 |
| TOU-A16/V003/197774/20181210 |