Блог

Я использую функцию SUM в инструкции when case, когда я сделал неправильное решение только [TOTALTesellum] . Что я могу сделать, вот мой код SQL

 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
  

Что я понимаю из вашего запроса — просто переформатируйте и перестройте скрипт, это может решить вашу проблему

 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