Good Afternoon, 

Currently I have functioning code that works for determine the number of opened work orders and the number of closed work orders during a period of time. I now want to add the number of work orders that were opened prior to that period of time; I have the code that produces the results I want but I am having difficulties figuring out how I would have my select statement built into the initial code so I do not have to assign a static date. It will be easier to show you what I currently have and what I expect to get.

```
SELECT o.dateinfo
    ,opened
    ,closed
FROM (
    SELECT MONTH(org_date) + (YEAR(org_date) * 100) AS dateinfo
        ,COUNT(wo) AS opened
    FROM wkaw
    WHERE org_date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 01)
    GROUP BY MONTH(org_date) + (YEAR(org_date) * 100)
    ) o
INNER JOIN (
    SELECT MONTH(cmpl_date) + (YEAR(cmpl_date) * 100) AS dateinfo
        ,COUNT(wo) AS closed
    FROM wkaw
    WHERE cmpl_date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 01)
        AND cmpl_date IS NOT NULL
    GROUP BY MONTH(cmpl_date) + (YEAR(cmpl_date) * 100)
    ) c ON o.dateinfo = c.dateinfo
ORDER BY o.dateinfo

```

Produces these results currently 

```
dateinfo  |  opened  |  closed
--------------------------------
201502    |   285    |    587
201503    |   519    |    345
201504    |   494    |    398
201505    |   415    |    430
201506    |   578    |    465
201507    |   409    |    646
201508    |   501    |    417
201509    |   430    |    347
201510    |   491    |    983
201511    |   657    |    455
201512    |   468    |    366
201601    |   723    |   1105
201602    |    54    |     60

```

I then have the following select statements that is getting the information I would want for each of those terms but they are using static dates instead of auto-generating like I did above.

```
SELECT COUNT(wo) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,02,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,02,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,03,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,03,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,04,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,04,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,05,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,05,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,06,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,06,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,07,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,07,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,08,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,08,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,09,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,09,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,10,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,10,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,11,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,11,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2015,12,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2015,12,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2016,01,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2016,01,01)
SELECT COUNT(*) FROM wkaw WHERE (cmpl_date >= DATEFROMPARTS(2016,02,01) OR cmpl_date IS NULL) AND org_date < DATEFROMPARTS(2016,02,01)

```

The results for those are as follows

```
1  |  2187
1  |  1885
1  |  2059
1  |  2155
1  |  2140
1  |  2253
1  |  2016
1  |  2100
1  |  2183
1  |  1691
1  |  1893
1  |  1995
1  |  1613

```

These numbers do show what I would expect. The results that I want to see though is 

```
dateinfo  |   open_wo_count  |  opened  |  closed
---------------------------------------------------
201502    |      2187        |   285    |    587
201503    |      1885        |   519    |    345
201504    |      2059        |   494    |    398
201505    |      2155        |   415    |    430
201506    |      2140        |   578    |    465
201507    |      2253        |   409    |    646
201508    |      2016        |   501    |    417
201509    |      2100        |   430    |    347
201510    |      2183        |   491    |    983
201511    |      1691        |   657    |    455
201512    |      1893        |   468    |    366
201601    |      1995        |   723    |   1105
201602    |      1613        |    54    |     60

```

I've tried some different methods but they have all produced un-expected results and I am sure it is just because I do not know how. Any help would be appreciated.

Cheers,
Johnathan