Calculating stock mutations in sql

Question

I want to calculate the outbound = quantity of products which left the warehouse daily. If I run these queries I can see that sql is counting the quantities somehow double but I don't get why. Anyone who can see the problem? How can I avoid this?

SELECT readYesterday.ArticleCode,
       MAX(readToday.ArticleCode) AS ArticleCodeToday,
       readYesterday.ReportDate,
       SUM(ISNULL(readToday.TotalStock, 0)) AS TotalStockToday,
       SUM(readYesterday.TotalStock) AS TotalStockYesterday,
       SUM(readYesterday.TotalStock - ISNULL(readToday.TotalStock, 0)) AS Outbound
FROM ArticleReads readYesterday
LEFT JOIN ArticleReads readToday ON readToday.ArticleCode = readYesterday.ArticleCode
AND readToday.InboundDossier = readYesterday.InboundDossier
AND readToday.ReportDate = DATEADD(DAY, 1, readYesterday.ReportDate)
WHERE readYesterday.ArticleCode ='ART01234'
  AND readToday.ReportDate <> Convert(date, getdate())
  AND readYesterday.ReportDate = '2018-04-26'
GROUP BY readYesterday.ArticleCode,
         readYesterday.ReportDate
ORDER BY ReportDate ASC

SELECT *
FROM ArticleReads readYesterday
WHERE readYesterday.ArticleCode = 'ART01234'
  AND readYesterday.ReportDate = '2018-04-26'

SELECT *
FROM ArticleReads readYesterday WHERE readYesterday.ArticleCode = 'ART01234'
AND readYesterday.ReportDate = '2018-04-27'

Here you can see the results

Answer 1

When joining the yesterday and today tables, you must be sure that you join on the primary key. In your case, you are using articlecode, inbounddossieur, and the reportdate+1; However, as there are 2 lines for each and nothing to distinguish them, the join will return 4 rows! You need to add an join condition which uniquely specifies each row - Location Code maybe?

and readToday.LocationCode=readYesterday.LocationCode