CODEWITHSUNDEEP
sqlsql-servert-sql
xris23
xris23

Reputation: 353

Update detail dates based on datediff from other table

I have a question, which I do not really know how to solve.

I have two tables.

  1. Header: Header_ID, start dates and duration in months
  2. Detail: Det_ID, Header_ID, Months (start date until start date + duration).

Sample data:

Headertable:

Header_ID Start_date Duration
1 01-01-21 4
2 15-10-21 3
3 18-10-21 2

Detail table:

Det_ID Header_ID Months
1 1 01-01-21
2 1 01-02-21
3 1 01-03-21
4 1 01-04-21
5 2 15-10-21
6 2 15-11-21
7 2 15-12-21
8 3 18-10-21
9 3 18-11-21

Now I am looking for a SQL script to update the detail based on a change in start date of the header table.

For example if in the header table, Header_ID number 3 start_date is modified from 18-10-2021 to 20-12-2021, detail should be updated to:

Det_ID Header_ID Months
1 1 01-01-21
2 1 01-02-21
3 1 01-03-21
4 1 01-04-21
5 2 15-10-21
6 2 15-11-21
7 2 15-12-21
8 3 20-12-21
9 3 20-01-22

How can I update this via a script in SQL Server?

I cannot just delete the Header_ID in the detail table and recalculate and reinsert it based on the header table, because there are much more details in the detail table that will be lost.

Perhaps via a CTE?

Something like:

  1. Select minimum of Months for every header ID in detail table to compare detail with header
  2. Calculate date difference between Header table and minimum of months of detail table.
  3. Dateadd in detail table for every ID based on the datediff in 2.

Hopefully somebody could help me in the right direction. Thanks!

Upvotes: 2

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

If only the start date is changing, then presumably the duration is not (for duration changes, you should ask a new question; that is much more challenging).

Basically, you just want to update the values in order. Let me assume that you know that id 3 is changing:

update d
    set month = dateadd(month, d.seqnum - 1, h.start_date)
    from (select d.*,
                 row_number() over (partition by header_id order by  months) as seqnum
          from details d
         ) d join
         header h
         on d.header_id = h.header_id
    where h.header_id = 3;

Upvotes: 1

Related Questions