CODEWITHSUNDEEP
sql-server
Marc
Marc

Reputation: 9567

How to get the previous row value with an ORDERING BY DATE?

When I run the here below code I get this error :

Msg 206, Level 16, State 2, Line 14 Operand type clash: int is incompatible with date

Here below my code for which I get the here above error:

declare @tbl table (
cat1 nvarchar(500),
cat2 nvarchar(500),
someDate date
);

insert into @tbl values
('A','B','2016-01-01'),
('A','B','2016-01-02'),
('A','B','2016-01-03'),
('A','B','2016-01-04'),
('A','B','2016-01-05');

select 
cat1,
cat2,
someDate,
LAG(someDate,1,0) OVER (PARTITION BY cat1, cat2 ORDER BY someDate asc) as PreviousSomeDate
from @tbl;

And here below the expected result (the previous row value of [someDate]) :

('A','B','2016-01-01',''),
('A','B','2016-01-02','2016-01-01'),
('A','B','2016-01-03','2016-01-02'),
('A','B','2016-01-04','2016-01-03'),
('A','B','2016-01-05','2016-01-04');

Upvotes: 1

Views: 148

Answers (1)

user330315
user330315

Reputation:

LAG(someDate,1,0) means: return the previous value of the column someDate and if there is no "previous" column (because it's the first row) return the value 0.

If that was possible, you would have a integer value in that column for the first row and a date value for all other rows. All values of one column must have the same data type, so SQL Server complains, that "int is incompatible with date"

You need to use a date as the default value (the third parameter). You apparently don't want any value, so just use lag(someDate) which will return NULL for the first row.

Upvotes: 2

Related Questions