Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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.