CODEWITHSUNDEEP
sqlsql-server-2008
tyo
tyo

Reputation: 67

how to update column with previous data

how to get previous record depend on unique_id and date? i want make my table (dbo : Daily_Summary) :

ID    Partner     part_no    Date      Periode_Sum     Previous_Sum
1      aa            12    2011-12-21       40             
2      aa            12    2011-12-22       30              40
3      bb2           13    2011-12-22       20             
4      bb2           13    2011-12-23       30              20
5                          2011-12-24
6                          2011-12-25
7      aa            12    2011-12-26       30              70
8      bb2           13    2011-12-27       40              50
and so on

which 'Previous_Sum' is function update, and the function only update the previous record of Periode_Sum. And group by partner, part_no and date

My display query had error to. When i display the previous record by partner, part_no and date. the record of previous_sum only displayed the first partner. here is my query :

 SELECT ds.partner_id, ds.part_no, ds. periode_in
        , ds.periode_out, ds.periode_date, ds.periode_sum, 
(   
  SELECT F1.periode_sum
  FROM Daily_Summary as F1 where 
  F1.periode_sum =
    ( 
      SELECT  Max(F2.periode_sum) 
      FROM Daily_Summary as F2 where F2.periode_date < ds.periode_date
      and F2.partner_id=ds.partner_id and F2.part_no = ds.part_no
    ) 
) AS Prev_Value
FROM Daily_Summary ds
where stsrc='A'
order by ds.partner_id, ds.periode_date

i tried using declare new param but not working :

DECLARE @prev_sum INT = 0
DECLARE @dudut INT = 2

SELECT @prev_sum = 
(select sum(periode_sum) 
    from Daily_Summary t1 
    where t1.partner_id = ds.partner_id and t1.part_no = ds.part_no
        and t1.periode_date < ds.periode_date --and t1.id < t.id
) 
FROM Daily_Summary ds
where stsrc='A'
order by ds.partner_id, ds.periode_date 

select partner_id,part_no,model,periode_sum,
case when @prev_sum is null then @dudut else @prev_sum end 
FROM daily_summary
where stsrc='A'

Upvotes: 2

Views: 214

Answers (1)

Michał Powaga
Michał Powaga

Reputation: 23173

select * into #tab from (
    select 1 as id, 'aa' as partner, 12 as part_no, 
        cast('2011-12-21' as datetime) as date, 40 as periode_sum union all
    select 2, 'aa', 12, '2011-12-22', 30 union all
    select 3, 'bb2', 13, '2011-12-22', 20 union all
    select 4, 'bb2', 13, '2011-12-23', 30 union all
    select 5, null, null, '2011-12-24', null union all
    select 6, null, null, '2011-12-25', null union all
    select 7, 'aa', 12, '2011-12-26', 30 union all
    select 8, 'bb2', 13, '2011-12-27', 40
) t


select *, (select sum(periode_sum) 
    from #tab t1 
    where t1.partner = t.partner and t1.part_no = t.part_no
        and t1.date < t.date and t1.id < t.id
) as previous_sum
from #tab t

If more than one row per day for specific pair (partner, part_no) is allowed then instead of and t1.date < t.date and t1.id < t.id you should use and t1.date <= t.date and t1.id < t.id. You can use just and t1.id < t.id if id ensures proper order (in time) for all rows.

Result:

id   partner    part_no    date                       periode_sum     previous_sum
1    aa         12         2011-12-21 00:00:00.000    40              NULL
2    aa         12         2011-12-22 00:00:00.000    30              40
3    bb2        13         2011-12-22 00:00:00.000    20              NULL
4    bb2        13         2011-12-23 00:00:00.000    30              20
5    NULL       NULL       2011-12-24 00:00:00.000    NULL            NULL
6    NULL       NULL       2011-12-25 00:00:00.000    NULL            NULL
7    aa         12         2011-12-26 00:00:00.000    30              70
8    bb2        13         2011-12-27 00:00:00.000    40              50

Upvotes: 1

Related Questions