'How would I minus one month from the previous month?

Currently the my database is setup, in order to get a full picture of the month to date (MTD) value, you have to use year to date (YTD) - the previous month year to date (YTD) to get the values for the current month.

Math Example of trying to get October's Month to date (MTD) Value: October YTD - September YTD

Currently I have the query setup (below) to pull in current period (Table A) and then Prior period (Table B) which is 1 minus the current period identifier.

Table A is the current YTD value WHEREAS Table B is always one month behind table A.

SELECT DISTINCT b.pe, 
            a.penum AS 'Current Period',
            sum(a.hwaytd) AS 'AHoursWorked', 
            b.penum AS 'Previous Period', 
            sum(b.hwaytd) AS 'BhoursWorked'
   
FROM stageElite.dbo.df_dwtkmat a
JOIN stageElite.dbo.df_dwtkmat b on b.matter = a.matter and a.watty = b.watty and b.penum = a.penum-1  

WHERE a.matter = '001153.1510'

GROUP BY b.pe, b.penum, a.penum
ORDER BY a.penum DESC

Why is the query wrong for some months? It starts to mess up in periods 0619 (june 2019) where the numbers are off, but from 0719- 1219, the months are accurate. See picture. Accurate values are green. Red values are wrong.

enter image description here

pe    | a.penum | AHoursWorked | b.pe   | b.penum | BHours Worked
------------------------------------------------------------
 1219 |   371   |    5,844     |  1119  |  370    |   8,542
 1119 |   370   |    8,542     |  1019  |  369    |   8382.5
 1019 |   369   |    0         |  NULL  |  NULL   |   NULL
 1019 |   369   |    8,382     |  0919  |  368    |   8,264.75
 0919 |   368   |    0.5       |  NULL  |  NULL   |   NULL
 0919 |   368   |    8,264     |  0819  |  367    |   8,103.75
sqldata-analysis


Solution 1:[1]

Your implicit INNER JOIN is only returning rows where a.watty matches b.watty. This is effectively applying a filter that is having an impact on your aggregated results.

Consider the following simplified table:

 penum | watty | hwaytd
------------------------
   1   |   1   |   5
   1   |   2   |   5
   2   |   1   |   5
   2   |   2   |   5
   3   |   1   |   5

This query:

SELECT a.penum AS APenum, SUM(a.hwaytd) AS AHoursWorked,
       b.penum AS BPenum, SUM(b.hwaytd) AS BHoursWorked
FROM df_dwtkmat a
INNER JOIN df_dwtkmat b ON b.watty = a.watty AND b.penum = a.penum - 1
GROUP BY a.penum, b.penum

Will produce this result, notice that HoursWorked do not match for Penum = 2:

 APenum | AHoursWorked | BPenum | BHoursWorked
-----------------------------------------------
   2    |      10      |   1    |      10
   3    |      5       |   2    |      5

Removing the filter is not a solution by itself, as this will create a many to many relationship and your aggregations will multiply. One potential solution is to do the aggregation in steps.

SELECT a.penum AS APenum, a.HoursWorked AS AHoursWorked,
       b.penum AS BPenum, b.HoursWorked AS BHoursWorked
FROM (SELECT penum, SUM(hwaytd) AS HoursWorked
      FROM df_dwtkmat
      GROUP BY penum) a
LEFT JOIN (SELECT penum, SUM(hwaytd) AS HoursWorked
           FROM df_dwtkmat
           GROUP BY penum) b ON b.penum = a.penum - 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions