I have two tables viz. #Exported_Data and User_Details.
(User_Id, User_Name, Status, Office_Id, Dept_Id, Service_Id, Allocation)
(User_Id, Office_ID, Dept_Id, Service_Id, ServiceAllocation)
In #Exported_Data table, Status can be Inactive or Active.
In User_Details table, Allocation can be between 0 and 1.
User_Details table is a transaction table and #Exported_Data table is a temporary table which hold all records which need to be inserted into the Transaction table based on certain criteria.
I have a query:
insert into User_Details (User_Id, Office_ID, Dept_Id, Service_Id, ServiceAllocation)
from #Exported_Data ED
where not exists (select Office_ID, Dept_ID, Service_ID from User_Details UD where UD.User_Id = ED.User_Id)
In the above query, in place of Allocation in select query, I have to insert an Allocation value based on certain conditions. The conditions are:
If Status of the user in #Exported_Data is Inactive then make Allocation 0, else
(1) Get Allocation of user from #Exported_Data
(2) Get sum(Allocation) of user from User_Details table
(3) If Allocation of (1) + sum(Allocation) < 1 then this Allocation value else skip insert
I tried using CASE statements but it is getting too complex to form the correct query.
Please help me get this conditional Allocation value to be used in place of Allocation in select query.