NLimbu NLimbu - 2 months ago 7
SQL Question

Create a table view depending on values in second table by adding the dates of that table and subtracting the result on first table in SQL

Open to either Create View or Update Queries.

I have two tables as below. One showing detention and the other shows a starting detention limit for a student. Both table contains row ID as Primary Key. ID below is not a PK.

Table1: Detention | Table2: TotalDetention
--------------------------------------------------------------------
PK ID startDate endDate status | PK ID totalDetention
1 1 2016-09-23 2016-09-29 Pending | 1 1 28
2 2 2016-09-23 2016-09-29 Pending | 2 2 28
3 3 2016-09-23 2016-09-29 Declined | 3 3 28
4 1 2016-10-01 2016-10-05 Declined | 4 10 28
5 1 2016-10-05 2016-10-10 Pending |


When the status of Detention is set to 'Approved', I want to get the total days from startDate and endDate then subtract that number from TotalDetention, in a view if possible.

Result would look like this.

Table1: Detention | Table2: TotalDetention
--------------------------------------------------------------------
PK ID startDate endDate status | PK ID totalDetention
1 1 2016-09-23 2016-09-29 Approved | 1 1 17
2 2 2016-09-23 2016-09-29 Pending | 2 2 28
3 3 2016-09-23 2016-09-29 Declined | 3 3 28
4 1 2016-10-01 2016-10-05 Declined | 4 10 28
5 1 2016-10-05 2016-10-10 Approved |

Answer

based on your title(update a table)..you can do this using update trigger on table1..

create trigger updatedata
    on dbo.table1
    after update as
    begin
  if (update(status)) and exists(select * from inserted where status='approved')
    Begin
    update t2
    set t2.totaldetention=t2.totaldetention-datediff(day,i.startddate,i.enddate)
    from table2 t2
    join
    inserted i
    on i.id=t2.id and i.status='approved'

    end
    end
Comments