Compare data within table

Transact-SQL
1

Good afternoon, all.

I'll start with giving you an example of a table I'm working with:

NUM	        NUM2	YR
987654321	L77796	2004
987654321	L77796	2005
987654321	NULL	2006
987555433	L22284	2004
987555433	L22284	2005
987555433	NULL	2006
217864444	T22244	2007
217864444	T22244	2008
217864444	NULL	2012

I'm drawing a total blank here -- how would I go about finding all the NUM's whose NUM2 value for the latest YR does not equal the NUM2 value for the year preceding?

What I'm trying to accomplish to to get the NUM2 to be the same for each NUM across all YR, but I'd like to see how many will be affected first by running a SELECT.

Sorry if this is clear as mud!.

2

In your example, that is true for all three NUM's, isn't it? In each case, the latest year has a NULL for NUM2. Also, for NUM 217864444 in one case, the years are not consecutive. Do you want to consider only consecutive years?

What is the output you are expecting for your sample data?

3

The years need not be consecutive, just as long as the latest year listed and the last one before it both have the same NUM2.

Expected output would be:

NUM	        NUM2	YR
987654321	L77796	2004
987654321	L77796	2005
987654321	L77796  2006
987555433	L22284	2004
987555433	L22284	2005
987555433	L22284	2006
217864444	T22244	2007
217864444	T22244	2008
217864444	T22244	2012
4

If you run the following query, it will give you the previous NUM2 along with the other columns.

select *,
	LAG(NUM2) OVER (Partition by NUM ORDER BY YR) PrevNUM2
from
	Tbl

From there, what you want to do depends on what your actual data (not the sample) is. For the sample, if you do the followong, you will get the result you are looking for.
select NUM, COALESCE(NUM2,PrevNUM2) as NUM2, YR from ( select *, LAG(NUM2) OVER (Partition by NUM ORDER BY YR) PrevNUM2 from Tbl )s

But that may not necessarily give you the right answers in your larger data set. Here I am just replacing the NULL value with the PrevNum2. If there is a year that is not the latest year, but has a different NUM2 when compared to the year prior to that, this will leave that as it is. Perhaps that is not what you want?

5

What version of SQL Server are you using? 2012 or something earlier? Also, how did you want to process NULLs? Do they match the previous row or not?

6

Well, it would actually be GREAT if this was possible!

In other news, I'm on SQL Server 2008, so I don't think LAG works. :confused:

7

You are right - LAG is available only on SQL 2012 or later. The following should work on SQL 2008.

select
	a.NUM,
	coalesce(a.NUM2, b.PrevNUM2) as NUM2,
	YR
from
	Tbl a
	outer apply
	(
		select top (1) NUM2 as PrevNUM2
		from Tbl b
		where b.NUM = a.NUM
		and b.YR < a.YR
		order by b.YR DESC
	) b
1 Like