Need query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need query help

Author

Topic

harjeet_21
Starting Member

2 Posts

Posted - 2010-10-13 : 13:23:29

Hi,

I need some help to write a query. Consider a table "CardHistory"

Create Table CardHistory
(
SerialNo INT IDENTITY(1,1),
ModificationDatetime DATETIME,
OldCardNo INT,
NewCardNo INT
)

Some times "OldCardNo" and "NewCardNo" can be same.

I need to find out very first CardNo, most recent (last) CardNo and ModificationsCount.

Example:
CardNo 100 changed to 101
CardNo 101 changed to 102
CardNo 102 changed to 103
CardNo 103 changed to 104

then query should return 100, 104, 4

Please help as soon as possible.

Thanks in advance ...

Sachin.Nand

2937 Posts

Posted - 2010-10-13 : 13:48:18

Will the changes be in order?
What if

CardNo 100 changed to 105
CardNo 105 changed to 102

What should be the o/p ?

PBUH

harjeet_21
Starting Member

2 Posts

Posted - 2010-10-13 : 13:58:38

100, 102, 2

Thanks,

Harjeet Singh Bhatia

Sachin.Nand

2937 Posts

Posted - 2010-10-13 : 14:12:43

[code]
Create Table CardHistory
(
SerialNo INT IDENTITY(1,1),
ModificationDatetime DATETIME,
OldCardNo INT,
NewCardNo INT
)

insert into CardHistory
select GETDATE()-3,100,107 union all
select GETDATE()-2,107,102 union all
select GETDATE()-1,102,105

select * from CardHistory

select
min(case when min=1 then OldCardNo end)old,
max(case when max=1 then NewCardNo end)new from
(
select *,row_number()over(order by modificationdatetime )min,
row_number()over(order by modificationdatetime desc)max
from CardHistory c
)t where min=1 or max=1
[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources