2013年的NOTIN记录。 [英] 2013 records that are NOTIN 2014.

问题描述

您好，



我正在尝试编写一个查询，该查询会为我提供2013年存在订单日期的所有记录但不是在2014年。当我写下我的查询时，它给了我2014年仍然存在的所有2013年记录。我正在努力获得2013年而不是2014年的订单日期记录。请帮助，因为我真的SQL的新手。



非常感谢。



以下是我的查询：



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）为SUM





来自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

和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

和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

和CMI.SUB_CUSTOMER_ID = OMFMC.BILL_SUB_CUSTOMER_ID）





LEFT OUTER JOIN FAR_TXN FT

ON（FT.ORDER_NO = OMFMC.ORDER_NO

和FT.ORDER_LINE_NO = OMFMC.ORDER_LINE_NO）





WHERE

OMFMC.ORDER_STATUS_CODE ='A'

和OMFMC.LINE_STATUS_CODE ='A'

AND OMFMC.ORDER_STATUS_CODE ='A'

和OMFMC.LINE_STATUS_CODE ='A'

和OMFMC.ORDER_DATE BETWEEN'1 / 1/2013 '和'12 / 31/2013'

和OMFMC.ORDER_DATE不在'2014年1月1日'和'12 / 31/2014'之间

和OMFMC.BILL_CUSTOMER_CLASS_CODE不在（'TEST_MBR'，'工作人员'）

和基金（'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



按BILL_MASTER_CUSTOMER_ID排序



了解更多：http：//www.justanswer.com/computer -programming / 8vee5-im-trying-write-query-give.html #ivx3OdjxoUxH

Hello,

I'm trying to write a query that would give me all records 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. 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

Read more: http://www.justanswer.com/computer-programming/8vee5-i-m-trying-write-query-give.html#ixzz3OdjxoUxH

推荐答案

你需要使用 NOT EXISTS [ ^ ]子查询。

You need to use a NOT EXISTS[^] subquery.

这篇关于2013年的NOTIN记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！