June 29, 2013 at 3:10 am
declare @t table
(
id int,
PayCode char(2) null,
Amount decimal(15,2) null,
CDate date,
TranSeq int null
)
declare @C int,@max int
set @C = 1
insert into @t
select 1, 'IR' , 1000.00 ,null, null union
select 2, 'IP' , 300.00 ,null, null union
select 3, 'IP' , 400.00 ,null,null union
select 4, 'IR' , -1000.00 ,'2013-02-02', null union
select 5, 'IR' , 200.00 ,null, null union
select 6, 'IP' , 100.00 ,null, null union
select 7, 'IR' , 200.00 ,null, null union
select 8, 'IP' , 400.00 ,null, null
select * from @t
Required Output is below
idPayCodeAmountCDateTranSeq
1IR1000.00NULL1
2IP300.00NULL2
3IP400.00NULL3
4IR-100.0020130202 1
5IR200.00NULL1
6IP100.00NULL2
7IR200.00NULL1
8IP400.00NULL2
For every IR sequence will start and get increment with coming IP but when next IR comes in , new sequence will start.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2013 at 10:36 am
Need help ..please .......
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2013 at 6:08 pm
First, we have to somehow group together rows that belong to the same group. So we will add a new column, GroupId, that has ID of the first preceding IR row:
select *,
GroupId = (SELECT TOP 1 tt.id FROM @t tt WHERE tt.id<=t.id and tt.PayCode='IR' ORDER BY tt.id desc)
from @t t
Result:
idPayCodeAmountCDateTranSeqGroupId
1IR1000.00NULLNULL1
2IP300.00NULLNULL1
3IP400.00NULLNULL1
4IR-1000.002013-02-02NULL4
5IR200.00NULLNULL5
6IP100.00NULLNULL5
7IR200.00NULLNULL7
8IP400.00NULLNULL7
Next step is trivial, assign a sequence number using ROW_NUMBER function, partitioned by that GroupId.
I used outer apply instead of inline view to make it a bit shorter:
select t.*,
Rnr = ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY id)
from @t t
outer apply (SELECT TOP 1 GroupId = tt.id FROM @t tt WHERE tt.id<=t.id and tt.PayCode='IR' ORDER BY tt.id desc) g
Result:
idPayCodeAmountCDateTranSeqRnr
1IR1000.00NULLNULL1
2IP300.00NULLNULL2
3IP400.00NULLNULL3
4IR-1000.002013-02-02NULL1
5IR200.00NULLNULL1
6IP100.00NULLNULL2
7IR200.00NULLNULL1
8IP400.00NULLNULL2
HTH
June 30, 2013 at 8:47 pm
Perhaps you'd rather use a Quirky Update (QU)?
declare @t table
(
id int PRIMARY KEY,
PayCode char(2) null,
Amount decimal(15,2) null,
CDate date,
TranSeq int null
)
insert into @t
select 1, 'IR' , 1000.00 ,null, null union
select 2, 'IP' , 300.00 ,null, null union
select 3, 'IP' , 400.00 ,null,null union
select 4, 'IR' , -1000.00 ,'2013-02-02', null union
select 5, 'IR' , 200.00 ,null, null union
select 6, 'IP' , 100.00 ,null, null union
select 7, 'IR' , 200.00 ,null, null union
select 8, 'IP' , 400.00 ,null, null
DECLARE @TranSeq INT = 1
UPDATE @t
SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END
,TranSeq = @TranSeq
select * from @t
Note that I added a clustered index (PRIMARY KEY) to your table.
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
July 1, 2013 at 2:04 am
here was one of the way
CREATE table #temptable
(
id int,
PayCode char(2) null,
Amount decimal(15,2) null,
CDate date,
TranSeq int null
)
declare @C int,@max int
set @C = 1
insert into #temptable
select 1, 'IR' , 1000.00 ,null, null union
select 2, 'IP' , 300.00 ,null, null union
select 3, 'IP' , 400.00 ,null,null union
select 4, 'IR' , -1000.00 ,'2013-02-02', null union
select 5, 'IR' , 200.00 ,null, null union
select 6, 'IP' , 100.00 ,null, null union
select 7, 'IR' , 200.00 ,null, null union
select 8, 'IP' , 400.00 ,null, null
WITH CTE
AS
(
SELECT id,PayCode,Amount,CDate,TranSeq,
GRP = CASE PayCode WHEN 'IR'
THEN ROW_NUMBER () OVER(PARTITION BY PayCode ORDER BY ID)
ELSE ID - ROW_NUMBER () OVER(PARTITION BY PayCode ORDER BY ID )
END
from #temptable
)
SELECT id,PayCode,Amount,CDate,TranSeq,
ROW_NUMBER () OVER(PARTITION BY GRP ORDER BY ID)
FROM CTE
July 1, 2013 at 3:02 am
Thanks a ton for help ..to all guys here 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 1, 2013 at 7:20 pm
dwain.c (6/30/2013)
Perhaps you'd rather use a Quirky Update (QU)?
declare @t table
(
id int PRIMARY KEY,
PayCode char(2) null,
Amount decimal(15,2) null,
CDate date,
TranSeq int null
)
insert into @t
select 1, 'IR' , 1000.00 ,null, null union
select 2, 'IP' , 300.00 ,null, null union
select 3, 'IP' , 400.00 ,null,null union
select 4, 'IR' , -1000.00 ,'2013-02-02', null union
select 5, 'IR' , 200.00 ,null, null union
select 6, 'IP' , 100.00 ,null, null union
select 7, 'IR' , 200.00 ,null, null union
select 8, 'IP' , 400.00 ,null, null
DECLARE @TranSeq INT = 1
UPDATE @t
SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END
,TranSeq = @TranSeq
select * from @t
Note that I added a clustered index (PRIMARY KEY) to your table.
You're missing several of the rules to make it safe Dwain.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2013 at 7:24 pm
Jeff Moden (7/1/2013)
dwain.c (6/30/2013)
Perhaps you'd rather use a Quirky Update (QU)?
declare @t table
(
id int PRIMARY KEY,
PayCode char(2) null,
Amount decimal(15,2) null,
CDate date,
TranSeq int null
)
insert into @t
select 1, 'IR' , 1000.00 ,null, null union
select 2, 'IP' , 300.00 ,null, null union
select 3, 'IP' , 400.00 ,null,null union
select 4, 'IR' , -1000.00 ,'2013-02-02', null union
select 5, 'IR' , 200.00 ,null, null union
select 6, 'IP' , 100.00 ,null, null union
select 7, 'IR' , 200.00 ,null, null union
select 8, 'IP' , 400.00 ,null, null
DECLARE @TranSeq INT = 1
UPDATE @t
SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END
,TranSeq = @TranSeq
select * from @t
Note that I added a clustered index (PRIMARY KEY) to your table.
You're missing several of the rules to make it safe Dwain.
Indeed I omitted them but not because I'm out of practice or nothing. I was hoping the OP would ask!
But since you mentioned it, here's a link to your definitive treatise on the subject: http://www.sqlservercentral.com/articles/T-SQL/68467/
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply