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.
You've not qualified all your predicates in your WHERE clause in the sub-query.

Specifically you're asking for rows where `fiscalId < fiscalId`

My guess based off the information you've provided would be that you want this to either be `fiscalId < a.fiscalId` or `a.fiscalId < fiscalId`

It's a good idea if you're using the same table in multiple places (or using an alias for one table anyway) to use aliases for all tables in your query so it's easy to identify which columns you're calling from what tables.