April 24, 2012 at 1:10 am
Hi there,
DDL:
create table seq (
pkid int not null primary key,
seqId int null,
amount decimal (10,2) not null,
dt datetime not null)
go
Data
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(1,NULL,-1.00,'Apr 18 2012 9:13:47:000AM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(2,NULL,1.00,'Apr 18 2012 9:25:30:000AM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(3,NULL,2.00,'Apr 18 2012 2:40:58:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(4,NULL,-1.00,'Apr 18 2012 2:45:52:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(5,NULL,-1.00,'Apr 18 2012 2:45:52:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(6,NULL,-2.00,'Apr 18 2012 2:57:32:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(7,NULL,1.00,'Apr 18 2012 3:04:02:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(8,NULL,1.00,'Apr 18 2012 3:06:12:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(9,NULL,2.00,'Apr 18 2012 3:35:12:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(10,NULL,-1.00,'Apr 18 2012 3:35:36:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(11,NULL,-1.00,'Apr 18 2012 3:42:56:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(12,NULL,-2.00,'Apr 18 2012 4:35:21:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(13,NULL,1.00,'Apr 18 2012 4:46:06:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(14,NULL,1.00,'Apr 18 2012 4:46:06:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(15,NULL,-2.00,'Apr 18 2012 4:59:21:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(16,NULL,1.00,'Apr 18 2012 5:07:37:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(17,NULL,1.00,'Apr 18 2012 5:28:01:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(18,NULL,-2.00,'Apr 20 2012 2:41:42:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(19,NULL,1.00,'Apr 20 2012 2:42:03:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(20,NULL,1.00,'Apr 20 2012 2:44:51:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(21,NULL,-2.00,'Apr 20 2012 3:51:21:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(22,NULL,1.00,'Apr 20 2012 4:03:18:000PM')
INSERT INTO [seq] ([pkid],[seqId],[amount],[dt])VALUES(23,NULL,1.00,'Apr 20 2012 4:12:35:000PM')
I will have the current max (seqId) in a variable @maxSeqId int. So need to have the sequencing starting with the variable +1.
I need a T-SQL statement that updates the seqId column and has a unique int value for a series where the amount adds up to zero ordered by dt asc. So if @maxSeqID is 100 the first 5 rows would look like:
1,101,-1,'2012-04-18 09:13:47.000'
2,101,1,'2012-04-18 09:25:30.000'
3,102,2,'2012-04-18 14:40:58.000'
4,102,-1,'2012-04-18 14:45:52.000'
5,102,-1,'2012-04-18 14:45:52.000'
Hope that makes sense, thanks for looking. 🙂
April 24, 2012 at 1:40 am
Consider using IDENTITY.
April 24, 2012 at 2:06 am
something like this
declare @maxseqid int = 101,@count int, @sum decimal (10,2) = 0, @loop int = 1
select @count = COUNT(*) from seq
while @loop <= @count
begin
select @sum = @sum + amount from seq where pkid = @loop
update seq set seqId = @maxseqid where pkid = @loop
if @sum = 0
begin
set @maxseqid = @maxseqid + 1
end
set @loop = @loop + 1
end
select * from seq
April 24, 2012 at 2:20 am
anthony.green (4/24/2012)
something like this
Thanks that does provide the correct result but my hunch is that it can be solved in one statement using RANK and OVER statements. There you go, I've thrown out a challenge for a more elegant solution... 😀
April 24, 2012 at 2:37 am
April 24, 2012 at 6:00 am
keymoo (4/24/2012)
anthony.green (4/24/2012)
something like thisThanks that does provide the correct result but my hunch is that it can be solved in one statement using RANK and OVER statements. There you go, I've thrown out a challenge for a more elegant solution... 😀
As this seems like a homework question, I will attempt to show without a formal while loop. This does not strictly answer the query as uses SQL 2012 functionality (this being a SQL 2008 forum).
The code below only uses the dt column for listing rather than rely on the pkid column, just in case.
create table seq (
pkid int not null primary key,
seqId int null,
amount decimal (10,2) not null,
dt datetime not null)
go
-- add the sample data as before
go
declare @startmax int = 100
with ZeroedRows as (
select * , row_number() over (order by dt asc) + @startmax as NewGroupID
from
(select *, sum(amount) over (partition by NULL order by dt) as RunningSum
from seq) as A
with RunningSum = 0
)
update S
set GroupID = NewGroupID
from seq as S
cross apply (select min(NewGroupID) as NewGroupID
from ZeroedRows
where S.dt <= ZeroedRows.dt) as D
select * from seq
)
Using the design and test data provided this answer costs more than the previous stated answer but when an additional index is added on the dt column, then is approximately the same cost. The most costly operator in the paln is an table spool (eager spool) at 29% of the cost.
Fitz
April 24, 2012 at 7:03 pm
Homework problem or not, that's one quirky update:
DECLARE @seq TABLE (
pkid int not null primary key,
seqId int null,
amount decimal (10,2) not null,
dt datetime not null)
DECLARE @MaxSeqID INT, @total INT
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(1,NULL,-1.00,'Apr 18 2012 9:13:47:000AM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(2,NULL,1.00,'Apr 18 2012 9:25:30:000AM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(3,NULL,2.00,'Apr 18 2012 2:40:58:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(4,NULL,-1.00,'Apr 18 2012 2:45:52:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(5,NULL,-1.00,'Apr 18 2012 2:45:52:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(6,NULL,-2.00,'Apr 18 2012 2:57:32:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(7,NULL,1.00,'Apr 18 2012 3:04:02:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(8,NULL,1.00,'Apr 18 2012 3:06:12:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(9,NULL,2.00,'Apr 18 2012 3:35:12:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(10,NULL,-1.00,'Apr 18 2012 3:35:36:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(11,NULL,-1.00,'Apr 18 2012 3:42:56:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(12,NULL,-2.00,'Apr 18 2012 4:35:21:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(13,NULL,1.00,'Apr 18 2012 4:46:06:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(14,NULL,1.00,'Apr 18 2012 4:46:06:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(15,NULL,-2.00,'Apr 18 2012 4:59:21:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(16,NULL,1.00,'Apr 18 2012 5:07:37:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(17,NULL,1.00,'Apr 18 2012 5:28:01:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(18,NULL,-2.00,'Apr 20 2012 2:41:42:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(19,NULL,1.00,'Apr 20 2012 2:42:03:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(20,NULL,1.00,'Apr 20 2012 2:44:51:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(21,NULL,-2.00,'Apr 20 2012 3:51:21:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(22,NULL,1.00,'Apr 20 2012 4:03:18:000PM')
INSERT INTO @seq ([pkid],[seqId],[amount],[dt])VALUES(23,NULL,1.00,'Apr 20 2012 4:12:35:000PM')
SELECT TOP 10 * FROM @seq ORDER BY dt
SELECT @MaxSeqID = 100, @total = 0
UPDATE s
SET @MaxSeqID = seqid = @MaxSeqID + CASE @total WHEN 0 THEN 1 ELSE 0 END
,@total = @total + amount
FROM @seq s
WHERE pkid IN (SELECT TOP 100 PERCENT pkid FROM @seq ORDER BY dt)
SELECT TOP 10 * FROM @seq ORDER BY dt
Actually it's sort of a knock off of Jeff Moden's quirky update!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 1:23 am
It's not a homework question, but I knew in the back of my mind that it could be solved without using RBAR. Thanks Fitz and Dwain!
April 25, 2012 at 1:31 am
April 25, 2012 at 1:44 am
Thanks Vinus for the +1!
I'm betting (without actually testing it) that it runs with "fire breathing efficiency" to quote Jeff from the latest article he published today.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 2:21 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply