Database

select s.IdStaff, s.NamaStaff, 
	sum(pd.Qty *
	case s.posisi
		when 'K.Teknisi' then kd.komisiteknisi
		when 'K.Controller' then kd.komisitelemarketing
		when 'K.Manager' then kd.komisicontroller
		when 'K.TeleMarketing' then kd.komisimanager
		when 'K.Cadangan' then kd.komisicadangan
	end) Insentif
from staff s inner join penjualan p on s.idstaff = p.idstaff
	inner join penjualandetail pd on p.idpenjualan = pd.idpenjualan
	inner join komisidetail kd on pd.idbarang = kd.idbarang
	where p.tglpenjualan between 12/1/2011 and 12/30/2011
group by s.idstaff, s.namastaff

Hi

I need help in writing a SQL query that gives the difference in values for two consecutive dates.

My table structure is as follows :

 Collapse | Copy Code
Symbol Name   Dates       Outstanding values
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0.7995
VAN Equity     12/5/2011   0.7000
VAN Equity     12/8/2011   0.7000

I want the output in the following form :

 Collapse | Copy Code
Symbol Name   Dates       Difference
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0
VAN Equity     12/5/2011   -0.0995
VAN Equity     12/8/2011   0
I came up with the below query.

 Collapse | Copy Code
WITH LHP AS
      (
      SELECT  *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
      FROM    test_table as LI
      )
SELECT   mc.[Outstanding Values] - mp.[Outstanding Values],
        mc.[Outstanding Values] , mp.[Outstanding Values]
FROM    LHP mc
inner JOIN    LHP mp
ON    mc.rn = mp.rn - 1
The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :

 Collapse | Copy Code
Symbol Name       Dates            Outstanding Values
VAN Equity   2011-12-03 00:00:00.000   0.7995
VAN Equity   2011-12-04 00:00:00.000   0.7995
VAN Equity   2011-12-05 00:00:00.000   0.7
VAN Equity   2011-12-08 00:00:00.000   0.7
VAN Equity   2011-12-09 00:00:00.000   0.6
VIN Equity   2011-12-03 00:00:00.000   0.1
VIN Equity   2011-12-04 00:00:00.000   0.2
VIN Equity   2011-12-05 00:00:00.000   0.7
VIN Equity   2011-12-08 00:00:00.000   0.7
VIN Equity   2011-12-09 00:00:00.000   0.6
VAT Equity   2011-12-03 00:00:00.000   0.1
VAT Equity   2011-12-04 00:00:00.000   0.2
VAT Equity   2011-12-05 00:00:00.000   0.7
VAT Equity   2011-12-08 00:00:00.000   0.7
VAT Equity   2011-12-09 00:00:00.000   0.6
i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.

 Collapse | Copy Code
No      Outstanding   Outstanding
Name    Values        Values
0.6995   0.7995         0.1
-0.1     0.1            0.2
-0.5995  0.2            0.7995
0.0995   0.7995         0.7
0        0.7            0.7
0.1      0.7            0.6
Any help would be greatly appreciated.