Show records which dont match the date year sequence

Question

how records which dont match the date year sequence I am trying to return all customer records who have NOT ordered every year without fail.

Eg,

cust1 - 2010
cust1 - 2012
cust1 - 2013
cust1 - 2014
cust2 - 2014

As you can see cust1 has not ordered in the year 2011

I would like to return the row that shows cust1.

I need to be able to specify a sequence of dates, eg min and max.

I have tried this:

DECLARE @MINDATE AS INT;
SELECT @MINDATE = '2010';

DECLARE @MAXDATE AS INT;
SELECT @MAXDATE = '2014';

SELECT CUSTID, YEAR(DATE) AS [YEAR]
FROM ORDER
WHERE DATE BETWEEN @MINDATE AND @MAXDATE;

I strongly disagree that this question is a duplicate of the one that was referenced. I would think that you would have to be pretty proficient in SQL in order to apply those answers to this problem. — Gordon Linoff, Jan 05 '15 at 15:11

Andrey Morozov · Answer 1 · 2015-01-12T17:27:45.910

You can specify sequence like this:

DECLARE
    @MINYEAR AS INT = '2010',
    @MAXYEAR AS INT = '2014';

with base as
(
    select @MINYEAR [year]
    union all
    select [year] + 1
    from base
    where [year] < @MAXYEAR
)
select * from [base]

Result:

year
----
2010
2011
2012
2013
2014

Ant then JOIN it with your table, for example like this:

DECLARE
    @MINYEAR AS INT = '2010',
    @MAXYEAR AS INT = '2014';

with base as
(
    select @MINYEAR [year]
    union all
    select [year] + 1
    from base
    where [year] < @MAXYEAR
)
select y.[year], isnull(b.CUSTID, 'No data') [CUSTID]
from base y
left join ORDER b on YEAR(b.DATE) = y.[year]

I don't know how to achieve the desired output because you did not provide any schema of your data

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

SELECT      #sequence.value
FROM        #sequence
LEFT JOIN   MyTable ON #sequence.value = MyTable.value
WHERE       MyTable.value IS NULL

taken from answer

edited May 23 '17 at 12:20

Community

1
1

answered Jan 05 '15 at 14:33

Woot4Moo

23,987
16
94
151

I think it's a bit more complicated than that because the goal is to find all customers who haven't ordered every year. So you would want a temp table of all the years in sequence `CROSS JOIN`ed to a temp(?) table of all customers. Then you could do a `LEFT JOIN` to the actual orders. – David Faber Jan 05 '15 at 14:35
@DavidFaber hmm perhaps, I think I misread the initial question by OP – Woot4Moo Jan 05 '15 at 14:38

score 0 · Answer 3 · edited Jan 05 '15 at 15:32

I would tend to do this with count(distinct):

SELECT CUSTID
FROM ORDER
WHERE DATE BETWEEN @MINDATE AND @MAXDATE
GROUP BY CUSTID
HAVING COUNT(DISTINCT YEAR(DATE)) = (SELECT COUNT(DISTINCT YEAR(DATE))
                                              FROM ORDER
                                              WHERE DATE BETWEEN @MINDATE AND @MAXDATE
                                             );

EDIT: This assumes that all years are represented in the data.

score 0 · Answer 4 · answered Jan 05 '15 at 16:45

Try this, it reports Customers who do not have orders in all years.

It compares the number of distinct years for a customer against the number of years that there should be based upon the difference between the MaxDate and MinDate.

SELECT CUSTID
FROM ORDER
WHERE DATE BETWEEN @MINDATE AND @MAXDATE
GROUP BY CUSTID
HAVING COUNT(DISTINCT YEAR(DATE)) <> (YEAR(@MAXDATE) - YEAR(@MINDATE)) + 1

Show records which dont match the date year sequence

4 Answers4