SQL. Create a query that will find non-null amounts in non-consecutive years

Question

I need to find any companies where profit is non-null in non-consecutive years (e.g. 2007 profit = 10,000.00; 2008 profit = null; 2009 profit = 12,000.00).

Here's a sample of the data:

Company year    Profit
ABW 2004    22566.68216
ABW 2005    23302.83199
ABW 2006    24015.42061
ABW 2007    25921.28214
ABW 2008    27549.34573
ABW 2009    24639.93533
ABW 2010    24289.14152
ABW 2011    25354.78247
ABW 2012    ---
AFG 2004    220.0562878
AFG 2005    252.4078925
AFG 2006    275.3509409
AFG 2007    373.5914162
AFG 2008    ----
AFG 2009    450.659239
AFG 2010    561.1976175
AFG 2011    613.9791916
AFG 2012    687.245475
AGO 2004    1229.342988
AGO 2005    1706.543616
AGO 2006    2440.631716
AGO 2007    3412.718998
AGO 2008    ----
AGO 2009    ----
AGO 2010    4218.649126
AGO 2011    5159.233666
AGO 2012    5482.428049
ALB 2004    2320.89233
ALB 2005    2620.820724
ALB 2006    2872.260625
ALB 2007    3380.894192
ALB 2008    4108.414124

Thanks for any help!

jb

Did you research it by yourself? Where did you reach so far? — ETO, Nov 15 '18 at 16:58
What query have you tried so far, and what's the problem with it? — Utrolig, Nov 15 '18 at 17:32
Yes, I've done some research on it, but haven't quite found what I need. This is the closest I've come: https://stackoverflow.com/questions/26117179/sql-count-consecutive-days I need to see what companies basically had no profit for one year, but they did for the previous year and the year after. Thanks! — John, Nov 15 '18 at 18:22

score 0 · Accepted Answer · answered Nov 15 '18 at 18:24

Is this what you're after?

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
    company CHAR(3) NOT NULL,
    [year] INT NOT NULL,
    profit DECIMAL(18,10) NULL 
    );
INSERT #TestData (company, year, profit) VALUES
    ('ABW', 2004, 22566.68216),
    ('ABW', 2005, 23302.83199),
    ('ABW', 2006, 24015.42061),
    ('ABW', 2007, 25921.28214),
    ('ABW', 2008, 27549.34573),
    ('ABW', 2009, 24639.93533),
    ('ABW', 2010, 24289.14152),
    ('ABW', 2011, 25354.78247),
    ('ABW', 2012, NULL),
    ('AFG', 2004, 220.0562878),
    ('AFG', 2005, 252.4078925),
    ('AFG', 2006, 275.3509409),
    ('AFG', 2007, 373.5914162),
    ('AFG', 2008, NULL),
    ('AFG', 2009, 450.659239 ),
    ('AFG', 2010, 561.1976175),
    ('AFG', 2011, 613.9791916),
    ('AFG', 2012, 687.245475 ),
    ('AGO', 2004, 1229.342988),
    ('AGO', 2005, 1706.543616),
    ('AGO', 2006, 2440.631716),
    ('AGO', 2007, 3412.718998),
    ('AGO', 2008, NULL),
    ('AGO', 2009, NULL),
    ('AGO', 2010, 4218.649126),
    ('AGO', 2011, 5159.233666),
    ('AGO', 2012, 5482.428049),
    ('ALB', 2004, 2320.89233 ),
    ('ALB', 2005, 2620.820724),
    ('ALB', 2006, 2872.260625),
    ('ALB', 2007, 3380.894192),
    ('ALB', 2008, 4108.414124);

--==============================================

SELECT 
    *
FROM (
    SELECT 
        td.company,
        td.year,
        td.profit,
        prev_prof_year = MAX(CASE WHEN td.profit IS NOT NULL THEN td.year END) OVER (PARTITION BY td.company ORDER BY td.year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM
        #TestData td
    ) ppy
WHERE 
    ppy.profit IS NOT NULL 
    AND ppy.year = ppy.prev_prof_year + 2;

Results:

company year        profit                                  prev_prof_year
------- ----------- --------------------------------------- --------------
AFG     2009        450.6592390000                          2007

YES!! That's VERY close! The real data has gap of two years for one company. In other words, it had a profit in 2007 and 2010, but none in 2008 and 2009. I'm playing with the logic in your last line to cover that, but you solved the meat of the problem for me. Thanks so much!! — John, Nov 15 '18 at 18:41
and just to help anyone in the future, I tweaked your last line to handle gaps of multiple years. This is the new last line "AND ppy.year > ppy.prev_prof_year + 1;" — John, Nov 15 '18 at 19:17

SQL. Create a query that will find non-null amounts in non-consecutive years

1 Answers1