Question

• ajkarora

  SSChasing Mays

  Points: 601

  More actions

  January 30, 2017 at 11:07 am

  #334738

  Hello All, 

  I have a table with the following information

  

  I want to add a new column called Previous Balance and take the data from previous run date. So it should look like this

  

  Is there anyway to do this in SQL Server 2008?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 30, 2017 at 11:41 am

  #1925701

  Yes.
  No table definitions, so I can't just write you a query. The essence of it is that you join the table to itself with an offset. You can use Row_Number function to generate that column to join on in a CTE.

  Roughly

  With  RawData as (
  select date, lot, balance, row_number() over (partition by <put partitioning column here> order by <put column defining order here>) as RowNumber
)
SELECT <column list>
FROM RawData as current left outer join RawData as previous on current.RowNumber = previous.RowNumber + 1;

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• DesNorton

  SSC-Insane

  Points: 24285

  More actions

  January 30, 2017 at 11:44 am

  #1925702

  This should do the trick

  
CREATE TABLE #LotInfo (
  [Date]  DATE NOT NULL
, Lot   INT NOT NULL
, QtyBalance INT NOT NULL
);

  INSERT INTO #LotInfo ( [Date], Lot, QtyBalance )
VALUES ( '2017-01-30', 1, 50 )
  , ( '2017-01-30', 2, 40 )
  , ( '2017-01-23', 1, 30 )
  , ( '2017-01-23', 2, 20 )
  , ( '2017-01-18', 1, 10 )
  , ( '2017-01-18', 2, 0 );

  ALTER TABLE #LotInfo
ADD PreviousBalance INT;

  WITH cteLotInfo AS (
SELECT [Date], Lot, QtyBalance, PreviousBalance
  , rn = ROW_NUMBER() OVER (PARTITION BY Lot ORDER BY [Date])
FROM #LotInfo
)
, ctePrevBal AS (
SELECT curr.[Date], curr.Lot, curr.QtyBalance, PreviousBalance = prev.QtyBalance
FROM cteLotInfo AS curr
LEFT JOIN cteLotInfo AS prev
  ON curr.Lot = prev.Lot
  AND curr.rn = prev.rn +1
)
UPDATE #LotInfo
SET PreviousBalance = prev.PreviousBalance
FROM #LotInfo AS src
INNER JOIN ctePrevBal AS prev
  ON src.Lot = prev.Lot
 AND src.[Date] = prev.[Date];


  ---

  How to post data/code on a forum to get the best help.

• Andrew P

  SSCarpal Tunnel

  Points: 4649

  More actions

  January 30, 2017 at 11:48 am

  #1925703

  ajkarora - Monday, January 30, 2017 11:07 AM

  Hello All, 

  I have a table with the following information

  

  I want to add a new column called Previous Balance and take the data from previous run date. So it should look like this

  

  Is there anyway to do this in SQL Server 2008?

  If you're asking how to add this column to the table in the database, I don't believe it's possible to do this easily. I see two possible solutions for that need, reading from a view that has the required previous balance column, or adding the Previous_Balance column and setting that using an AFTER INSERT trigger.

  Here's how to make a view that would include the previous balance:
  create table LotBalances (
    LotDate date
    , LotNumber int
    , Quantity int
)

  insert into LotBalances(
    lotdate
    , lotnumber
    , quantity
)
select '20170130', 1, 50
union all select '20170130', 2, 40
union all select '20170123', 1, 30
union all select '20170123', 2, 20
union all select '20170118', 1, 10
union all select '20170118', 2, 0;

  GO;

  create view LotBalancesView as
select
    lotbalances.LotDate
    , lotbalances.LotNumber
    , lotbalances.Quantity
    , lead(lotbalances.quantity, 1) over(partition by lotbalances.lotnumber order by lotbalances.lotdate desc) as PreviousBalance
from lotbalances

  select
   LotBalancesView.LotDate
   , LotBalancesView.LotNumber
  , LotBalancesView.PreviousBalance
  , LotBalancesView.Quantity
from LotBalancesView
order by LotBalancesView.lotdate desc
    , LotBalancesView.lotnumber

• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 30, 2017 at 11:51 am

  #1925705

  Andrew P - Monday, January 30, 2017 11:48 AM

  ajkarora - Monday, January 30, 2017 11:07 AM

  Hello All, 

  I have a table with the following information

  

  I want to add a new column called Previous Balance and take the data from previous run date. So it should look like this

  

  Is there anyway to do this in SQL Server 2008?

  If you're asking how to add this column to the table in the database, I don't believe it's possible to do this easily. I see two possible solutions for that need, reading from a view that has the required previous balance column, or adding the Previous_Balance column and setting that using an AFTER INSERT trigger.

  Here's how to make a view that would include the previous balance:
  create table LotBalances (
    LotDate date
    , LotNumber int
    , Quantity int
)

  insert into LotBalances(
    lotdate
    , lotnumber
    , quantity
)
select '20170130', 1, 50
union all select '20170130', 2, 40
union all select '20170123', 1, 30
union all select '20170123', 2, 20
union all select '20170118', 1, 10
union all select '20170118', 2, 0;

  GO;

  create view LotBalancesView as
select
    lotbalances.LotDate
    , lotbalances.LotNumber
    , lotbalances.Quantity
    , lead(lotbalances.quantity, 1) over(partition by lotbalances.lotnumber order by lotbalances.lotdate desc) as PreviousBalance
from lotbalances

  select
   LotBalancesView.LotDate
   , LotBalancesView.LotNumber
  , LotBalancesView.PreviousBalance
  , LotBalancesView.Quantity
from LotBalancesView
order by LotBalancesView.lotdate desc
    , LotBalancesView.lotnumber

  LEAD doesn't exist in SQL Server 2008. It was introduced in 2012.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ajkarora

  SSChasing Mays

  Points: 601

  More actions

  January 30, 2017 at 12:25 pm

  #1925710

  DesNorton - Monday, January 30, 2017 11:44 AM

  This should do the trick

  
CREATE TABLE #LotInfo (
  [Date]  DATE NOT NULL
, Lot   INT NOT NULL
, QtyBalance INT NOT NULL
);

  INSERT INTO #LotInfo ( [Date], Lot, QtyBalance )
VALUES ( '2017-01-30', 1, 50 )
  , ( '2017-01-30', 2, 40 )
  , ( '2017-01-23', 1, 30 )
  , ( '2017-01-23', 2, 20 )
  , ( '2017-01-18', 1, 10 )
  , ( '2017-01-18', 2, 0 );

  ALTER TABLE #LotInfo
ADD PreviousBalance INT;

  WITH cteLotInfo AS (
SELECT [Date], Lot, QtyBalance, PreviousBalance
  , rn = ROW_NUMBER() OVER (PARTITION BY Lot ORDER BY [Date])
FROM #LotInfo
)
, ctePrevBal AS (
SELECT curr.[Date], curr.Lot, curr.QtyBalance, PreviousBalance = prev.QtyBalance
FROM cteLotInfo AS curr
LEFT JOIN cteLotInfo AS prev
  ON curr.Lot = prev.Lot
  AND curr.rn = prev.rn +1
)
UPDATE #LotInfo
SET PreviousBalance = prev.PreviousBalance
FROM #LotInfo AS src
INNER JOIN ctePrevBal AS prev
  ON src.Lot = prev.Lot
 AND src.[Date] = prev.[Date];


  This worked. Thank you so much!!