Total = Total(t-1) - (Resource_Name1-Resource_Name1). 

quote:

---

Originally posted by hasanqz

here is my problem For reference:

http://www.pge.com/pipeline/operations/cgt_storage_search.page

If you go to the following link and search for Sept 23, 2013 and surrounding dates, you will see that the bottom row (PG&E Total Gas in Storage) does not have values starting Sept 22. But the values can be calculated using the Sept 23 PG&E Total Gas in Storage and subtracting (PG&E Core and Market Center (Injection) - PG&E Core and Market Center (Withdrawal) for Sept 22.

---

select a.opr_date
      ,a.resource_name
      ,sum(b.value*case
                      when b.resource_name='PG&E Core and Market Center'
                       and b.direction='Injection'
                      then -1
                      else  1
                   end
          ) as value
      ,'BalancingGas' as direction
  from (select a.opr_date
              ,min(b.opr_date) as total_opr_date
          from table_name as a
               inner join table_name as b
                       on b.opr_date<a.oprdate
                      and b.resource_name=a.resource_name
                      and b.direction='BalancingGas'
         where a.resource_name='PG&E Core and Market Center'
           and a.direction='Injection'
           and not exists (select 1
                             from table_name as t
                            where t.opr_date=a.opr_date
                              and t.resource_name='PG&E Total Gas in Storage'
                              and t.direction='BalancingGas'
                          )
         group by a.opr_date
       ) as a
       inner join table_name as b
               on b.opr_date>a.opr_date
              and b.opr_date<=a.total_opr_date
              and ((b.resource_name='PG&E Total Gas in Storage'
              and   b.direction='BalancingGas'
                   )
               or  (b.resource_name='PG&E Core and Market Center'
              and   b.direction in ('Injection','Withdrawel')
                  ))
 group by a.opr_date

quote:

---

Originally posted by hasanqz

@bitsmed,
you are doing it correctly. This calculated method isn't the most accurate way to come up with the Total but its the best estimate possible given availability of the data

@gbritton
I did not create the table so I'm not sure where to get this statement from. I am simply writing a query to generate the missing data based on existing value.

Here is the table structure for just Sept 22 and Sept 23...As you can see Sept 22 does not have PG&E Total Gas in Storage but I want to calculate it by taking Sept 23's PG&E Total Gas in Storage MINUS [Sept 22's PG&E Core and Market Center (where direction = Injection) MINUS Sept 22's PG&E Core and Market Center (where direction = Withdrawal)]. I want to do this for all remaining dates prior to Sept 23.

OPR_DATE RESOURCE_NAME VALUE DIRECTION
9/22/2013 PG&E Core and Market Center 409 Injection
9/22/2013 PG&E Core and Market Center 159 Withdrawal
9/23/2013 PG&E Core and Market Center 381 Injection
9/23/2013 PG&E Core and Market Center 159 Withdrawal
9/23/2013 PG&E Total Gas in Storage 99787 BalancingGas

---