Listing number sequence for financial periods

Question

In SQL 2016, I need to create a list using financial periods but only have the from/to available - it's formatted similar to dates but are 0mmyyyy, so the first 3 numbers are the month/period and the last 4 digits the year.

e.g. period_from is '0102017' and period_to '0032018', but trying to bring back a list that includes the ones in between as well?

0102017, 
0112017, 
0122017, 
0012018,
0022018

Also, the first three characters can go to 012 or 013, so need to be able to easily alter the code for other databases.

Answer 1

This is going to be a little complicated. To start, I have a user defined table value function that outputs a calendar table based on a start and end date. You'll want to create that first...

CREATE FUNCTION dbo.udf_calendar (@datestart smalldatetime, @dateend smalldatetime)
RETURNS @calendar TABLE (
  [day] int,
  [date] smalldatetime
)
AS

BEGIN

  DECLARE @rows int
  DECLARE @i int = 1

  SELECT
    @rows = DATEDIFF(DAY, @datestart, @dateend)

  WHILE (@i <= @rows)
  BEGIN

    INSERT INTO @calendar ([day])
      VALUES (@i)

    SET @i = @i + 1

  END

  UPDATE a
  SET [date] = DATEADD(DAY, [day] - 1, @datestart)
  --select *, DATEADD(day,id-1,@datestart)
  FROM @calendar a

  RETURN
END

Then, the following will give you the output that I THINK you are looking for. I've commented to try and explain how I got there, but it still might be a bit difficult to follow...

--Create temp table example with your period from and to.

IF (SELECT
    OBJECT_ID('tempdb..#example'))
  IS NOT NULL
  DROP TABLE #example

SELECT
  '0102017' periodfrom,
  '0032018' periodto INTO #example

/*

This is the difficult part. Basically you're inner joining the calendar
to the temp table where the dates are between the manipulated period from and to.
I've added an extra column formatted to allow ordering correctly by period.

*/

SELECT DISTINCT
  periodfrom,
  periodto,
  RIGHT('00' + CAST(DATEPART(MONTH, [date]) AS varchar(50)), 3) + CAST(DATEPART(YEAR, [date]) AS varchar(50)) datefill,
   CAST(DATEPART(YEAR, [date]) AS varchar(50)) + RIGHT('00' + CAST(DATEPART(MONTH, [date]) AS varchar(50)), 3) datefill2
FROM dbo.udf_calendar('2015-01-01', '2018-12-31') a
INNER JOIN #example b
  ON a.[date] BETWEEN SUBSTRING(periodfrom, 2, 2) + '-01-' + SUBSTRING(periodfrom, 4, 4) AND SUBSTRING(periodto, 2, 2) + '-01-' + SUBSTRING(periodto, 4, 4)
ORDER BY datefill2

Answer 2

I am not entirely sure what you are wanting to use this list for, but you can get all your period values with the help of a tally table and some common table expressions.

-- Test data
declare @p table(PeriodFrom nvarchar(10),PeriodTo nvarchar(10));
insert into @p values('0102017','0032018'),('0052018','0112018');

-- Specify the additional periods you want to include, use 31st December for correct sorting
declare @e table(ExtraPeriodDate date
                ,ExtraPeriodText nvarchar(10)
                );
insert into @e values('20171231','0132017');

                        -- Convert start and end of periods to dates
with m    as (select cast(min(right(PeriodFrom,4) + substring(PeriodFrom,2,2)) + '01' as date) as MinPeriod
                    ,cast(max(right(PeriodTo,4) + substring(PeriodTo,2,2)) + '01' as date) as MaxPeriod
              from @p
             )          -- Built a tally table of dates to join from
    ,t(t) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)
    ,d(d) as (select top (select datediff(month,MinPeriod,MaxPeriod)+1 from m) dateadd(m,row_number() over (order by (select null))-1,m.MinPeriod) from m, t t1, t t2, t t3, t t4, t t5)
                        -- Use the tally table to convert back to your date period text format
    ,p    as (select d.d as PeriodDate
                    ,'0' + right('00' + cast(month(d) as nvarchar(2)),2) + cast(year(d) as nvarchar(4)) as PeriodText
              from d
              union all -- and add in any of the addition '13th' month periods you specified previously
              select ExtraPeriodDate
                      ,ExtraPeriodText
              from @e
             )
select PeriodText
from p
order by PeriodDate;

Output:

+------------+
| PeriodText |
+------------+
|    0102017 |
|    0112017 |
|    0122017 |
|    0132017 |
|    0012018 |
|    0022018 |
|    0032018 |
|    0042018 |
|    0052018 |
|    0062018 |
|    0072018 |
|    0082018 |
|    0092018 |
|    0102018 |
|    0112018 |
+------------+

---

If this isn't what you require exactly it should put you on the right path to generating these values either as the result of a function or concatenated together into a list as per your comment by using for xml on the result by changing the final select statement to:

select stuff((select ', ' + PeriodText
              from p
              order by PeriodDate
              for xml path('')
             )
             ,1,2,'') as PeriodTexts;

Which outputs:

+---------------------------------------------------------------------------------------------------------------------------------------+
|                                                              PeriodTexts                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------+
| 0102017, 0112017, 0122017, 0132017, 0012018, 0022018, 0032018, 0042018, 0052018, 0062018, 0072018, 0082018, 0092018, 0102018, 0112018 |
+---------------------------------------------------------------------------------------------------------------------------------------+