I am trying to write the query and is working fine with the one card_nr. Example:
Declare @card_nr as nvarchar(50)
set @card_nr = '704487442952000472'
select
a.times, b.times
from
(select
stat_id, times
from info
where card_nr = @card_nr) as a
left outer join
(select
stat_id, times
from info
where card_nr = @card_nr) as b on a.stat_id = b.stat_id+1
And I got the correct result:
2014-02-04 11:20:00.000 NULL
2014-02-06 09:44:00.000 2014-02-04 11:20:00.000
2014-02-12 09:59:00.000 2014-02-06 09:44:00.000
2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
2014-02-21 09:49:00.000 2014-02-13 10:31:00.000
I want to do it for every card_nr
from a table list. So, first I have written the function:
alter FUNCTION tabletest
( @card_nr as nvarchar(50))
RETURNS smalldatetime
AS BEGIN
Declare @sqldata as smalldatetime
select
@sqldata = b.times
from
(select
stat_id, times
from info where card_nr = @card_nr) as a
left outer join
(select
stat_id, times
from info where card_nr = @card_nr ) as b on a.stat_id = b.stat_id + 1
RETURN @datas
END
After the function I have tried select my query:
select name, times, dbo.tabletest(card_nr)
from dbo.info
And I got incorrect result:
User1 2014-02-04 11:20:00.000 2014-02-21 09:49:00
User1 2014-02-06 09:44:00.000 2014-02-21 09:49:00
User1 2014-02-12 09:59:00.000 2014-02-21 09:49:00
User1 2014-02-13 10:31:00.000 2014-02-21 09:49:00
User1 2014-02-21 09:49:00.000 2014-02-21 09:49:00
User2 2014-02-14 13:41:00.000 2014-02-28 12:20:00
User2 2014-02-24 11:46:00.000 2014-02-28 12:20:00
User2 2014-02-28 12:20:00.000 2014-02-28 12:20:00
I would like to get this:
User1 2014-02-04 11:20:00.000 NULL
User1 2014-02-06 09:44:00.000 2014-02-04 11:20:00.000
User1 2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
User1 2014-02-13 10:31:00.000 2014-02-12 09:59:00.000
User1 2014-02-21 09:49:00.000 2014-02-13 10:31:00.000
User2 2014-02-14 13:41:00.000 NULL
User2 2014-02-24 11:46:00.000 2014-02-14 13:41:00.000
User2 2014-02-28 12:20:00.000 2014-02-24 11:46:00.000