Chris Michalczuk
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
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
Do you have another field (or set of fields) to uniquely identify a row?
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
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.
Here is the result for these two. Will continue with the rest shortly.
Test Table RENEWALS from your previous posts in an Excel file:
;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
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
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);
ASKER
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]
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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].[Renewa ls]
where acctid in ('A6UJ9A0006D3','A6UJ9A000 6D4')
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
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].[Renewa
where acctid in ('A6UJ9A0006D3','A6UJ9A000
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
ASKER
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
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
ASKER
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].[Renewa ls]
where acctid in ('A6UJ9A0006D3','A6UJ9A000 6EV')
order by rowid
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].[Renewa
where acctid in ('A6UJ9A0006D3','A6UJ9A000
order by rowid