Group By & Sum Of Values With Missing Months

I have gone through a lot of examples and joined couple of them in order to come down to the following statement; DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME; SELECT

Solution 1:

You need to move condtition to ON:

-- ...SELECT 
  [Period] =CONVERT(VARCHAR(4),YEAR(d.d)) +'-'+CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM d LEFTOUTERJOIN VE_STOCKTRANS AS o
  ON o.TRANSDATE >= d.d

  AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
  AND STOCKID =6000AND TRANSTYPE =3553-- hereGROUPBY d.d
ORDERBY d.d;

Solution 2:

A more generic approach is to apply the filter before you join.

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) +1) 
    n =ROW_NUMBER() OVER (ORDERBY [object_id]) -1FROM sys.all_objects ORDERBY [object_id] ) AS n
),
  o AS
(
  SELECT*FROM VE_STOCKTRANS
   WHERE STOCKID =6000AND TRANSTYPE =3553
)
SELECT 
  [Period] =CONVERT(VARCHAR(4), YEAR(d.d)) +'-'+CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM
  d
LEFTOUTERJOIN
  o
    ON  o.TRANSDATE >= d.d
    AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
GROUPBY
  d.d
ORDERBY
  d.d;

It's not strictly necessary here, as you've seen in the other answer. When doing FULL OUTER JOIN or other complex queries, however, it can be extremely helpful to filter your sources in one scope and join in a separate scope.

(I always filter my sources, I hate lumpy ketchup.)