Update rows with same Account ID

[tranzeo]

See attached image.
Need to update 'Beg Balance' of FY 19 to be the same as FY 18.
Account ID is the unique identifier that can be used as link. Please kindly let me know what will be the SQL script like.

Thanks.

[gigawatt38]

Hi tranzeo,

pls try my script:

UPDATE next_year
SET next_year.[Beg Balance] = prev_year.[Beg Balance]
FROM [F0902 Account Balance] prev_year
 INNER JOIN [F0902 Account Balance] next_year
  ON next_year.[Account ID] = prev_year.[Account ID]
  AND prev_year.FY = 18
  AND next_year.FY = 19 

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

[harishgg1]

Hi

I tried doing with SQL select

CREATE Data Script

drop table AccountBalance
go 

create table AccountBalance
(
AccountID int identity(1,1) not null,
Century int null,
FY int null,
FiscQTR int null,
LT varchar(50) null,
SubLedger varchar(20) null,
Co varchar(100) null,
BegBalance money,
NetPosting money
)
go 

insert into AccountBalance(Century ,FY ,LT ,Co ,BegBalance ,NetPosting) Values 
(20,18,'BA','00001',-80000.00,-5000.00),
(20,18,'BA','00001',17199304.62,1479749.00),
(20,19,'BA','00001',null,-5000.00),
(20,19,'BA','00001',null,1479749.00)
go 

select * from AccountBalance
go

SELECT

SELECT a.accountid, 
       a.fy, 
       a.lt, 
       a.co, 
       a.begbalance, 
       b.accountid, 
       b.fy, 
       b.lt, 
       b.co, 
       Isnull(b.begbalance, a.begbalance) 
FROM   (SELECT Row_number() 
                 OVER( 
                   ORDER BY accountid) AS rna, 
               * 
        FROM   accountbalance 
        WHERE  fy = 18) a 
       JOIN (SELECT Row_number() 
                      OVER( 
                        ORDER BY accountid) AS rnb, 
                    * 
             FROM   accountbalance 
             WHERE  fy = 19) b 
         ON a.rna = b.rnb

How does this look ???

different sql

Diff SQL

SELECT a.accountid, 
       a.fy, 
       a.lt, 
       a.co, 
       a.begbalance 
FROM   (SELECT Row_number() 
                 OVER( 
                   ORDER BY accountid) AS rna, 
               * 
        FROM   accountbalance 
        WHERE  fy = 18) a 
       JOIN (SELECT Row_number() 
                      OVER( 
                        ORDER BY accountid) AS rnb, 
                    * 
             FROM   accountbalance 
             WHERE  fy = 19) b 
         ON a.rna = b.rnb 
UNION ALL 
SELECT b.accountid, 
       b.fy, 
       b.lt, 
       b.co, 
       Isnull(b.begbalance, a.begbalance) 
FROM   (SELECT Row_number() 
                 OVER( 
                   ORDER BY accountid) AS rna, 
               * 
        FROM   accountbalance 
        WHERE  fy = 18) a 
       JOIN (SELECT Row_number() 
                      OVER( 
                        ORDER BY accountid) AS rnb, 
                    * 
             FROM   accountbalance 
             WHERE  fy = 19) b 
         ON a.rna = b.rnb

How does this look ????