Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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.
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.
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.