Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.
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.
I'm guessing you added "_Perfect" to indicate you thought the past perfect should be used.
The query first calculates the category frequencies and stores them in a temporary table. Then, it uses this temporary table to update the products table. Finally, to ensure a clean environment, the temporary table is dropped. This temporary table approach can sometimes be faster than the subquery method, particularly for very large tables.
I'm guessing you added "_Perfect" to indicate you thought the past perfect should be used.
The query first calculates the category frequencies and stores them in a temporary table. Then, it uses this temporary table to update the products table. Finally, to ensure a clean environment, the temporary table is dropped. This temporary table approach can sometimes be faster than the subquery method, particularly for very large tables.
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.