Databases
StackOverflow2013
Postsdbo
POValue is multiplied when condition is fulfiled
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POValue is multiplied when condition is fulfiled
    primarykey
    data
    text
    <pre><code>SELECT C.ClientCaseNumber, Sum(CASE WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1 ELSE 0 END) AS [Wezwania], Sum(CASE WHEN CA.CaseActionDefinitionId IN (14, 21) THEN 1 ELSE 0 END) AS [Kontakt], Sum(CASE WHEN CA.CaseActionDefinitionId = 32 THEN 1 ELSE 0 END) AS [SMS], Sum(CASE WHEN CA.CaseActionDefinitionId = 44 THEN 1 ELSE 0 END) AS [Zgon], Sum(CASE WHEN CA.CaseActionDefinitionId = 49 THEN 1 ELSE 0 END) AS [Areszt], Sum(CASE WHEN CA.CaseActionDefinitionId = 37 THEN 1 ELSE 0 END) AS [Odmowa], Sum(CASE WHEN CA.CaseActionDefinitionId = 39 THEN 1 ELSE 0 END) AS [Podważa], Sum(CASE WHEN CA.CaseActionDefinitionId = 99 THEN 1 ELSE 0 END) AS [Ugoda] FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 68 UNION ALL SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId IN (54, 55, 56, 58, 59, 60, 61, 62, 63) UNION ALL SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 66 UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId WHERE DefinitionId BETWEEN 70 AND 78) AS x INNER JOIN CaseDetails AS C ON x.CaseDetailId = C.CaseDetailsId INNER JOIN CaseActionHistory AS CA ON C.CaseDetailsId = CA.CaseDetailId WHERE C.ClientId = 11 GROUP BY C.ClientCaseNumber </code></pre> <p>I've got a problem with sum(case). Whenever one of CaseActionHistory record is deleted (CA.IsDeleted ='True') values are multiplied x2 or sometimes even more without any reason. Running just a certain part of query ex:</p> <pre><code>SELECT C.ClientCaseNumber ,Sum(CASE WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1 ELSE 0 END) AS [Wezwania] from CaseActionHistory as CA join CaseDetails as C on CA.CaseDetailId = C.CaseDetailsId where C.ClientId = '11' group by CD.ClientCaseNumber </code></pre> <p>Sums records perfectly without any problems.</p> <p>What is more I need to add an argument</p> <pre><code>....AS x INNER JOIN CaseDetails AS C ON x.CaseDetailId = C.CaseDetailsId INNER JOIN CaseActionHistory AS CA ON C.CaseDetailsId = CA.CaseDetailId WHERE C.ClientId = 11 and CA.IsDeleted &lt;&gt; 'True' GROUP BY C.ClientCaseNumber </code></pre> <p>However this screws all the counting and all sums are equal 0. But what is interesting using:</p> <pre><code>AS x INNER JOIN CaseDetails AS C ON x.CaseDetailId = C.CaseDetailsId INNER JOIN CaseActionHistory AS CA ON C.CaseDetailsId = CA.CaseDetailId WHERE C.ClientId = 11 and CA.IsDeleted = 'True' GROUP BY C.ClientCaseNumber </code></pre> <p>Works perfectly and it sums deleted codes.</p> <p>IsDeleted can have three values: NULL/True/False with NULL as default. True after seting code as deleted; False after restoring the code. </p> <hr> <pre><code>declare @table table ( ClientCaseNumber int ,SetId int ,Wezwania int ,Kontakt int ,SMS int ,Zgon int ,Areszt int ,Odmowa int ,Podważa int ,Ugoda int ,Adres nvarchar(255) ,Info1 nvarchar(255) ,Numer nvarchar(255) ,Info2 nvarchar(255) ,Mail nvarchar(255) ,Info3 nvarchar(255) ,Powód nvarchar(255)) insert into @table ( ClientCaseNumber ,Wezwania ,Kontakt ,SMS ,Zgon ,Areszt ,Odmowa ,Podważa ,Ugoda) SELECT C.ClientCaseNumber, Sum(CASE WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1 ELSE 0 END) AS [Wezwania], Sum(CASE WHEN CA.CaseActionDefinitionId IN (14, 21) THEN 1 ELSE 0 END) AS [Kontakt], Sum(CASE WHEN CA.CaseActionDefinitionId = 32 THEN 1 ELSE 0 END) AS [SMS], Sum(CASE WHEN CA.CaseActionDefinitionId = 44 THEN 1 ELSE 0 END) AS [Zgon], Sum(CASE WHEN CA.CaseActionDefinitionId = 49 THEN 1 ELSE 0 END) AS [Areszt], Sum(CASE WHEN CA.CaseActionDefinitionId = 37 THEN 1 ELSE 0 END) AS [Odmowa], Sum(CASE WHEN CA.CaseActionDefinitionId = 39 THEN 1 ELSE 0 END) AS [Podważa], Sum(CASE WHEN CA.CaseActionDefinitionId = 99 THEN 1 ELSE 0 END) AS [Ugoda] FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 68 UNION ALL SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId IN (54, 55, 56, 58, 59, 60, 61, 62, 63) UNION ALL SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 66 UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId WHERE DefinitionId BETWEEN 70 AND 78) AS x INNER JOIN CaseDetails AS C ON x.CaseDetailId = C.CaseDetailsId INNER JOIN CaseActionHistory AS CA ON C.CaseDetailsId = CA.CaseDetailId WHERE C.ClientId = 11 and (CA.IsDeleted &lt;&gt; 'True' OR CA.IsDeleted IS NULL) GROUP BY C.ClientCaseNumber update @table set SetId = X.SetId , Adres = X.Adres ,Info1 = X.Info1 ,Numer = X.Numer ,Info2 = X.Info2 ,Mail = X.Mail ,Info3 = X.Info3 ,Powód = X.Powód from @table as T join ( SELECT C.ClientCaseNumber, C.ClientId, C.SetId, [Adres], [Info1], [Numer], [Info2], [Mail], [Info3], [Powód] FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 68 UNION ALL SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId IN (54, 55, 56, 58, 59, 60, 61, 62, 63) UNION ALL SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId FROM CaseActionHistory WHERE CaseActionDefinitionId = 66 UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId WHERE DefinitionId BETWEEN 70 AND 78) AS x INNER JOIN CaseDetails AS C ON x.CaseDetailId = C.CaseDetailsId INNER JOIN CaseActionHistory AS CA ON C.CaseDetailsId = CA.CaseDetailId WHERE C.ClientId = 11 ) as X on X.ClientCaseNumber = T.ClientCaseNumber SELECT DISTINCT ClientCaseNumber, SetId, [Wezwania], [Kontakt], [SMS], [Zgon], [Areszt], [Odmowa], [Podważa], [Ugoda], [Adres], [Info1], [Numer], [Info2], [Mail], [Info3], [Powód] FROM @table </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USglaeran
    1. USglaeran
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COTry change to `ISNULL(CA.IsDeleted, 0) <> 0`
      singulars
      1. POValue is multiplied when condition is fulfiled
      1. USveljasije
    2. CONope. Shows completely different value.
      singulars
      1. POValue is multiplied when condition is fulfiled
      1. USglaeran
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload