Consider table Tax_Details with below details. In this table, each and every Tax_code should have unique Frequency
[Tax_Code] [Frequency] A-001 A-002 A-003 B-001 C-001
Consider another table Freq-Details with Below details. In this table, a Tax_code may have multiple frequency
[Tax_Code] [Frequency] A-001 Weekly A-001 Bi-Weekly A-001 Daily A-002 Daily A-002 Monthly A-003 Bi-Weekly A-003 Monthly B-001 Daily B-001 Monthly C-001 Monthly
Consider 3rd table Frequency_Sequence with details. In this table, each and every frequency have some sequence.
[Frequency] [Sequence] Daily 1 Weekly 2 Bi-Weekly 3 Monthly 4
I want to update [Frequency] column in Tax_Details table with Frequency from Freq-Details table and the Sequence of frequency should be minimum. My output should be like this
[Tax_Code] [Frequency] A-001 Daily A-002 Daily A-003 Bi-Weekly B-001 Daily C-001 Monthly
Please help to get this. Thanks in advance.
Advertisement
Answer
You can return the results you want using conditional aggregation:
select fd.tax_code,
max(case when seqnum = 1 then fd.frequency end) as frequency
from (select fd.tax_code, fd.frequency,
row_number() over (partition by fd.tax_code order by fs.sequence) as seqnum
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
) fd
group by fd.tax_code;
You can express this in an update as:
update tax_details
set frequency = (select fd.frequency
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
where fd.tax_code = tax_details.tax_code
order by fs.sequence
fetch first 1 row only
);
Both of these are standard SQL and will work in most databases. However, the syntax might differ slightly in any given database.