share meeting

 

Ø SUM(CASE WHEN

B.SalesPerson <> C.SalesPersonCode  

 THEN 

Quantity ELSE 0 END

dbo.F_Invoice_Main_CURRENT B WITH (NOLOCK))

ON

A.Item_key = B.ITEMNUMBER_KEY

LEFT JOIN

#D_SalesTeam C WITH(NOLOCK)

ON

B.CustomerNumber = C.CustomerNumber

  ---------------------------------------------------------------------------------

Ø SUM(

CASE WHEN

C.SalesPersonCode 

NOT EXISTS (select salespersoncode from #D_SalesTeam where B.CustomerNumber = CustomerNumber)

THEN Quantity ELSE 0 END

dbo.F_Invoice_Main_CURRENT B WITH (NOLOCK))

ON

A.Item_key = B.ITEMNUMBER_KEY

--------------------------------------------------------------------------------------------------------------------------------------------

 

前提:

有很多字段时,当从同一个字段取出数据来分成几个字段时:

CASE WHEN THEN ELSE END

CASE

WHEN

(DATEDIFF(DAY,SHIPDATE,GETDATE())>=1 AND DATEDIFF(DAY,SHIPDATE,GETDATE())<=30)

THEN

(A.AvarageCost * A.BalanceQuantity) ELSE 0

END

 ----------------------------------------------------------------

SELECT BRAND,

SUM(AVARAGECOST*BALANCEQUANTITY) AS TOTAL_COST_001

0 AS TOTAL_COST_031

INTO #TEMP_00 FROM R_RMA_AGING

WHERE PERIOD='001-030'

GROUP BY BRAND

UNION ALL

SELECT BRAND,

0 AS TOTAL_COST_001,

SUM(AVARAGECOST*BALANCEQUANTITY) AS TOTAL_COST_031 FROM R_RMA_AGING

WHERE PERIOD='031-060'

AND INPUT_DATE = @DATE_CURRENT_E

GROUP BY BRAND