CODEWITHSUNDEEP
sqlsql-serversql-delete
kishore kumar
kishore kumar

Reputation: 11

SQL: delete records depends on a two variables conditions

depends on the below:

Rank  Provincecode provincekey   Name   Value  
 1         ABC        100       address  Detriot
 1         ABC        100        city     NULL
 1         ABC        100        country  US
 1         ABC        100         region   US
 5         ABC        200        address   NULL
 5         ABC        200          city    NULL
 5         ABC        200         Region   NULL
 5         ABC         200        country  NULL   
 9         XYZ         500        address  Texas
 9         XYZ         500        city      TX
 9         xyz         500         country  US
 9         XYZ         500         Region   US

The requirement is to delete the records grouped by the raked with these two conditions:

*If the second condition does not pass we try with the second-lowest rank and check the value value if it contains any not null value. and so on *

Desired results only low ranking records should be considered irrespective of the block values are null or not null.

o/p :

 1         ABC        100       address  Detriot
 1         ABC        100        city     NULL
 1         ABC        100        country  US
 1         ABC        100         region   US

Tried something like this 

DELETE CTE FROM  
      (
      SELECT RANK() OVER(Partition BY NAME ORDER BY Rank Asc, Date Desc, key Desc) AS RNK,FName,Name,Value,Code,key,Date
      FROM #TABLE1  SRC WHERE code = 'ABC' AND FName = 'CITY' AND (ISNULL(Value,'') <> '') 
           UNION 
      SELECT RANK() OVER(Partition BY FName ORDER BY Rank Asc, Date Desc, key Desc) AS RNK,FName,Name,Value,Code,key,Date
      FROM #TABLE1  SRC WHERE code = 'ABC' AND FName = 'CITY' AND (ISNULL(Value,'') = '')   
     ) CTE

     WHERE ((RNK > 1 AND FName = 'ABC' AND (ISNULL(Value,'') <> '')) OR (FName = 'CITY' AND (ISNULL(Value,'') = '')))

It is thowing an error. Can any one provide help on this.

Upvotes: 0

Views: 585

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Getting the rank seems to be the key to this puzzle.

The example below gives priority to the groups that have a country.

So if there's only 1 group, and that group has no country, then it won't be deleted.
If there are more groups then the newest group with a country will remain.

Sample data:

CREATE TABLE Table1 
(
  ProvinceCode varchar(3) not null, 
  ProvinceKey int not null, 
  Name nvarchar(30) not null, 
  [Value] nvarchar(30),
  ModifiedDate datetime not null
);

INSERT INTO Table1
(ProvinceCode, ProvinceKey, Name, [Value], ModifiedDate) VALUES 

 ('ABC', 200, 'address', NULL, '2019-12-21')
,('ABC', 200, 'city', NULL, '2019-12-21')
,('ABC', 200, 'region', NULL, '2019-12-21')
,('ABC', 200, 'country', NULL, '2019-12-21')

,('ABC', 100, 'address', 'Detriot', '2019-12-15')
,('ABC', 100, 'city', NULL, '2019-12-15')
,('ABC', 100, 'country', 'US', '2019-12-15')
,('ABC', 100, 'region', 'US', '2019-12-15')

,('XYZ', 500, 'address', 'Texas', '2019-12-09')
,('XYZ', 500, 'city', 'TX', '2019-12-09')
,('XYZ', 500, 'country', 'US', '2019-12-09')
,('XYZ', 500, 'region', 'US', '2019-12-09')

Delete:

WITH CTE_SLC1 AS
(
  SELECT *
  , CAST(MAX(CASE WHEN Name = 'country' AND [Value] IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY ProvinceCode, ProvinceKey) AS BIT) AS hasCountry 
  , MAX(ModifiedDate) OVER (PARTITION BY ProvinceCode, ProvinceKey) AS p_date
  FROM Table1
)
, CTE_SLC2 AS
(
  SELECT *
  , DENSE_RANK() OVER (ORDER BY 1-hasCountry, p_date DESC) AS p_rank
  FROM CTE_SLC1
)
DELETE
FROM CTE_SLC2
WHERE p_rank > 1;

What remains

SELECT *
FROM Table1
ORDER BY ProvinceCode, ProvinceKey, Name;
GO

ProvinceCode | ProvinceKey | Name    | Value   | ModifiedDate       
:----------- | ----------: | :------ | :------ | :------------------
ABC          |         100 | address | Detriot | 15/12/2019 00:00:00
ABC          |         100 | city    | null    | 15/12/2019 00:00:00
ABC          |         100 | country | US      | 15/12/2019 00:00:00
ABC          |         100 | region  | US      | 15/12/2019 00:00:00

db<>fiddle here

Upvotes: 0

gbeaven
gbeaven

Reputation: 1790

I think you are over-complicating your logic in your question.

If you need to delete values regardless of their null status then just use a where and exclude the null conditions:

delete from [source table]
where rank > 1

Data is either present (not null) or it isn't (null), if your criteria doesn't care, why include the logic at all?

Upvotes: 1

Related Questions