SQL Server中的交叉计算

Question

I have a complicated request. I have to make a report in PHP like the picture below. Part summarized yield must be filled with across calculation. Like I explain in formula column, value in part summarized yield got from calculation between location yield and part summarized yield. (See this formula)

How to do that calculation in a PHP report? I already tried using a cursor, but it still did not work.

Here is my cursor calculation:

--In PHP file, i make query to insert data first to table tyield_summary
--Cursor to input yield_summary
Declare @nourut varchar(2), @maxnourut varchar(2), @bpnum varchar(20), @pnum varchar(20), @curnourut varchar(2), @psum decimal(18,2), @ysum decimal(18,2)

DECLARE StockCursor CURSOR
FOR

select no_urut, part_number, Part_Summary
from tyield_summary 
where no_urut<>'99'
order by part_number desc, no_urut asc

set @bpnum=''

OPEN StockCursor
FETCH NEXT FROM StockCursor INTO @nourut, @pnum, @psum

WHILE @@FETCH_STATUS=0
BEGIN


  if @bpnum=@pnum
   begin

    select top 1 @curnourut=no_urut
    from tyield_summary 
    where part_number=@pnum 
    and no_urut<@nourut 
    order by no_urut desc

    set @bpnum=@pnum

    select @maxnourut = max(no_urut) from tyield_summary 
    where part_number=@pnum 


    update tyield_summary
    set yield_summary = case when Part_Summary=0 then @psum else (Part_Summary*@psum)/100 end
    where part_number=@pnum
    and no_urut=@curnourut



   end 
  else
   begin

    set @bpnum=@pnum

   end

  FETCH NEXT FROM StockCursor INTO @nourut, @pnum, @psum

END

CLOSE StockCursor
DEALLOCATE StockCursor

Here table structure :

I need to fill part_summary field using formula that i show in excel. In formula show, calculation using cross field.

Answer 1

Here is how you can do it (without nasty cursors). Just use row number over your ordering/aggregation criteria to find the next row, left join each row with its next (not forgetting the aggregation criteria) and it's done.

Let's use an example (and here is how you properlu post a table structure):

create table Lazydude
(
   Partno varchar(20) not null
  ,Seq int not null
  ,[Value] float not null
)
GO

insert into Lazydude (Partno, Seq, [Value])
values
 ('AAA', 1, 77.7)
,('BBB', 0, 2) 
,('BBB', 3, 3) 
,('BBB', 9, 5) 
,('CCC', 1, 33.3) 
,('CCC', 2, 33.3) 
GO

and to select using row number and use the result in a self-join

with Temp as(
  select Partno, Seq, [Value]
  ,ROW_NUMBER() OVER(ORDER BY Partno, Seq) AS [Row] 
  from Lazydude
 )
 select t0.Partno, t0.Seq, t0.[Value], t0.[Row]
 , t1.row as [Next Row], t1.[Value] as [Next Value]
 , case when t1.row is null
     then t0.[Value]
     else t0.Value * t1.Value
  end as [The Calculation]
from Temp t0
left join Temp t1 on t1.[Row] = t0.[Row] + 1 and t1.Partno = t0.Partno

You can see the results in the SQL Fiddle

Partno  Seq Value   Row  Next Row   Next Value  The Calculation
AAA     1   77.7    1    (null)     (null)       77.7
BBB     0   2       2    3          3            6
BBB     3   3       3    4          5            15
BBB     9   5       4    (null)     (null)       5
CCC     1   33.3    5    6          33.3         1108.8899999999999
CCC     2   33.3    6    (null)     (null)       33.3