0

I have a table with the columns Lkup_Value and Report_Date. Based on the below table I need to write the SQL Script like, if for the same Report_Date there are two Lkup_values values, then need to take only one Lkup_Value.

    Lkup_Value     |      Report_Date 
-------------------+------------------------------
 MMM               |2022-07-11 
 MMM-IR            |2022-07-11
 MMM-IR            |2022-07-04
 MMM               |2022-07-04
 CCC               |2022-01-26
 CCC               |2022-01-03


OutPut:-
    Lkup_Value     |      Report_Date 
-------------------+------------------------------
 MMM               |2022-07-11 
 MMM               |2022-07-11
 MMM               |2022-07-04
 MMM               |2022-07-04
 CCC               |2022-01-26
 CCC               |2022-01-03

For example, for the Report_Date “2022-07-11” there are two Lkup_Values i.e. “MMM” and “MMM-IR” then, in this scenario I need to take only "MMM". Overall If a day (Report_Date) contains the Lkup_Values “MMM” then irrespective of another values I need to take only "MMM".

Learner
  • 25
  • 5
  • `SELECT * FROM yourtable WHERE Lkup_Value = 'MMM'`? – ADyson Jul 22 '22 at 10:12
  • Need SQL Script like for Report_Date '2022-07-11' MMM and In the place of MMM-IR also I need to Update as "MMM" – Learner Jul 22 '22 at 10:18
  • So now you want to update all values for 2022-07-11 and change them to MMM? That's not what you asked originally. Make up your mind what you actually want. – ADyson Jul 22 '22 at 10:24
  • 1
    This doesn't look like you want "1 value" but want to "strip" the text after and incl;uding the hyphen (`-`). – Thom A Jul 22 '22 at 10:40
  • 1
    Your question is really unclear. Maybe `UPDATE yourtable SET Lkup_Value = LEFT(Lkup_Value, NULLIF(CHARINDEX('-', Lkup_Value, 0) - 1) WHERE CHARINDEX('-', Lkup_Value) <> 0` ? – Charlieface Jul 22 '22 at 11:15
  • Should this be doing an update to the original data in the table, or just selecting data to display on the screen? It's not clear. – ADyson Jul 22 '22 at 11:20

1 Answers1

0
WITH CTE(Lkup_Value, Report_Date )
AS
(
  SELECT 'MMM'    ,            '2022-07-11' UNION ALL
  SELECT'MMM-IR'  ,           '2022-07-11' UNION ALL
  SELECT'MMM-IR'  ,          '2022-07-04' UNION ALL
  SELECT'MMM'     ,           '2022-07-04' UNION ALL
  SELECT 'CCC'     ,           '2022-01-26' UNION ALL
  SELECT'CCC'     ,           '2022-01-03' UNION ALL
  SELECT 'MMM','2022-01-03'
)
SELECT TOP 1 WITH TIES X.Lkup_Value,X.Report_Date 
    FROM CTE AS X
ORDER BY ROW_NUMBER()OVER(PARTITION BY X.Report_Date ORDER BY CASE WHEN X.Lkup_Value='MMM'THEN 1 ELSE 2 END ASC)
Sergey
  • 4,719
  • 1
  • 6
  • 11