sql server - Use conditional computed column in SQL query -

i have 2 tables viz. #exported_data , user_details.

#exported_data: (user_id, user_name, status, office_id, dept_id, service_id, allocation)

user_details: (user_id, office_id, dept_id, service_id, serviceallocation)

in #exported_data table, status can inactive or active.

in user_details table, allocation can between 0 , 1.

user_details table transaction table , #exported_data table temporary table hold records need inserted transaction table based on criteria.

i have query:

insert user_details (user_id, office_id, dept_id, service_id, serviceallocation) select      user_id,      office_id,     dept_id,     service_id,     allocation #exported_data ed not exists (select office_id, dept_id, service_id user_details ud ud.user_id = ed.user_id) 

in above query, in place of allocation, have insert allocation value based on conditions. conditions are:

if status of user in #exported_data inactive make allocation 0, else

(1) allocation of user #exported_data

(2) sum(allocation) of user user_details table

(3) if allocation of (1) + sum(allocation) < 1 allocation value else skip insert

i tried using case statements getting complex form correct query. while inserting checking whether combination (user_id, office_id, dept_id, service_id) exists in transaction table or not. query should not insert duplicate row. should insert unique rows. example, user 1 can in 2 different departments or can have different service ids.

the allocation value want in query is:

allocation value #exported_data + sum(serviceallocation)

i dun have full imagination of act , thinking.but know rkh , if tried bring sum(serviceallocation) variable in order brief query.

telling if #exported_data.allocation + sum(serviceallocation) >= 1 skip insert, can put clause avoid fetching such these rows.

in idea have use either case when statement or a function. suggest code below.may useful.you can declare variable above query(not in or function).

(personally,i dun functions because makes query 2 or 3 times lower specially in big queries )

declare @sum_serviceallocation float set @sum_serviceallocation = (select sum(serviceallocation) user_details user_details.user_id = (select top 1 user_id exported_data)) insert user_details (user_id, office_id, dept_id, service_id, serviceallocation) select      user_id,      office_id,     dept_id,     service_id,     (case status when 'inactive' 0 else (ed.allocation + @sum_serviceallocation)end) 'allocation' #exported_data ed not exists (select office_id, dept_id, service_id user_details ud ud.user_id = ed.user_id) , (ed.allocation + @sum_serviceallocation)<1 

maybe isn't close goal it's suggestion