SQL Server 2012 - find duplicate month (string) but different year

Question

Having difficulty getting my head around this one.

I've been asked to create a report showing customers who signed up in the same month in previous year.

Invoice table looks a bit like this: (can't figure out how to create a nicer table)

invoiceid  customerid  monthinvoice  yearinvoice      
1          50          July           2016*  
2          51          July           2016  
3          52          July           2016*
4          53          July           2016
5          54          August         2016
6          50          July           2017*
7          51          August         2017
8          52          July           2017*
9          53          August         2017
10         54          September      2017

The only proper date column used is date the invoice was generated and the date payment received.

The records marked with * are the ones I'm only interested in, I just want to see 2 records returned when I pass a month as a parameter (I'll be asked to show how many customers have renewed in August for example. If the 1st invoice was in July 2016 and next invoice in August 2017 they will be treated as a new customer, not a renewal (must be exactly 12 months)) 1) 50 2) 52

Any help much appreciated.

Answer 1

Here is one way. First we get all invoices for this month, current year, then union to the same month of the previous year. Then, we filter on customers who have a record for both using HAVING.

;with cte as(
select * 
from yourtable
where 
    (monthinvoice = @monthinvoice
    and yearinvoice = datepart(year,getdate()))
union 
select * 
from yourtable
where 
    (monthinvoice = @monthinvoice
    and yearinvoice = datepart(year,dateadd(year,-1,getdate()))))

select *
from cte
where customerid in (select customerid from cte group by customerid having count(invoiceid) > 1)

Answer 2

I think this should do the trick for you-

SELECT I1.invoiceid, I1.customerid, I1.monthinvoice, I1.yearinvoice, I2.yearinvoice
FROM Invoice_table I1
INNER JOIN Invoice table I2
ON I1.customerid = I2.customerid
AND I1.monthinvoice = I2.monthinvoice
AND I1.yearinvoice = I2.yearinvoice + 1

Answer 3

something like this

select customerid , monthinvoice from yourtable
where yearinvoice in (2016, 2017) and monthinvoice = 'July'
group by customerid , monthinvoice
having count(*) = 2

Answer 4

Something like the following should give you some ideas as to how to build the report out.

Declare @ReportYear as int = 2017;
--this should show all customers with invioices for these months in both 2017 and 2016
select a.customerid, a.monthinvoice
from
(
    --get people with invoice last year
    Select distinct customerid, monthinvoice
    from Invoices i0
    where yearinvoice = @ReportYear - 1
)   a
join
(
    --get people with invoice this year
    Select distinct customerid, monthinvoice
    from Invoices i0
    where yearinvoice = @ReportYear
)   b   on  a.customerid = b.customerid
        and a.monthinvoice = b.monthinvoice

Answer 5

If Im following your question correctly...

SELECT customerid FROM InvTblName T
   INNER JOIN (SELECT customerID
           FROM InvTblName     
           HAVING Z.invyear=T.invyear+1) Z
       ON T.invmonth=Z.invmonth