Question: Using SQL Server, I have a query that contains a subquery that is intended to provide a lookback for 12 fiscal periods.

It is returning only NULL and I have been unable to determine why. The query is below:

```
SELECT a.companyId,
       a.profitCenterId,
       a.coaId,
       a.fiscalId,
       sum(a.amount*-1) amount,

       twelveMo =
       (SELECT sum(amount*-1) FROM gl
       WHERE
       companyId=a.companyId AND
       coaId=a.coaId AND
       fiscalId>=a.fiscalId-12 AND fiscalId<fiscalId)

FROM
     gl a 
     INNER JOIN fiscal ON a.fiscalId=fiscal.Id
     INNER JOIN coa ON a.coaId=coa.Id
     WHERE coa.statementType=4
     GROUP BY a.companyId,a.profitCenterId,a.coaId,a.fiscalId
     ORDER BY a.companyId,a.profitCenterId,a.coaId,a.fiscalId

```

The results are shown below. My expectation is that I would get a running 12 fiscal period lookback instead of NULLS. 

[![Results of query](https://i.stack.imgur.com/MmNAb.png)](https://i.stack.imgur.com/MmNAb.png)

Answer: 
Your subquery is not properly correlated with the outer query. Especially, this will never match:

```
fiscalId<fiscalId

```

Here is a new version for your query. I used table alias and column prefixes *everywhere*:

```
select 
    a.companyId,
    a.profitCenterId,
    a.coaId,
    a.fiscalId,
    sum(a.amount * -1) amount,
    twelveMo = (
        select coalesce(sum(amount * -1), 0)
        from gl a1
        where 
            a1.companyid = a.companyid
            and a1.coaid = a.coaid
            and a1.fiscalid >= a.fiscalid - 12 and a1.fiscalid < a.fiscalid
        )
from
    gl a 
    inner join fiscal f on a.fiscalid = f.id
    inner join coa c on a.coaid = c.id on c.statementtype = 4
    group by a.companyid, a.profitcenterid, a.coaid, a.fiscalid
    order by a.companyid, a.profitcenterid, a.coaid, a.fiscalid

```

NB: the condition on `coa.statementtype` is better placed in the `on` clause of the relevant `join` than in the `where` clause.
Performing a `sum()` on zero rows will return null. In this case, your subquery for the `twelveMo` column has a `where` clause of:

```
fiscalId<fiscalId

```

This will never return any rows, and thus your `sum` will return null.