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.
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.
CASE WHEN count(distinct [passport_exipiry_date])=1 OR count(distinct [passport_exipiry_date])=0 THEN t.passport_exipiry_date
FROM dim_table twhere 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.
Go to Solution.
That's not a valid SQL syntax
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.
View solution in original post
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.