Update a column in SQL based on field frequency

[sabrinekalbousi]

Hello SQL query  team ,

I need to create a dimension in my database ( to create later a report in Power BI) and I am struggling with the stored procedure. In fact, I need to have the following table dim_table as a result.

passport number	passport_expiry_date	is_valid_expiry_date	consolidated_expiry_date
ALJDLJZ	7/10/2020	1	7/10/2020
ALJDLJZ	7/10/2020	1	7/10/2020
ALJDLJZ	NULL	1	7/10/2020
ALJDLJZ	8/10/2021	1	7/10/2020

DONE: I have already the passport numbers and passport expiry dates in the table and and I have updated the columns 'is_valid_expiry_date' to consider NULL and dates in the future as valid..

TO BE DONE: I need to update the consolidated column based on these rules:

- we consider just records with is_valid_expiry_date=1

-if count( distinct(passport_expiry_date))=1 (just one valid date)then, this latter should be in each line in the consolitaed column.

- if we have different dates ( as in our example), we need to take the most frequent one (7/10/2020 (repeated twice)) and put it in each line as a consolidated value.

--------------------------

update t 

[consolidated_passport_expiry_date]=

CASE WHEN count(distinct [passport_exipiry_date])=1 OR count(distinct [passport_exipiry_date])=0 THEN t.passport_exipiry_date

          WHEN.........................

           WHEN..................

ELSE

END

FROM dim_table t
where t.passport_expiry_dateis_valid_expiry_date=1 
GROUP BY t.[passport_number], [passport_exipiry_date]

------------------------

Thanks in advance for any help or tips to complete this update section.

Sabrina

[v-xuding-msft]

Hi @sabrinekalbousi ,

If you think lbendlin's answer is helpful, please accept it as a solution. And if you still need help, it is better to get dedicated support from the database forum.

[lbendlin · Accepted solution]

That's not a valid SQL syntax

UPDATE table

SET field= CASE WHEN THEN ELSE END

WHERE <filter condition>

The FROM statement is only needed when you update one table's columns from another related table's content.