问题描述:

I use SUM function in when case statement when I did wrong solution only [TOTALTesellum] . What can I do here is my SQL code

SELECT CUS.NAME AS [Tedarikçi], GOOD.NAME AS [Ürün] ,

SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) AS[Haftalık Sipariş],

SUM(CASE WHEN PDET.COUNT IS NOT NULL THEN PDET.COUNT ELSE 0 END ) AS [Toplam Sipariş],

SUM(CASE WHEN TES.TESELLUMDATE IS NOT NULL THEN TES.ORDERAMOUNT ELSE 0 END) AS[TOTALTesellum],

-- SUM(TES.ORDERAMOUNT) AS [Toplam Tesellum],

(CASE WHEN SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) > SUM(PDET.COUNT) THEN SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) - SUM(PDET.COUNT) ELSE 0 END) AS [Fazlalık] ,

(CASE WHEN SUM(PDET.COUNT) > SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) THEN SUM(PDET.COUNT) - SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) ELSE 0 END) AS [Eksiklik]

--ORDER BY TES.TESELLUMDATE DESC AS [asxasxa]

FROM dbo.IDTESELLUMDATA TES INNER JOIN

dbo.IDLANDPRELOADS PRE ON TES.SUPPLIERID = PRE.EXPORTERCUSTOMERID

INNER JOIN

dbo.IDLANDPRELOADDETAILS PDET ON PDET.PRELOADID = PRE.ID

INNER JOIN

dbo.IDCUSTOMERS CUS ON TES.SUPPLIERID = CUS.ID AND

PRE.EXPORTERCUSTOMERID = CUS.ID

INNER JOIN

dbo.IDLANDGOODSDEFINITIONS GOOD ON TES.GOODID = GOOD.ID AND PDET.GOODSID = GOOD.ID

GROUP BY CUS.NAME , GOOD.NAME

网友答案:

What I understand from your query - just reformatted and re-arrange the script , this may solve your problem

SELECT M.[Ürün],
       M.[Haftalık Sipariş], 
       M.[Toplam Sipariş],
       M.[TOTALTesellum],
       CASE WHEN  M.[Haftalık Sipariş] > M.[PDET COUNT] THEN M.[Haftalık Sipariş] - M.[PDET COUNT] ELSE 0 END AS [Fazlalık],
       CASE WHEN  M.[PDET COUNT] > M.[Haftalık Sipariş]  THEN M.[PDET COUNT]-M.[Haftalık Sipariş]  ELSE 0 END AS [Eksiklik]  
FROM(
   SELECT  CUS.NAME  AS [Tedarikçi],  GOOD.NAME AS [Ürün] ,
           SUM(CASE WHEN PDET.ORDERDATE <= GETDATE() THEN PDET.COUNT ELSE 0 END) AS[Haftalık Sipariş],
           SUM(CASE WHEN PDET.COUNT IS NOT NULL THEN  PDET.COUNT ELSE 0 END ) AS [Toplam Sipariş],
           SUM(DISTINCT CASE WHEN TES.TESELLUMDATE IS NOT NULL THEN TES.ORDERAMOUNT ELSE 0 END) AS[TOTALTesellum],
           SUM(PDET.COUNT) AS [PDET COUNT]
    FROM dbo.IDTESELLUMDATA  TES 
    INNER JOIN dbo.IDLANDPRELOADS PRE ON TES.SUPPLIERID = PRE.EXPORTERCUSTOMERID 
    INNER JOIN dbo.IDLANDPRELOADDETAILS  PDET ON  PDET.PRELOADID = PRE.ID 
    INNER JOIN dbo.IDCUSTOMERS CUS ON TES.SUPPLIERID = CUS.ID AND PRE.EXPORTERCUSTOMERID = CUS.ID 
    INNER JOIN dbo.IDLANDGOODSDEFINITIONS GOOD ON TES.GOODID = GOOD.ID AND PDET.GOODSID = GOOD.ID
    GROUP BY CUS.NAME, GOOD.NAME
)M
相关阅读:
Top