Best Practice SQL for validating two sets of data ?

[EVANS310711]

Hi all,

I'm looking to validate some external monthly invoices against a customer portfolio and need some advice on the best practice SQL to use to output various scenarios.

My customer portfolio looks like this:

CUSTOMER PRODUCT PRODUCT START DATE PRODUCT END DATE
1 A 01/01/2015 NULL
2 A 01/01/2015 31/03/2015
2 B 01/04/2015 30/06/2015
2 C 01/07/2015 30/09/2015
3 F 01/01/2015 31/03/2015
3 H 01/07/2015 NULL

* Notice a gap in dates for Customer 3 where that customer briefly switched to another company before rejoining

The external monthly invoices we receive look like this:

CUSTOMER PRODUCT INVOICE START DATE INVOICE END DATE
1 A 01/11/2015 30/11/2015
2 C 01/11/2015 30/11/2015
3 G 01/11/2015 30/11/2015
4 A 01/11/2015 30/11/2015

The scenarios I need to output are as follows:

1) Invoiced for a customer we have never supplied (e.g. customer 4 on invoice but not in portfolio)
2) Invoiced for a customer we have supplied but ceased to supply prior to the invoice date (e.g. customer 2 invoiced after we stopped supplying on 30/09/2015)
3) Invoiced for a customer we have supplied during that period but for a different product (e.g. customer 3 invoiced for product G but we only ever supplied products F and H)

Would it be best to use a CASE statement in this scenario ?

Thanks