April 12, 2016 at 9:42 am
Hi all,
I have a Source table Tbl_Src that has 5 columns:
Company_id,Salesrep_sales_date,Product_id,sales_Amt
I have a process that runs and re-calculate the sales_amt and re-distribute portion of the sales to others based on same business rules and commission, etc...
This process is also in SQL and has about 5 steps where I take the tbl_src data move it to a #tmp table and run each step.
I tend to compare the total amount from #tmp table to tbl_src after each step to make sure the total amt per company did not change (zero variance).
Like :
Select Z.*,(z.src_amt-z.tmp_amt) as variance
From
(select company_id,sum(amt) as src_amt from tbl_src group by company_id) t1
full outer join
(select company_id,sum(amt) as tmp_amt from #tmp group by company_id) t2
on t1.company_id=t2.company_id
) T
My question is : I need to put this check step in a function or SP where I pass the Src and tmp tables as variables and call it from within the code without typing it five times after each step.
any thoughts would be appreciated.
Thanks
April 12, 2016 at 9:55 am
oneteabag (4/12/2016)
Hi all,I have a Source table Tbl_Src that has 5 columns:
Company_id,Salesrep_sales_date,Product_id,sales_Amt
I have a process that runs and re-calculate the sales_amt and re-distribute portion of the sales to others based on same business rules and commission, etc...
This process is also in SQL and has about 5 steps where I take the tbl_src data move it to a #tmp table and run each step.
I tend to compare the total amount from #tmp table to tbl_src after each step to make sure the total amt per company did not change (zero variance).
Like :
Select Z.*,(z.src_amt-z.tmp_amt) as variance
From
(select company_id,sum(amt) as src_amt from tbl_src group by company_id) t1
full outer join
(select company_id,sum(amt) as tmp_amt from #tmp group by company_id) t2
on t1.company_id=t2.company_id
) T
My question is : I need to put this check step in a function or SP where I pass the Src and tmp tables as variables and call it from within the code without typing it five times after each step.
any thoughts would be appreciated.
Thanks
Your 'question' is a requirement, not a question. Are you looking for guidance in a particular area?
If you are passing a temp table as a variable, presumably something else has already generated it? You may face scoping issues with that.
Are the column names always the same, regardless of table name?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2016 at 11:29 am
1)Yes. Column names are always same regardless from table name.
2) The #tmp table is created basically from from tbl_src :
Select * into #tmp from tbl_src
3) data operation is applied 5 times to the #tmp table as
a) Some SQL code is run to reclass the data in #tmp
b) Then Check with Tbl_src for variances (to make sure total Amt by company did not change)
4) Because step 3b is done 5 times I was asking if there is an easy way to call this Check by passing the 2 tables names as a parameter. The challenge is I am trying to pass a #tmp table as a variable.
Thanks
April 14, 2016 at 11:57 am
Anyone?..Any thoughts?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply