calculations in sql
-
hello,
I have output from a query into a temporary table like this
Description Col1 Col2 Col3 etc
Row1 Income 20 30 10
Row2 Outcome 10 20 30
Row3 Misc 40 10 30
I am trying to calculate losses/gains for columns, ie
Row4 would be
Loss/Gain (20-10)/20 for col1, (30-20)/30 for col2, (10-30)/10 for col3 etc..
Is this possible?
thank you for any help in advance
-
Yes, I would think that it is possible.
However, can't really say much more without knowing more about what the actual tables look like and the data within - along with examples of the desired results.
/Kenneth
-
The table looks like this
Description ProductA ProductB ProductC etc
Material1_Used 100 150 60
Material1_Produced 90 140 70
Material2_Used 150 160 170
Material2_Produced 170 151 185
etc etc
The desired results would be
Descritption ProductA ProductB ProductC
Material1_Used 100 150 60
Material1_Produced 90 140 70
Material2_Used 150 160 170
Material2_Produced 170 151 185
Material1_Loss -10 -10 10
Material2_Loss 20 9 -15
--------
The loss being Produced - Used, then maybe divide by Used * 100 to get percentages etc
I have used dynamic sql to put the products along the columns, now I want to calculate the losses/gains for each product
-
sorry, Material2_Loss should say 20 -9 15
(170-150), (151-160), (185-170)
-
Ok, I'll just borrow Remi's links for what we need in order to provide any useful help.
Must have that stuff, else it's pure guesswork.
/Kenneth
-
its OK Ken, I've figured out how to do this by using temporary tables and looping thru records
thanks for your help anyway........
-
Well, ok, whatever works for you.
However, by resorting to temptables and cursors, there's actually nothing you can't solve. The question (for us SQL dudes anyway) is more if you really need the 'easy way out' or if there is a 'better' way to solve the problem.
Anyways, it works for you, so I guess we'll just leave it at that.
/Kenneth
-
Wrong by design.
It must be like this:
Material | Action | Product | Usage
---------------------------------------
After this you may join rows in order you like and do whatever calculations you need.
_____________
Code for TallyGenerator -
I totally agree, its wrong design, but I am trying to replicate what someone is doing in Excel using Pivot tables. Once I've got this working, I'll probably re-design the tables and hopefully change his way of thinking!!!
When I've finished my queries, I'll post them here and would welcome your comments.
-
I shall attempt to describe what I'm trying to achieve, if anyone's interested:
Input table called Results
AssignWk
OrderGroup
PTGRP
Qty
Seq
5
Prod1
Raw Material Used
100
1
5
Prod1
Semi Finished Material Produced
90
2
5
Prod1
Semi Finished Material Used
85
3
5
Prod1
Finished Material Produced
70
4
5
Prod2
Raw Material Used
150
1
5
Prod2
Semi Finished Material Produced
155
2
5
Prod2
Semi Finished Material Used
150
3
5
Prod2
Finished Material Produced
160
4
several products and week no’s etc
Desired Output
Weekly Report for Week 5
Seq
PTGRP
Prod1
Prod2
Prod3
Prod4
1
Raw Material Used
100
150
2
Semi Finished Material Produced
90
155
3
Semi Finished Material Used
85
150
4
Finished Material Produced
70
160
5
Loss1 (%) (Seq 2 – Seq1)/Seq1 *100
10 %
-3.33 %
6
Loss2 (%) (Seq 3 – Seq4)/Seq3 *100
17.6 %
-6.66 %
and here's the procedure to produce the desired results
CREATE PROCEDURE dbo.PivotTable1(@WeekNum char(3)) AS
Create table dbo.#tbl_temp
(AssignWk float,
OrderGroup varchar (50) NULL,
PTGrp varchar (50) NULL,
Qty float,
Seq float)
Declare @product varchar(20)
Declare @RM_Used real,@SFM_Produced real,
@SFM_Used real,FM_Produced real
Declare @Loss1 real,@Loss2 real
Declare @sql varchar(8000)
-- Extract data for Week into temporary table
Insert into #tbl_temp
Select * from Results
where AssignWk = @WeekNum
-- Declare cursor for unique Products
Declare MyCursor Cursor for
Select distinct ordergroup from #tbl_temp
Open MyCursor
Fetch Next from MyCursor into @product
While (@@Fetch_Status <> -1)
begin
-- Extract totals by product for use in calculations
set @RM_Used = (select sum(Qty) from #tbl_temp
where OrderGroup = @product
and Seq = 1)
set @SFM_Produced = (select sum(Qty) from #tbl_temp
where OrderGroup = @product
and Seq = 2)
set @SFM_Used = (select sum(Qty) from #tbl_temp
where OrderGroup = @product
and Seq = 3)
set @FM_Produced = (select sum(Qty) from #tbl_temp
where OrderGroup = @product
and Seq = 4)
Set @Loss1 = 0
Set @Loss2 = 0
-- Calculate losses
if @RM_Used <> 0
begin
Set @Loss1 = (@RM_Used - @SFM_Produced) / @RM_Used * 100
end
if @SFM_Used <> 0
begin
Set @Loss2 = @SFM_Used/@FM_Produced * 100
end
-- Insert losses into temporary table with sequence numbers
Insert into #tbl_temp
select @WeekNum,@Product,'Loss1 %',@Loss1,5,1
Insert into #tbl_temp
select @WeekNum,@Product,'Loss2 %',@Loss2,6,1
end
Close MyCursor
Deallocate MyCursor
--Write dynamic SQL to extract report to look like Pivot Table
--There could be several products going across the columns
select @SQL = 'Select Seq,PTGRP'
Declare MyCursor CURSOR for
select distinct ordergroup
from #tbl_temp
Open MyCursor
Fetch Next from MyCursor into @product
While (@@Fetch_Status <> -1)
begin
select @SQL = @SQL + ', ' + @product + '=str(sum(case whenOrderGroup = ''' + @product + ''' then Qty end),10,1)'
Fetch Next from MyCursor into @product
End
Close MyCursor
Deallocate MyCursor
select @SQL = @SQL + ' from #tbl_temp ' +
' group by Seq,PTGRP
order by seq'
exec(@sql)
GO
I don't like the way the original data is stored, but like I said earlier, this report is currently produced in Excel using Pivot tables.
I am trying to convince them to move data and report to SQL server, not sure if this is the most efficient way of doing it
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply