SQL Syntax for Date that exist in on year but not in Another

Question

I'm trying to write a query that would give me the records of all members that have Order dates that exist in 2013 but not in 2014. When I write my query it gives me ALL of the 2013 records which still exist in 2014. I'm trying to get ONLY record that have order dates in 2013 and not in 2014. So it should show records which exist in 2013 and exclude records that do not exist in 2014. Please help as I'm really new to SQL.

Thank you very much.

Below is my query:

SELECT OMFMC.BILL_MASTER_CUSTOMER_ID, OMFMC.BILL_FIRST_NAME, OMFMC.BILL_LAST_NAME, CMI.NATIONAL_LEVEL2, MIN(OMFMC.ORDER_DATE), OMFMC.ORDER_DATE, FT.PAYMENT_AMOUNT,

SUM(FT.PAYMENT_AMOUNT)as SUM

FROM CUSTOMER CUSTOMER_IN_TRIBUTE_TO

RIGHT OUTER JOIN ORDER_FND_DETAIL OFD
    ON (CUSTOMER_IN_TRIBUTE_TO.MASTER_CUSTOMER_ID=OFD.IN_TRIBUTE_TO_MAST_CUST 
    and CUSTOMER_IN_TRIBUTE_TO.SUB_CUSTOMER_ID=OFD.IN_TRIBUTE_TO_SUB_CUST)


RIGHT OUTER JOIN ORDER_MBR_FND_MTG_CUS_INFO_VW OMFMC
    ON (OMFMC.ORDER_NO=OFD.ORDER_NO 
    and OMFMC.ORDER_LINE_NO = OFD.ORDER_LINE_NO  )


LEFT OUTER JOIN CUS_CURRENT_MEMBERSHIP_INFO CMI
    ON (CMI.MASTER_CUSTOMER_ID=OMFMC.BILL_MASTER_CUSTOMER_ID 
    and CMI.SUB_CUSTOMER_ID=OMFMC.BILL_SUB_CUSTOMER_ID)  


LEFT OUTER JOIN FAR_TXN FT
    ON (FT.ORDER_NO=OMFMC.ORDER_NO 
    and FT.ORDER_LINE_NO=OMFMC.ORDER_LINE_NO)  

WHERE

OMFMC.ORDER_STATUS_CODE='A' 
AND OMFMC.LINE_STATUS_CODE = 'A'        
AND OMFMC.ORDER_STATUS_CODE = 'A' 
AND OMFMC.LINE_STATUS_CODE = 'A'   
AND OMFMC.ORDER_DATE  BETWEEN  '1/1/2013' and '12/31/2013'
AND OMFMC.ORDER_DATE  Not BETWEEN  '1/1/2014' and '12/31/2014'
AND OMFMC.BILL_CUSTOMER_CLASS_CODE  NOT IN  ( 'TEST_MBR','STAFF' ) 
AND FUND in ('FOSFN' , 'MFUND')

GROUP BY OMFMC.BILL_MASTER_CUSTOMER_ID, OMFMC.BILL_FIRST_NAME, OMFMC.BILL_LAST_NAME, OMFMC.BILL_LABEL_NAME, OMFMC.BILL_PRIMARY_EMAIL_ADDRESS, OMFMC.BILL_ADDRESS_1, OMFMC.BILL_ADDRESS_2, OMFMC.BILL_CITY, OMFMC.BILL_STATE, OMFMC.BILL_POSTAL_CODE, CMI.NATIONAL_LEVEL2, OMFMC.ORDER_DATE, FT.PAYMENT_AMOUNT

HAVING SUM(FT.PAYMENT_AMOUNT)  <  0

Order By BILL_MASTER_CUSTOMER_ID

In the order dates field, there are dates that have 2013, and 2014 dates. I want a my query to look at 2013 and 2014 order dates and ONLY pull out order dates that are in 2013. Not the records that have 2013 and 2014 dates. If they have an order in 2013 AND 2014 then I want to exclude those records. So for example:

enter code here

Bill_Member_ID..........Order_Date
123....................01/05/2013
123.................... 01/27/2013
123.................... 02/15/2014
123.................... 02/18/2014
456.................... 01/07/2013
789.................... 01/05/2013
789.................... 02/17/2014
992.................... 03/15/2013

So then I should get a return of

Bill_Member_ID..........Order_Date

456.................... 01/07/2013
992.................... 03/15/2013

Thank you so much

Answer 1

What you need is a simple subselect, and to feed its results into your outermost select to display the results.

What you are after is the list of Bill_Member_ID who have an order date between 2013-01-01 and 2013-12-31 (inclusive) but do not have an order dates in between 2014-01-01 and 2014-12-13. So, let's build our pseudo-code select. We do this inside out (starting at the innermost, and working our way to the outermost layer of the select).

First up, generate a list of Members who have orders in 2014. So that's

Select Bill_Member_ID
From Order_Table
WHERE Year( OrderDate ) = '2014';

We feed that as a condition to the next select. This next layer's job is to select all the Members who have an OrderDate in 2013, but not in 2014.

    Select Bill_Member_ID
    From Order_Table
    WHERE Year( OrderDate ) = '2013' AND Bill_Member_ID Not In 
    (
        Select Bill_Member_ID
        From Order_Table
        WHERE Year( OrderDate ) = '2014'
    )

This yields your list of Bill_Member_ID who have orders in 2013, but not in 2014. Add the additional columns you need for your final select. Also, add in your other filtering conditions (ie, the not Test and not Staff) to narrow the results as needed.

That's really all you need to do to generate your list of sought customers. If you aren't using a database that does not supports just specifying the year, then you'll need to use the between dates to filter the where date field in range of condition.