September 16, 2014 at 7:45 am
Hi bros,
I need to compare the next row with the previous row of same table and produce nonidentical column.
for eg... say
mytable has
Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180
then my output would be as below:
StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150
i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.
Plz suggest me...
Thanks
Abhas.
September 16, 2014 at 8:14 am
abhas (9/16/2014)
Hi bros,I need to compare the next row with the previous row of same table and produce nonidentical column.
for eg... say
mytable has
Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180
then my output would be as below:
StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150
i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.
Plz suggest me...
Thanks
Abhas.
Quick question, can you post the DDL (create table) and some sample data in consumable format?
😎
September 16, 2014 at 8:40 am
abhas (9/16/2014)
Hi bros,I need to compare the next row with the previous row of same table and produce nonidentical column.
for eg... say
mytable has
Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180
then my output would be as below:
StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150
i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.
Plz suggest me...
Thanks
Abhas.
The concept of "previous row" in SQL server is invalid if you're relying on perceived natural order. There has to be a column that contains a date, IDENTITY, or something that identifies the order of the rows.
If you'd like a code answer, please help us help you and read the article at the first link under helpful links in my signature line below. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2014 at 12:44 am
Thank you Jeff and Eirikur.
See below object.
create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)
insert into #temp values ( 1001, 'Abhas', 120, 150, 180)
insert into #temp values (1002, 'John', 150, 150, 180)
insert into #temp values (1001, 'Abhas', 150, 150, 180)
select * from #temp
Now, in above case for CanNum Attempt1 column has changed, So in my output it will display CanNUm and Attempt1 as a first row and all column for new record. i.e. i want to compare each column and display changed columns.
Thanks.
Abhas.
September 18, 2014 at 1:41 am
Quick self-join type solution
😎
create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)
insert into #temp values ( 1001, 'Abhas', 120, 150, 180)
insert into #temp values (1002, 'John', 150, 150, 180)
insert into #temp values (1001, 'Abhas', 150, 150, 180);
insert into #temp values (1002, 'John', 150, 150, 190)
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY T.CanNum
ORDER BY T.candId
) AS CAN_RID
,T.candId
,T.CanNum
,T.name
,T.Attempt1
,T.Attempt2
,T.attempt3
from #temp T
)
SELECT
BD.CAN_RID
,BD.candId
,BD.CanNum
,BD.name
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt1 IS NULL THEN BD.Attempt1
WHEN BD.CAN_RID = 1 AND BD.Attempt1 <> B2.Attempt1 THEN BD.Attempt1
WHEN BD.CAN_RID > 1 THEN BD.Attempt1
ELSE NULL
END AS Attempt1
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt2 IS NULL THEN BD.Attempt2
WHEN BD.CAN_RID = 1 AND BD.Attempt2 <> B2.Attempt2 THEN BD.Attempt2
WHEN BD.CAN_RID > 1 THEN BD.Attempt2
ELSE NULL
END AS Attempt2
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt3 IS NULL THEN BD.Attempt3
WHEN BD.CAN_RID = 1 AND BD.Attempt3 <> B2.Attempt3 THEN BD.Attempt3
WHEN BD.CAN_RID > 1 THEN BD.Attempt3
ELSE NULL
END AS Attempt3
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.CanNum = B2.CanNum
AND BD.CAN_RID = B2.CAN_RID - 1
DROP TABLE #temp;
Results
CAN_RID candId CanNum name Attempt1 Attempt2 Attempt3
-------- ------- ------- ------ ----------- ----------- -----------
1 1 1001 Abhas 120 NULL NULL
2 3 1001 Abhas 150 150 180
1 2 1002 John NULL NULL 180
2 4 1002 John 150 150 190
September 18, 2014 at 1:56 am
Thanks Eirikur.
Working fine. 🙂
Thank you so much!!!!!!!
Thanks
Abhas.
September 18, 2014 at 2:08 am
You're welcome;-)
Quick question, would you ever have more than two rows for a subject (CanNum)?
😎
September 18, 2014 at 5:53 am
Yes Erirukar,
There are more rows for CanNum as below: and in such case its failing. I want to compare only latest two.
I can Add one more column into table definition as dateadded, no probs..
create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)
insert into #temp values ( 1001, 'Abhas', 120, 150, 180)
insert into #temp values (1002, 'John', 150, 150, 180)
insert into #temp values (1001, 'Abhas', 150, 150, 180);
insert into #temp values (1002, 'John', 150, 150, 190)
insert into #temp values (1001, 'Abhas', 150, 150, 125);
insert into #temp values (1001, 'Abhas', 50, 50, 125);
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY T.CanNum
ORDER BY T.candId
) AS CAN_RID
,T.candId
,T.CanNum
,T.name
,T.Attempt1
,T.Attempt2
,T.attempt3
from #temp T
)
SELECT
BD.CAN_RID
,BD.candId
,BD.CanNum
,BD.name
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt1 IS NULL THEN BD.Attempt1
WHEN BD.CAN_RID = 1 AND BD.Attempt1 <> B2.Attempt1 THEN BD.Attempt1
WHEN BD.CAN_RID > 1 THEN BD.Attempt1
ELSE NULL
END AS Attempt1
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt2 IS NULL THEN BD.Attempt2
WHEN BD.CAN_RID = 1 AND BD.Attempt2 <> B2.Attempt2 THEN BD.Attempt2
WHEN BD.CAN_RID > 1 THEN BD.Attempt2
ELSE NULL
END AS Attempt2
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt3 IS NULL THEN BD.Attempt3
WHEN BD.CAN_RID = 1 AND BD.Attempt3 <> B2.Attempt3 THEN BD.Attempt3
WHEN BD.CAN_RID > 1 THEN BD.Attempt3
ELSE NULL
END AS Attempt3
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.CanNum = B2.CanNum
AND BD.CAN_RID = B2.CAN_RID - 1
DROP TABLE #temp;
Thanks,
Abhas.
September 20, 2014 at 12:30 am
I thought that might be the case;-)
Here is an adjustment to the code, picks the two last entries for each subject
😎
create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)
insert into #temp values (1001, 'Abhas', 120, 150, 180) ;
insert into #temp values (1002, 'John', 150, 150, 180) ;
insert into #temp values (1001, 'Abhas', 150, 150, 180) ;
insert into #temp values (1002, 'John', 150, 150, 190) ;
insert into #temp values (1001, 'Abhas', 150, 150, 125) ;
insert into #temp values (1001, 'Abhas', 50, 50, 125) ;
;WITH BASE_DATA AS
(
SELECT
2 + ROW_NUMBER() OVER
(
PARTITION BY T.CanNum
ORDER BY T.candId
) --AS CAN_RID
- COUNT(T.candId) OVER
(
PARTITION BY T.CanNum
) AS CAN_RID
,T.candId
,T.CanNum
,T.name
,T.Attempt1
,T.Attempt2
,T.attempt3
from #temp T
)
SELECT
BD.CAN_RID
,BD.candId
,BD.CanNum
,BD.name
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt1 IS NULL THEN BD.Attempt1
WHEN BD.CAN_RID = 1 AND BD.Attempt1 <> B2.Attempt1 THEN BD.Attempt1
WHEN BD.CAN_RID > 1 THEN BD.Attempt1
ELSE NULL
END AS Attempt1
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt2 IS NULL THEN BD.Attempt2
WHEN BD.CAN_RID = 1 AND BD.Attempt2 <> B2.Attempt2 THEN BD.Attempt2
WHEN BD.CAN_RID > 1 THEN BD.Attempt2
ELSE NULL
END AS Attempt2
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt3 IS NULL THEN BD.Attempt3
WHEN BD.CAN_RID = 1 AND BD.Attempt3 <> B2.Attempt3 THEN BD.Attempt3
WHEN BD.CAN_RID > 1 THEN BD.Attempt3
ELSE NULL
END AS Attempt3
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.CanNum = B2.CanNum
AND BD.CAN_RID = B2.CAN_RID - 1
WHERE BD.CAN_RID > 0;
DROP TABLE #temp;
Results
CAN_RID candId CanNum name Attempt1 Attempt2 Attempt3
-------- ------- ------- ------ ----------- ----------- -----------
1 5 1001 Abhas 150 150 NULL
2 6 1001 Abhas 50 50 125
1 2 1002 John NULL NULL 180
2 4 1002 John 150 150 190
September 22, 2014 at 1:10 am
abhas (9/18/2014)
Yes Erirukar,There are more rows for CanNum as below: and in such case its failing. I want to compare only latest two.
I can Add one more column into table definition as dateadded, no probs..
I guess you'd better really add some business-related date column. Consider deleting some rows by mistake and then adding this info again.
September 22, 2014 at 1:29 am
serg-52 (9/22/2014)
abhas (9/18/2014)
Yes Erirukar,There are more rows for CanNum as below: and in such case its failing. I want to compare only latest two.
I can Add one more column into table definition as dateadded, no probs..
I guess you'd better really add some business-related date column. Consider deleting some rows by mistake and then adding this info again.
When using an identity property to maintain the sequence, re-inserting would simply imply an identity insert. On the other hand, temporal sequence would be both more robust and more flexible.
😎
September 22, 2014 at 2:51 am
Eirikur Eiriksson (9/22/2014)
When using an identity property to maintain the sequence, re-inserting would simply imply an identity insert. On the other hand, temporal sequence would be both more robust and more flexible.
😎
Yes, in some simple cases it helps. But consider inserting one more row for some business reason. Sure you've met other cases where one can't rely on identity for the purpose.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply