MySQL month negative margin order Details _20161027

#当月每天负毛利订单明细

SELECTc.id,a.city,a.username,a. Order date, A. Order number, A. Sales confirmation, a. Cost amount, a. Gross profit of 1, Case  whenA. Gross Profit 1<0  Then"negative margin" whenA. Gross Profit 1=0  Then"negative margin" whenA. Gross Profit 1>0  Then"positive margin"ELSE NULL END  asidentify 1,b. Benefits, (a. Gross Profit 1-Ifnull (b. Offer,0)) asGross margin 2, Case  whenA. Gross Profit 1=(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))<0  Then"Unused coupon negative margin" whenA. Gross Profit 1=(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))=0  Then"unused coupon 0 Gross margin" whenA. Gross Profit 1=(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))>0  Then"Coupon positive margin not used" whenA. Gross Profit 1>(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))<0  Then"Use coupon negative margin" whenA. Gross Profit 1>(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))=0  Then"Use coupon negative margin" whenA. Gross Profit 1>(A. Gross profit 1-Ifnull (b. Offer,0)) and(A. Gross profit 1-Ifnull (b. Offer,0))>0  Then"Use coupon positive margin"ELSE NULL END  asidentify 2,a. Premiums, (a. Gross Profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) asGross margin 3, Case  when(A. Gross profit 1-Ifnull (b. Offer,0))=(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))<0  Then"No giveaway negative margin" when(A. Gross profit 1-Ifnull (b. Offer,0))=(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))=0  Then"No giveaway 0 gross profit" when(A. Gross profit 1-Ifnull (b. Offer,0))=(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))>0  Then"no premium positive margin" when(A. Gross profit 1-Ifnull (b. Offer,0))>(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))<0  Then"There is a giveaway negative margin" when(A. Gross profit 1-Ifnull (b. Offer,0))>(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))=0  Then"There's a giveaway 0 gross." when(A. Gross profit 1-Ifnull (b. Offer,0))>(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0)) and(A. Gross profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))>0  Then"There is a gift of positive profit"ELSE NULL END  asIdentification 3, (a. Gross Profit 1-Ifnull (b. Offer,0)-Ifnull (A. Gift amount,0))/A. Sales Confirmation amount asNet gross margin from (SELECTA1.city,a1.username,date (A1. Order date) asOrder date, A1. Order number, A1. Sales Clerk,SUMSales asSales Confirmation Amount,SUM(gross margin) asGross margin 1,SUM(Cost amount) asCost Amount,SUM(IF(Sales=0  andCost Amount>0, Cost Amount,NULL)) asGift Amount from' A005_account ' asA1WHEREA1. Order Date>=Date_add (Date_add (Last_day (Date_add (current_date, INTERVAL- 1  Day)), INTERVAL1  Day), INTERVAL-1 MONTH) andA1. Order Date<current_date GROUP  byA1. Order number) asa Left JOIN(#当月每天每个订单ID优惠额SELECTB1.city,b1.username,date (B1. Use time) asUse time, B1. Order number,SUM(coupon amount) asDiscount Amount from' A016_order_customercoupon_xref ' asB1WHEREB1. Use time>=Date_add (Date_add (Last_day (Date_add (current_date, INTERVAL- 1  Day)), INTERVAL1  Day), INTERVAL-1 MONTH) andB1. Use time<current_date GROUP  byB1. Order number) asB onA. Order number=B. Order number Left JOIN' A000_city ' asC onC.city=a.city Left JOIN' A001_rest ' asD onD.username=A.username having(A. Gross profit 1-Ifnull (A. Gift amount,0)-Ifnull (b. Offer,0))<0ORDER  byC.id,a. Order date, (a. Gross profit 1-Ifnull (A. Gift amount,0)-Ifnull (b. Offer,0))

MySQL month negative margin order Details _20161027