asked on 9/20/2016

PERFORMANCE OF SQL QUERY

Hi Experts,

I have some performance issue with my T-SQL Query attached , here are the details below.

1. I am processing 1000 records in a batch first by insert in to temporary table and then eventually to the fact table.
2. Problem is with the query of the temporary table.
3. When I check the execution plan , sort is taking longer time , is it because of the use of Group By and Order By statement in the select ? How to Optimize this ?
4. Any missing Indexes . Please advice ?

Please help me to optimize the performance of this query and also advice me on missing indexes.

Here are the indexes on the various tables used in the query (excluding the dimension tables) as the dimension tables are not large size, hence I have not listed the indexes of the dimension tables.

1.#FACT_Labor_Demand_Ideal

create clustered index idx1 on #FACT_Labor_Demand_Ideal ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,comparable_flag_id,day_comparable_flag_id,business_unit_status_id,[datasource_id],[labor_schedule_version_id] )

2.#FACT_Labor_Schedule_Version

create clustered index idx1 on #FACT_Labor_Schedule_Version ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,labor_schedule_version_id,comparable_flag_id,business_unit_status_id,[datasource_id] )

Will appreciate your quick reply and your help and support in this regard.

Thanks,
SRK.

Microsoft SQL Server 2005 Microsoft SQL Server 2008 SSIS Microsoft SQL Server

Last Comment

n_srikanth4

10/7/2016

Vitor Montalvão

9/20/2016

I have some performance issue with my T-SQL Query attached

No attachments found.

Please help me to optimize the performance of this query and also advice me on missing indexes.

We'll need the Query Execution plan of the query so we can analyze it and give you the best advice possible.

n_srikanth4

9/20/2016

ASKER

Hi Vitor,

Please find the attached SQL Query and will provide the execution plan in a short while .

Thanks,
SRK.

n_srikanth4

9/20/2016

ASKER

Sorry , missed as the format .sql is not accepted. Please find sql.txt file for the attachment.

Vitor Montalvão

9/20/2016

Still no attachments. Also, forget the SQL query. If you can please post the Query Execution Plan instead.

n_srikanth4

9/20/2016

ASKER

Sorry , I am unsure if you are allowed to open the attachment . here is the query below :

insert into #FACT_Labor_Schedule_Version_Accuracy (datasource_id


,business_unit_id


,calendar_day


,time_quarter_hour_id


,labor_workgroup_id


,labor_role_id


,daypart_id


,labor_schedule_version_id


,labor_demand_acc_hours_qty


,labor_demand_acc_adj_hours_qty


,labor_sched_acc_hours_qty


,ver_covered_time


,ver_covered_time_adj


,ver_calc_sched_accuracy_hours_qty


,ver_adj_sched_accuracy_hours_qty


,ver_calc_sched_accuracy_id


,ver_adj_sched_accuracy_id


,business_unit_status_id


,comparable_flag_id


,day_comparable_flag_id)
                                                            select

datasource_id,

business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypar

t_id,labor_schedule_version_id


, max(labor_demand_acc_hours_qty) as labor_demand_acc_hours_qty


, max(labor_demand_acc_adj_hours_qty) as labor_demand_acc_adj_hours_qty


, max(labor_sched_hours_qty) as labor_sched_acc_hours_qty


, max(ver_covered_time) as ver_covered_time


, max(ver_covered_time_adj) as ver_covered_time_adj


, max(ver_calc_sched_accuracy_hours_qty) as ver_calc_sched_accuracy_hours_qty


, max(ver_adj_sched_accuracy_hours_qty) as ver_adj_sched_accuracy_hours_qty


,coalesce(t.sched_accuracy_id,0) as ver_calc_sched_accuracy_id


,coalesce(ta.sched_accuracy_id,0) as ver_adj_sched_accuracy_id


,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
                                                            from
                                                            (


select


demand.datasource_id,demand.business_unit_id,demand.calendar_day,demand.time_quarter_hour_

id,demand.labor_workgroup_id,demand.labor_role_id,demand.daypart_id,demand.labor_schedule_

version_id


,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty


,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty


,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty


,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as

ver_covered_time


,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as

ver_covered_time_adj


,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as

[ver_calc_sched_accuracy_hours_qty]


,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as

[ver_adj_sched_accuracy_hours_qty]


,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id


from #FACT_Labor_Demand_Ideal demand


left outer join #FACT_Labor_Schedule_Version sched
                                                                  on

demand.business_unit_id = sched.business_unit_id


and demand.calendar_day = sched.calendar_day


and demand.time_quarter_hour_id = sched.time_quarter_hour_id


and demand.labor_workgroup_id = sched.labor_workgroup_id


and demand.labor_role_id = sched.labor_role_id


and demand.datasource_id = sched.datasource_id


and demand.labor_schedule_version_id = sched.labor_schedule_version_id




UNION ALL



select


sched.datasource_id,sched.business_unit_id,sched.calendar_day,sched.time_quarter_hour_id,s

ched.labor_workgroup_id,sched.labor_role_id,sched.daypart_id,sched.labor_schedule_version_

id


,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty


,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty


,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty


,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as

ver_covered_time


,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as

ver_covered_time_adj


,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as

[ver_calc_sched_accuracy_hours_qty]


,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as

[ver_adj_sched_accuracy_hours_qty]


,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id


from #FACT_Labor_Schedule_Version sched


left outer join #FACT_Labor_Demand_Ideal demand
                                                                  on

demand.business_unit_id = sched.business_unit_id


and demand.calendar_day = sched.calendar_day


and demand.time_quarter_hour_id = sched.time_quarter_hour_id


and demand.labor_workgroup_id = sched.labor_workgroup_id


and demand.labor_role_id = sched.labor_role_id


and demand.datasource_id = sched.datasource_id
                                                            ) u
                                                            left outer

join dim_labor_over_under t
                                                            on

[ver_calc_sched_accuracy_hours_qty] = t.value
                                                            left outer

join dim_labor_over_under ta
                                                            on

[ver_adj_sched_accuracy_hours_qty] = ta.value
                                                            group by

datasource_id,

business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypar

t_id,labor_schedule_version_id
                                                            ,coalesce

(t.sched_accuracy_id,0),coalesce(ta.sched_accuracy_id,0)


,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
                                                            order by

calendar_day,business_unit_id,labor_workgroup_id,labor_role_id,labor_schedule_version_id,t

ime_quarter_hour_id, datasource_id

n_srikanth4

9/20/2016

ASKER

Here is the attached SQL query execution plan.ds_load_Fact_Labor_Schedule_Accu.sqlplan ds_load_Fact_Labor_Schedule_Accu.sqlplan[eds_load_Fact_Labor_Schedule_Accu.sqlplan

n_srikanth4

9/20/2016

ASKER

Also is the attached stored procedure (complete code) for which the execution plan is captured.
Version-Accuracy.txt

Vitor Montalvão

9/20/2016

I don't even know where to begin.

First of all you have hundreds of query executions and some of them dealing with billions of records. It's an huge amount of records to be handled that many times so I wouldn't expect a miracle here. How long is all this process taking to run successfully?

Now, trying to give some tips following the code.

Do not create the indexes in an empty table before a massive insert. Let the insert run and then create the index. It will save the insert time.
First insert into #bulist doesn't need to check for the records existence since the table is empty at this stage (where not exists (select 1 from #bulist bl...).
Now you can run "create index idx1 on #bulist (bu_id,business_date,datasource_id)"
In inserts from a select you don't need the rows to be ordered especially if the target table has a clustered PK. Sort operations are really heavy so get rid of them.
Do not use DISTINCT. Instead use GROUP BY. It will give the same result but with better performance.

Hope this helps to boost a little bit the performance.

Scott Pletcher

9/20/2016

Here's my tips for the code:

A) definitely create the clustered index on the temp tables prior to loading, as you've shown, but include only the columns used to JOIN the table in the clus index. [Don't create any nonclus index(es) on the temp tables.] The idea is to, hopefully, get a MERGE join of the two tables rather than a HASH join or, ouch!, a LOOP join, and to avoid later sort(s). Also, explicitly specify a fillfactor of 100 to reduce the number of pages in the temp table as much as possible. Finally, you'll want to put the clus keys in the same order as the GROUP BY in the insert to #FACT_Labor_Schedule_Version_Accuracy.

Therefore, the temp table index definitions should be:

create clustered index idx1 on #FACT_Labor_Demand_Ideal ( datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id ) with ( fillfactor = 100 );

create clustered index idx1 on #FACT_Labor_Schedule_Version ( datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id ) with ( fillfactor = 100 );

B) The ORDER BY in the INSERT to #FACT_Labor_Schedule_Version_Accuracy should be removed. But do define prior to loading the table a clustered index on the #FACT_Labor_Schedule_Version_Accuracy table on the same columns: datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id. Since the data to be INSERTed is already GROUPed BY those columns, the sort is already done or very close to it.

n_srikanth4

9/21/2016

ASKER

Thanks Vitor for the immediate reply , Could you please send me the Code snippets you have recommended above , so that we are on the same page and I can implement them . Will appreciate your help in this regard.

Vitor Montalvão

9/21/2016

Not really my style to do all the coding for you but here you go:

/****** Object:  StoredProcedure [dbo].[ds_load_FACT_Labor_Schedule_Version_Accuracy]    Script Date: 9/12/2016 11:19:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[ds_load_FACT_Labor_Schedule_Version_Accuracy] @datasource_id int = -99,@Process_Start_Log_ID int = 0 as

set nocount on
-- declare @datasource_id int,@Process_Start_Log_ID int select @datasource_id = -99,@Process_Start_Log_ID = 0
declare	@Process_Log_ID	int
	,@sql nvarchar(3999) 
	,@calendar_day int
	,@iteration_count int
	

insert WH_Process_Log( process_start_log_id, event_description, start_datetime)
select @Process_Start_Log_ID, OBJECT_NAME(@@PROCID)+' @datasource_id = '+cast(@datasource_id as nvarchar(30)), getdate() 

set @Process_Log_ID = @@IDENTITY

declare	@InsertCount	int,
		@UpdateCount	int

set @InsertCount = 0
set @UpdateCount = 0

if object_id('tempdb..#bulist') is not null
	drop table #bulist
if object_id('tempdb..#bulist_processing') is not null
	drop table #bulist_processing
if object_id('tempdb..#FACT_Labor_Schedule_Version_Accuracy') is not null
	drop table #FACT_Labor_Schedule_Version_Accuracy
if object_id('tempdb..#FACT_Labor_Schedule_Version') is not null
	drop table #FACT_Labor_Schedule_Version
if object_id('tempdb..#FACT_Labor_Demand_Ideal') is not null
	drop table #FACT_Labor_Demand_Ideal
	

create table	#bulist (
	datasource_id int not null,
	bu_id			int,
	business_date   smalldatetime,
	processed_flag	nchar( 1)	not null	default 'N',
	business_unit_id int null,
	primary key clustered (business_date,bu_id,datasource_id))
	
--create index idx1 on #bulist (bu_id,business_date,datasource_id) #Remove from here
	
create table	#bulist_processing (
	datasource_id int not null,
	bu_id			int,
	business_date   smalldatetime,
	business_unit_id int null,
	primary key clustered (business_date,bu_id,datasource_id))

CREATE TABLE #FACT_Labor_Schedule_Version_Accuracy (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[daypart_id] [int] NULL,
	[labor_schedule_version_id] [int] NOT NULL,
	[labor_demand_acc_hours_qty] [numeric](38, 6) NULL,
	[labor_demand_acc_adj_hours_qty] [numeric](38, 6) NULL,
	[labor_sched_acc_hours_qty] [numeric](38, 6) NULL,
	[ver_covered_time] [numeric](38, 6) NULL,
	[ver_covered_time_adj] [numeric](38, 6) NULL,
	[ver_calc_sched_accuracy_hours_qty] [numeric](38, 6) NULL,
	[ver_adj_sched_accuracy_hours_qty] [numeric](38, 6) NULL,
	[ver_calc_sched_accuracy_id] [int] NULL,
	[ver_adj_sched_accuracy_id] [int] NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	primary key clustered ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[labor_schedule_version_id],[time_quarter_hour_id],[datasource_id]) )

CREATE TABLE #FACT_Labor_Demand_Ideal (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	[daypart_id] [int] NOT NULL,
	[demand_calc_fte_qty] [numeric](38, 6) NULL,
	[demand_adj_fte_qty] [numeric](38, 6) NULL,
	[labor_schedule_version_id] [int] NOT NULL
)
create clustered index idx1 on #FACT_Labor_Demand_Ideal ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,comparable_flag_id,day_comparable_flag_id,business_unit_status_id,[datasource_id],[labor_schedule_version_id] )


CREATE TABLE #FACT_Labor_Schedule_Version (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[daypart_id] [int] NULL,
	[labor_schedule_version_id] [int] NOT NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	[labor_sched_hours_qty] [numeric](38, 6) NULL )

create clustered index idx1 on #FACT_Labor_Schedule_Version ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,labor_schedule_version_id,comparable_flag_id,business_unit_status_id,[datasource_id] )
		
insert into #bulist (datasource_id,bu_id,business_date,business_unit_id)  
select a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id
from f_gen_labor_requirements_min_bu_day_load a
	join dim_org_hierarchy b with (nolock) on a.bu_id = b.org_hierarchy_id
											and a.datasource_id = b.datasource_id
											and a.datasource_id = case when @datasource_id < 1 then a.datasource_id else @datasource_id end
	join dim_time t with (nolock) on a.business_date = t.calendar_day
/*where not exists (select 1 from #bulist bl
				where a.bu_id = bl.bu_id
				and a.business_date = bl.business_date
				and a.datasource_id = bl.datasource_id)*/ -- #No need for this clause since table is empty at this point
group by a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id

create index idx1 on #bulist (bu_id,business_date,datasource_id) -- #Now is good to create the index
		
insert into #bulist (datasource_id,bu_id,business_date,business_unit_id)  
select a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id
from f_lab_rpt_sched_hours_load a 
	join dim_org_hierarchy b with (nolock) on a.bu_id = b.org_hierarchy_id
										and a.datasource_id = b.datasource_id
										and a.datasource_id = case when @datasource_id < 1 then a.datasource_id else @datasource_id end
	join dim_time t with (nolock) on a.business_date = t.calendar_day
where not exists (select 1 
				from #bulist bl
				where a.bu_id = bl.bu_id
				and a.business_date = bl.business_date
				and a.datasource_id = bl.datasource_id)
group by a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id


while exists( select * from #bulist where processed_flag = 'N')
	begin
		truncate table #bulist_processing
		truncate table #FACT_Labor_Schedule_Version_Accuracy
		truncate table #FACT_Labor_Demand_Ideal
		truncate table #FACT_Labor_Schedule_Version
  
		select @iteration_count = coalesce(fact_labor_iteration_bu_count,200)
		from wh_ssis_global_settings_retail
			
		select @sql = 'insert into #bulist_processing (datasource_id,bu_id,business_date,business_unit_id)  
					select top '+cast(@iteration_count as nvarchar(30))+' datasource_id,bu_id,business_date,business_unit_id
					from #bulist  
					where processed_flag = ''N'''  
					--order by datasource_id asc,business_date desc, bu_id asc' #No need for ORDER BY clause in Insert
		 exec sp_executesql @sql
		  
		 
		insert into #FACT_Labor_Schedule_Version (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id,business_unit_status_id,day_comparable_flag_id,labor_sched_hours_qty)			
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,a.labor_schedule_version_id,null as business_unit_status_id,null as comparable_flag_id,sum(labor_sched_hours_qty) as labor_sched_hours_qty  
		from FACT_Labor_Schedule_Version a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id ,daypart_id,a.labor_schedule_version_id	

--changed the order of processing so that we only generate demand versions where we have schedule.  need to include auto version whenever we have demand.
		insert into #FACT_Labor_Demand_Ideal (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,labor_schedule_version_id, demand_calc_fte_qty, demand_adj_fte_qty)
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,1000001 as labor_schedule_version_id, sum(demand_calc_fte_qty) as demand_calc_fte_qty, sum(demand_adj_fte_qty) as demand_adj_fte_qty
		from fact_labor_demand_ideal a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id


		insert into #FACT_Labor_Demand_Ideal (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id, demand_calc_fte_qty, demand_adj_fte_qty)
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id, sum(demand_calc_fte_qty) as demand_calc_fte_qty, sum(demand_adj_fte_qty) as demand_adj_fte_qty
		from fact_labor_demand_ideal a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
			join DIM_Labor_Schedule_Version_Code dlsv on dlsv.labor_schedule_version_id <> 0
			join (select datasource_id,business_unit_id,calendar_day,labor_schedule_version_id -- # DISTINCT removed
					from #FACT_Labor_Schedule_Version 
					where labor_schedule_version_id>1000001
					group by datasource_id,business_unit_id,calendar_day,labor_schedule_version_id -- # GROUP BY added
					) l on a.datasource_id=l.datasource_id 
						and a.business_unit_id=l.business_unit_id 
						and a.calendar_day=l.calendar_day 
						and dlsv.labor_schedule_version_id=l.labor_schedule_version_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id
												
--select * from #bulist_processing

		insert into #FACT_Labor_Schedule_Version_Accuracy (datasource_id
												,business_unit_id
												,calendar_day
												,time_quarter_hour_id
												,labor_workgroup_id
												,labor_role_id
												,daypart_id
												,labor_schedule_version_id
												,labor_demand_acc_hours_qty
												,labor_demand_acc_adj_hours_qty
												,labor_sched_acc_hours_qty
												,ver_covered_time
												,ver_covered_time_adj
												,ver_calc_sched_accuracy_hours_qty
												,ver_adj_sched_accuracy_hours_qty
												,ver_calc_sched_accuracy_id
												,ver_adj_sched_accuracy_id
												,business_unit_status_id
												,comparable_flag_id
												,day_comparable_flag_id)
		select datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id
												, max(labor_demand_acc_hours_qty) as labor_demand_acc_hours_qty
												, max(labor_demand_acc_adj_hours_qty) as labor_demand_acc_adj_hours_qty
												, max(labor_sched_hours_qty) as labor_sched_acc_hours_qty
												, max(ver_covered_time) as ver_covered_time
												, max(ver_covered_time_adj) as ver_covered_time_adj
												, max(ver_calc_sched_accuracy_hours_qty) as ver_calc_sched_accuracy_hours_qty
												, max(ver_adj_sched_accuracy_hours_qty) as ver_adj_sched_accuracy_hours_qty
												,coalesce(t.sched_accuracy_id,0) as ver_calc_sched_accuracy_id  
												,coalesce(ta.sched_accuracy_id,0) as ver_adj_sched_accuracy_id  
												,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
		from (select demand.datasource_id,demand.business_unit_id,demand.calendar_day,demand.time_quarter_hour_id,demand.labor_workgroup_id,demand.labor_role_id,demand.daypart_id,demand.labor_schedule_version_id
												,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
												,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
												,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty 
												,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as ver_covered_time
												,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as ver_covered_time_adj
												,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as [ver_calc_sched_accuracy_hours_qty]   
												,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as [ver_adj_sched_accuracy_hours_qty]  
												,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
				from #FACT_Labor_Demand_Ideal demand
					left outer join  #FACT_Labor_Schedule_Version sched on demand.business_unit_id = sched.business_unit_id  
																	and demand.calendar_day = sched.calendar_day  
																	and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
																	and demand.labor_workgroup_id  = sched.labor_workgroup_id  
																	and demand.labor_role_id  = sched.labor_role_id  
																	and demand.datasource_id = sched.datasource_id
																	and demand.labor_schedule_version_id = sched.labor_schedule_version_id
											
				UNION ALL
				select sched.datasource_id,sched.business_unit_id,sched.calendar_day,sched.time_quarter_hour_id,sched.labor_workgroup_id,sched.labor_role_id,sched.daypart_id,sched.labor_schedule_version_id
														,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
														,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
														,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty 
														,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as ver_covered_time
														,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as ver_covered_time_adj
														,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as [ver_calc_sched_accuracy_hours_qty]   
														,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as [ver_adj_sched_accuracy_hours_qty]  
														,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
				from #FACT_Labor_Schedule_Version sched   
					left outer join #FACT_Labor_Demand_Ideal demand on demand.business_unit_id = sched.business_unit_id  
																	and demand.calendar_day = sched.calendar_day  
																	and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
																	and demand.labor_workgroup_id  = sched.labor_workgroup_id  
																	and demand.labor_role_id  = sched.labor_role_id  
																	and demand.datasource_id = sched.datasource_id) u
					left outer join dim_labor_over_under t on [ver_calc_sched_accuracy_hours_qty]  = t.value  
					left outer join dim_labor_over_under ta on [ver_adj_sched_accuracy_hours_qty]  = ta.value  
				group by datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id
										,coalesce(t.sched_accuracy_id,0),coalesce(ta.sched_accuracy_id,0)
										,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
				-- order by calendar_day,business_unit_id,labor_workgroup_id,labor_role_id,labor_schedule_version_id,time_quarter_hour_id, datasource_id # No need for ORDER BY clause in Insert



		delete FACT_Labor_Schedule_Version_Accuracy
		from FACT_Labor_Schedule_Version_Accuracy a 
			join #bulist_processing bup on a.datasource_id = bup.datasource_id
										and a.calendar_day = bup.business_date
										and a.business_unit_id = bup.bu_id
		where not exists (select * 
						from #FACT_Labor_Schedule_Version_Accuracy b
						where a.datasource_id = b.datasource_id
						and a.business_unit_id=b.business_unit_id
						and a.calendar_day=b.calendar_day
						and a.time_quarter_hour_id=b.time_quarter_hour_id
						and a.labor_workgroup_id=b.labor_workgroup_id
						and a.labor_role_id=b.labor_role_id
						and coalesce(a.daypart_id,0) = coalesce(b.daypart_id,0)
						and a.labor_schedule_version_id = b.labor_schedule_version_id
						and coalesce(a.labor_demand_acc_hours_qty,-9999)=coalesce(b.labor_demand_acc_hours_qty,-9999)
						and coalesce(a.labor_demand_acc_adj_hours_qty,-9999)=coalesce(b.labor_demand_acc_adj_hours_qty,-9999)
						and coalesce(a.labor_sched_acc_hours_qty,-9999)=coalesce(b.labor_sched_acc_hours_qty,-9999)
						and coalesce(a.ver_covered_time,-9999)=coalesce(b.ver_covered_time,-9999)
						and coalesce(a.ver_covered_time_adj,-9999)=coalesce(b.ver_covered_time_adj,-9999)
						and coalesce(a.ver_calc_sched_accuracy_hours_qty,-9999)=coalesce(b.ver_calc_sched_accuracy_hours_qty,-9999)
						and coalesce(a.ver_adj_sched_accuracy_hours_qty,-9999)=coalesce(b.ver_adj_sched_accuracy_hours_qty,-9999)
						and a.ver_calc_sched_accuracy_id=b.ver_calc_sched_accuracy_id
						and a.ver_adj_sched_accuracy_id=b.ver_adj_sched_accuracy_id
						and coalesce(a.business_unit_status_id,0)=coalesce(b.business_unit_status_id,0)
						and coalesce(a.comparable_flag_id,0)=coalesce(b.comparable_flag_id,0)
						and coalesce(a.day_comparable_flag_id,0) = coalesce(b.day_comparable_flag_id,0))
			
		set @UpdateCount = @UpdateCount + @@ROWCOUNT

--select top 1 * from FACT_Labor_Schedule_Version_Accuracy

		insert FACT_Labor_Schedule_Version_Accuracy (datasource_id
												,business_unit_id
												,calendar_day
												,time_quarter_hour_id
												,labor_workgroup_id
												,labor_role_id
												,daypart_id
												,labor_schedule_version_id
												,labor_demand_acc_hours_qty
												,labor_demand_acc_adj_hours_qty
												,labor_sched_acc_hours_qty
												,ver_covered_time
												,ver_covered_time_adj
												,ver_calc_sched_accuracy_hours_qty
												,ver_adj_sched_accuracy_hours_qty
												,ver_calc_sched_accuracy_id
												,ver_adj_sched_accuracy_id
												,business_unit_status_id
												,comparable_flag_id
												,day_comparable_flag_id)
		select a.datasource_id
												,a.business_unit_id
												,a.calendar_day
												,a.time_quarter_hour_id
												,a.labor_workgroup_id
												,a.labor_role_id
												,a.daypart_id
												,a.labor_schedule_version_id
												,a.labor_demand_acc_hours_qty
												,a.labor_demand_acc_adj_hours_qty
												,a.labor_sched_acc_hours_qty
												,a.ver_covered_time
												,a.ver_covered_time_adj
												,a.ver_calc_sched_accuracy_hours_qty
												,a.ver_adj_sched_accuracy_hours_qty
												,a.ver_calc_sched_accuracy_id
												,a.ver_adj_sched_accuracy_id
												,a.business_unit_status_id
												,a.comparable_flag_id
												,a.day_comparable_flag_id
		from #FACT_Labor_Schedule_Version_Accuracy a
			left outer join (select f.calendar_day,f.business_unit_id,f.business_unit_status_id,f.labor_workgroup_id,f.labor_role_id,f.time_quarter_hour_id,f.datasource_id,f.labor_schedule_version_id
							from FACT_Labor_Schedule_Version_Accuracy f							
								join #bulist_processing bup on f.datasource_id = bup.datasource_id
															and f.calendar_day = bup.business_date
															and f.business_unit_id = bup.bu_id
							group by f.calendar_day,f.business_unit_id,f.business_unit_status_id,f.labor_workgroup_id,f.labor_role_id,f.time_quarter_hour_id,f.datasource_id,f.labor_schedule_version_id ) b
								on a.calendar_day = b.calendar_day 
									and a.business_unit_id = b.business_unit_id
									and coalesce(a.business_unit_status_id,0)=coalesce(b.business_unit_status_id,0)
									and a.labor_workgroup_id = b.labor_workgroup_id
									and a.labor_role_id = b.labor_role_id
									and a.time_quarter_hour_id = b.time_quarter_hour_id
									and a.datasource_id = b.datasource_id
									and a.labor_schedule_version_id = b.labor_schedule_version_id
		where b.business_unit_id is null
									
		set @InsertCount = @InsertCount + @@ROWCOUNT		
		
		update a
		set a.processed_flag = 'Y'
		from #bulist a
			join #bulist_processing b on a.bu_id = b.bu_id and a.business_date = b.business_date
end

update WH_Process_Log
set	end_datetime = getdate(),
	records_inserted = @InsertCount,
	records_updated = @UpdateCount
where record_id = @Process_Log_ID

GO

Open in new window

NOTE: I added comments in the lines that I've changed.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

10/5/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

n_srikanth4

10/7/2016

ASKER

You are great Sir , I need to try on production environment . But I think it should work sir.