/* first create table and put in some data */
IF OBJECT_ID('dbo.yseq') IS NOT NULL
DROP TABLE dbo.yseq;
GO
CREATE TABLE dbo.yseq
(
yrnum INT NOT NULL PRIMARY KEY,
label VARCHAR(10) NOT NULL
);
GO
INSERT INTO dbo.yseq(yrnum, label)
VALUES(1995, 'Rep-1995');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(1996, 'Rep-1996');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(1997, 'Rep-1997');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(1999, 'Rep-1999');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(2000, 'Rep-2000');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(2002, 'Rep-2002');
INSERT INTO dbo.yseq(yrnum, label)
VALUES(2003, 'Rep-2003');
GO
/* Left Outer Join helps to find Missing Years in the sequence. The join is
made between two instances of years sequence table (self join) */
SELECT b.yrnum AS real_year,
(a.yrnum + 1) AS virtual_year, b.label
FROM dbo.yseq AS a
LEFT JOIN dbo.yseq AS b
ON b.yrnum = a.yrnum + 1
WHERE (a.yrnum + 1) <= 2003;
/* This technique is efficient when only single numbers are missing from the
sequence. If we'll have to find missing ranges of numbers (gaps), then the
Left Join will have to be made against additional table representing true
sequence without gaps. */
|