I am ultimately trying to create a new table through querying data from an existing table. The new table will define time period and provide a count for the number of records from the existing table based on dates.
Existing table looks like.
EIN Start Date Termination Date 1 1/1/2009 6/7/2014 2 5/5/2009 3 2/5/2010 6/12/2015 4 5/19/2012 5 6/13/2014 12/5/2015
I need the new table to look like (sorry it is long) Need some help in getting this done:
Period Active Termed Jan-09 1 Feb-09 1 Mar-09 1 Apr-09 1 May-09 2 Jun-09 2 Jul-09 2 Aug-09 2 Sep-09 2 Oct-09 2 Nov-09 2 Dec-09 2 Jan-10 2 Feb-10 3 Mar-10 3 Apr-10 3 May-10 3 Jun-10 3 Jul-10 3 Aug-10 3 Sep-10 3 Oct-10 3 Nov-10 3 Dec-10 3 Jan-11 3 Feb-11 3 Mar-11 3 Apr-11 3 May-11 3 Jun-11 3 Jul-11 3 Aug-11 3 Sep-11 3 Oct-11 3 Nov-11 3 Dec-11 3 Jan-12 3 Feb-12 3 Mar-12 3 Apr-12 3 May-12 4 Jun-12 4 Jul-12 4 Aug-12 4 Sep-12 4 Oct-12 4 Nov-12 4 Dec-12 4 Jan-13 4 Feb-13 4 Mar-13 4 Apr-13 4 May-13 4 Jun-13 4 Jul-13 4 Aug-13 4 Sep-13 4 Oct-13 4 Nov-13 4 Dec-13 4 Jan-14 4 Feb-14 4 Mar-14 4 Apr-14 4 May-14 4 Jun-14 5 1 Jul-14 4 Aug-14 4 Sep-14 4 Oct-14 4 Nov-14 4 Dec-14 4 Jan-15 4 Feb-15 4 Mar-15 4 Apr-15 4 May-15 4 Jun-15 4 1 Jul-15 3 Aug-15 3 Sep-15 3 Oct-15 3 Nov-15 3 Dec-15 3 1