Link to home
Start Free TrialLog in
Avatar of Chris Michalczuk
Chris MichalczukFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Loops and updating in SQL Query

I have a table  "RENEWALS" with 55 records which has a column called ROWID
I want to update this column with a sequential number 1 to 55

RowID  AccountID
NULL    A
NULL    B
NULL    C

I used this to get the first rows filled and it works

DECLARE @RowID int
SET @RowID = 0
UPDATE [dbo].[Renewals]
SET @RowID = RowID = @RowID + 1

RowID  AccountID
1    A
2    B
3    C

Question 2
same as question 1 but I now want to update the number 1,2,3,4 etc for each change in the AccountID
However I want to do this at everychange of the accountid to update the accountID as the loop

RowID  AccountID   OrderID
1    A                            1
2    A                            2
3    B                            1
4    B                            2
5    C                            1
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Do you have another field (or set of fields) to uniquely identify a row?
Avatar of Chris Michalczuk

ASKER

for every change in AccountID then update the OrderID squentially. The data is already sorted so you could use the ROWID as the sort ie
ACCOUNTID+ROWID

AccountID is Varchar(30) RowID is Int
so in the example below the sequence restarts for every new occurance of accountID

RowID  AccountID   OrderID
1    A                            1
2    A                            2
3    B                            1
4    B                            2
RowID and OrderID are handled by a single cte below. The code below is to display only. After we like what it displays, we will replace "Select..." at the end with "Update ..." later to store permanent values of RowID and OrderID in the Renewal table.
;with cte(RowID, OrderID, AcctID, [Date], Number, [Type], Value) 
as
(
	SELECT Row_number() Over(Order By AcctID Asc) RowID, 
	  Row_number() OVER (Partition By AcctID ORDER BY AcctID) AS OrderID,
	  AcctID, [Date], Number, [Type], Value
	From RENEWALS
)
Select RowID, OrderID, AcctID, [Date], Number, [Type], Value from cte;
-- Update 

Open in new window


Here is the result for these two. Will continue with the rest shortly.
RowID, OrderID, AcctID,           [Date],  Number, [Type],      Value
1	     1	     A6UJ9A000A0B    18/07/2005	13265	Invoice	     12.00
2	     2	A6UJ9A000A0B	     29/09/2006	559	Credit Note -12.00
3	     1	A6UJ9A000A0D	     01/02/2005	12826	Invoice	     12.00
4	     1	A6UJ9A000A0I	     14/05/2004	12219	Invoice	     402.00
5	     2	A6UJ9A000A0I	     06/04/2005	13023	Invoice	     225.00
6	     3	A6UJ9A000A0I	     03/03/2006	13815	Invoice	     327.80
7	     4	A6UJ9A000A0I	     14/03/2007	14791	Invoice	     327.80
8	     5	A6UJ9A000A0I	     12/03/2008	16020	Invoice	     399.50
9	     6	A6UJ9A000A0I	     15/05/2008	905	Credit Note -399.50
10	     1	A6UJ9A000A0K	     02/03/2005	12954	Invoice	     90.00
11	     2	A6UJ9A000A0K	     23/06/2006	14088	Invoice	     90.00
12	     3	A6UJ9A000A0K	     07/12/2006	14558	Invoice	     90.00
13	     4	A6UJ9A000A0K	     07/01/2008	15738	Invoice	     90.00
14	     5	A6UJ9A000A0K	     08/01/2009	17054	Invoice	     90.00
15	     6	A6UJ9A000A0K	     05/01/2010	18425	Invoice	     90.00
16	     7	A6UJ9A000A0K	     04/01/2011	19887	Invoice	     90.00
17	     8	A6UJ9A000A0K	     04/01/2012	21826	Invoice	     90.00
18	     9	A6UJ9A000A0K	     02/01/2013	24585	Invoice	     90.00
19	     10	A6UJ9A000A0K	     07/01/2014	27668	Invoice	     90.00
20	     11	A6UJ9A000A0K	     05/01/2015	31582	Invoice	     90.00
21	     12	A6UJ9A000A0K	     06/01/2016	37523	Invoice	     90.00
22	     13	A6UJ9A000A0K	     03/01/2017	44464	Invoice	     90.00
23	     14	A6UJ9A000A0K	     20/01/2017	3611	Credit Note -90.00
24	     1	A6UJ9A000A0L	     02/11/2004	12573	Invoice	     12.00
25	     2	A6UJ9A000A0L	     23/08/2007	15286	Invoice	     350.00
26	     3	A6UJ9A000A0L	     11/08/2008	16518	Invoice	     90.00
27	     4	A6UJ9A000A0L	     21/08/2009	17910	Invoice	     90.00
28	     5	A6UJ9A000A0L	     04/08/2010	19262	Invoice	     90.00
29	     6	A6UJ9A000A0L	     04/08/2011	20961	Invoice	     90.00
30	     7	A6UJ9A000A0L	     16/08/2011	1946	Credit Note -90.00
31	     8	A6UJ9A000A0L	     01/08/2012	23458	Invoice	     90.00
32	     9	A6UJ9A000A0L	     28/08/2012	2321	Credit Note -90.00
33	     1	A6UJ9A000A0M	     07/01/2014	27595	Invoice	     179.10
34	     2	A6UJ9A000A0M	     09/01/2015	31737	Invoice	     179.10
35	     3	A6UJ9A000A0M	     05/01/2016	37423	Invoice	     179.10
36	     4	A6UJ9A000A0M	     03/01/2017	44387	Invoice	     179.10
37	     1	A6UJ9A000A0O	     10/01/2006	13679	Invoice	     168.00
38	     2	A6UJ9A000A0O	     05/12/2006	14534	Invoice	     168.00
39	     3	A6UJ9A000A0O	     07/01/2008	15715	Invoice	     204.00
40	     4	A6UJ9A000A0O	     08/01/2009	17033	Invoice	     204.00
41	     5	A6UJ9A000A0O	     04/01/2010	18403	Invoice	     204.00
42	     6	A6UJ9A000A0O	     04/01/2011	19868	Invoice	     252.00
43	     7	A6UJ9A000A0O	     04/01/2012	21801	Invoice	     204.00
44	     1	A6UJ9A000A0P	     20/02/2006	13776	Invoice	     426.14
45	     2	A6UJ9A000A0P	     17/01/2007	14679	Invoice	     426.14
46	     3	A6UJ9A000A0P	     14/02/2008	15889	Invoice	     479.40
47	     4	A6UJ9A000A0P	     16/02/2009	17196	Invoice	     479.40
48	     5	A6UJ9A000A0P	     02/02/2010	18546	Invoice	     479.40
49	     6	A6UJ9A000A0P	     01/02/2011	20020	Invoice	     479.40
50	     7	A6UJ9A000A0P	     01/02/2012	22028	Invoice	     479.40
51	     8	A6UJ9A000A0P	     01/02/2013	24843	Invoice	     479.40
52	     9	A6UJ9A000A0P	     03/02/2014	27886	Invoice	     479.40
53	     10	A6UJ9A000A0P	     02/02/2015	32001	Invoice	     479.40
54	     11	A6UJ9A000A0P	     01/02/2016	37937	Invoice	     479.40
55	     12	A6UJ9A000A0P	     01/02/2017	45042	Invoice	     479.40

Open in new window


Test Table RENEWALS from your previous posts in an Excel file:
--drop table RENEWALS
create Table RENEWALS(RowID int null, AcctID varchar(20), [Date] VARCHAR(10),	Number int, Type varchar(15), Value decimal(6,2));
--select * from RENEWALS
insert into RENEWALS(AcctID, [Date], Number, [Type], Value) values
('A6UJ9A000A0B', '18/07/2005', 13265, 'Invoice', 12.00)
, ('A6UJ9A000A0B', '29/09/2006', 559, 'Credit Note', 	-12.00)
, ('A6UJ9A000A0D', '01/02/2005', 12826, 'Invoice', 	12.00)
, ('A6UJ9A000A0I', '14/05/2004', 12219, 'Invoice', 	402.00)
, ('A6UJ9A000A0I', '06/04/2005', 13023, 'Invoice', 	225.00)
, ('A6UJ9A000A0I', '03/03/2006', 13815, 'Invoice', 	327.80)
, ('A6UJ9A000A0I', '14/03/2007', 14791, 'Invoice', 	327.80)
, ('A6UJ9A000A0I', '12/03/2008', 16020, 'Invoice', 	399.50)
, ('A6UJ9A000A0I', '15/05/2008', 905, 'Credit Note', 	-399.50)
, ('A6UJ9A000A0K', '02/03/2005', 12954, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '23/06/2006', 14088, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '07/12/2006', 14558, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '07/01/2008', 15738, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '08/01/2009', 17054, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '05/01/2010', 18425, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '04/01/2011', 19887, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '04/01/2012', 21826, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '02/01/2013', 24585, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '07/01/2014', 27668, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '05/01/2015', 31582, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '06/01/2016', 37523, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '03/01/2017', 44464, 'Invoice', 	90.00)
, ('A6UJ9A000A0K', '20/01/2017', 3611, 'Credit Note', 	-90.00)
, ('A6UJ9A000A0L', '02/11/2004', 12573, 'Invoice', 	12.00)
, ('A6UJ9A000A0L', '23/08/2007', 15286, 'Invoice', 	350.00)
, ('A6UJ9A000A0L', '11/08/2008', 16518, 'Invoice', 	90.00)
, ('A6UJ9A000A0L', '21/08/2009', 17910, 'Invoice', 	90.00)
, ('A6UJ9A000A0L', '04/08/2010', 19262, 'Invoice', 	90.00)
, ('A6UJ9A000A0L', '04/08/2011', 20961, 'Invoice', 	90.00)
, ('A6UJ9A000A0L', '16/08/2011', 1946, 'Credit Note', 	-90.00)
, ('A6UJ9A000A0L', '01/08/2012', 23458, 'Invoice', 	90.00)
, ('A6UJ9A000A0L', '28/08/2012', 2321, 'Credit Note', 	-90.00)
, ('A6UJ9A000A0M', '07/01/2014', 27595, 'Invoice', 	179.10)
, ('A6UJ9A000A0M', '09/01/2015', 31737, 'Invoice', 	179.10)
, ('A6UJ9A000A0M', '05/01/2016', 37423, 'Invoice', 	179.10)
, ('A6UJ9A000A0M', '03/01/2017', 44387, 'Invoice', 	179.10)
, ('A6UJ9A000A0O', '10/01/2006', 13679, 'Invoice', 	168.00)
, ('A6UJ9A000A0O', '05/12/2006', 14534, 'Invoice', 	168.00)
, ('A6UJ9A000A0O', '07/01/2008', 15715, 'Invoice', 	204.00)
, ('A6UJ9A000A0O', '08/01/2009', 17033, 'Invoice', 	204.00)
, ('A6UJ9A000A0O', '04/01/2010', 18403, 'Invoice', 	204.00)
, ('A6UJ9A000A0O', '04/01/2011', 19868, 'Invoice', 	252.00)
, ('A6UJ9A000A0O', '04/01/2012', 21801, 'Invoice', 	204.00)
, ('A6UJ9A000A0P', '20/02/2006', 13776, 'Invoice', 	426.14)
, ('A6UJ9A000A0P', '17/01/2007', 14679, 'Invoice', 	426.14)
, ('A6UJ9A000A0P', '14/02/2008', 15889, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '16/02/2009', 17196, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '02/02/2010', 18546, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '01/02/2011', 20020, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '01/02/2012', 22028, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '01/02/2013', 24843, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '03/02/2014', 27886, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '02/02/2015', 32001, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '01/02/2016', 37937, 'Invoice', 	479.40)
, ('A6UJ9A000A0P', '01/02/2017', 45042, 'Invoice', 479.40);

Open in new window

thanks Expert Commentby:Mike Eghtebas


this looks exact what I need so now I just need to understand the update part of the query above
the aim as you can see is to allocate the details of which invoice_number / rowID replaced the rowid being looked at.
so I need to fill in the rowid and accountorder as updates to get the order and then use the acctid and orderid to do the update for the renewedby

my thought was to add an extra column say COMPAREID to the table which was an Int  and  update it with
Update Renewals set COMPAREID =OrderID-1 and then use the AcctID+Compareid combination to do the update of the date.
You can see I'm a beginner !!!! however you probably will be able to show me the proper way for the updates of the data

thanks

TOP 1000 [RowID]
      ,[AcctID]
      ,[INVOICE_DATE]
      ,[INVOICE_NUMBER]
      ,[Type]
      ,[STG_VALUE]
      ,[AccountOrder]
      ,[Renewedon]
      ,[RenewedValue]
      ,[Renewedby]
      ,[Status]
      ,[CompareNo]
  FROM [Renewals]
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Expert Commentby:Sharath


with the above query I have achieved the first part now I need to add the Renewby column into the table
so in the data below rowid 1 renewedby = 2, rowid 2 renewedby =3, rowid 6 renewedby = NULL as its the last one for the acctID selected
then we change acctID so in my data below rowid 3828 renewedby=3829, rowid 3829 renewedby = 3830 until last one in sequence ir rowid 3834 renewedby = NULL

so what would the Update statement be to achieve this. The data has already been ordered by AcctID + RowId  (or Acctid +AccountOrder


RowID      AcctID      INVOICE_DATE      INVOICE_NUMBER      Type      STG_VALUE      AccountOrder      Renewedby
1      A6UJ9A0006D3      2009-04-14 00:00:00      17396      Invoice      583.33      1      NULL
2      A6UJ9A0006D3      2009-04-14 00:00:00      17400      Invoice      583.33      2      NULL
3      A6UJ9A0006D3      2009-09-07 00:00:00      1357      Credit Note      -583.33      3      NULL
4      A6UJ9A0006D3      2010-02-02 00:00:00      18541      Invoice      116.67      4      NULL
5      A6UJ9A0006D3      2011-02-01 00:00:00      20059      Invoice      116.67      5      NULL
6      A6UJ9A0006D3      2012-02-02 00:00:00      22071      Invoice      116.67      6      NULL
3828      A6UJ9A0006T8      2010-06-30 00:00:00      19087      Invoice      420.00      1      NULL
3829      A6UJ9A0006T8      2011-05-04 00:00:00      20472      Invoice      84.00      2      NULL
3830      A6UJ9A0006T8      2012-05-03 00:00:00      22638      Invoice      42.00      3      NULL
3831      A6UJ9A0006T8      2013-05-01 00:00:00      25586      Invoice      42.00      4      NULL
3832      A6UJ9A0006T8      2014-05-01 00:00:00      28768      Invoice      42.00      5      NULL
3833      A6UJ9A0006T8      2015-05-01 00:00:00      33472      Invoice      42.00      6      NULL
3834      A6UJ9A0006T8      2016-05-04 00:00:00      39647      Invoice      42.00      7      NULL
How to I change this to get the right result in LAG_ROWID see what I got and what i expected
Note the last in the series always has to be NULL too !

   SELECT ROWID, AcctID
, ROW_NUMBER() OVER ( PARTITION BY AcctID ORDER BY  AccountOrder ) AccountOrder
, LAG(ROWID) OVER ( PARTITION BY AcctID ORDER BY AccountOrder) LAG_ROWID
 FROM [OrbisSales].[dbo].[Renewals]
 where acctid in ('A6UJ9A0006D3','A6UJ9A0006D4')
  order by rowid

this is the result I got but is the wrong order

ROWID      AcctID      AccountOrder      LAG_ROWID      I expected Lag_rowid to return
1      A6UJ9A0006D3      1      NULL                                              2
2      A6UJ9A0006D3      2      1                                                     3  
3      A6UJ9A0006D3      3      2                                                     4
4      A6UJ9A0006D3      4      3                                                    NULL
7      A6UJ9A0006D4      1      NULL                                              8
8      A6UJ9A0006D4      2      7                                                      9    
9      A6UJ9A0006D4      3      8                                                    NULL
select
    r.*
  , RenewedBy=p.RowId
from [dbo].[Renewals] as r
  left join [dbo].[Renewals] as p
    on p.AcctID = r.AcctID
   and p.AccountOrder = r.AccountOrder+1
    order by rowid

this achieved what I was looking at
thanks this sorts the data into the order I need - there is a related question to cover the update version of this

   SELECT ROWID, AcctID
, ROW_NUMBER() OVER ( PARTITION BY AcctID ORDER BY  AccountOrder DESc) AccountOrder
, LAG(ROWID) OVER ( PARTITION BY AcctID ORDER BY AccountOrder DESC) LAG_ROWID
 FROM [OrbisSales].[dbo].[Renewals]
 where acctid in ('A6UJ9A0006D3','A6UJ9A0006EV')
  order by rowid