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

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 |
+---------------------------------------------------------------------------------------------------------------------------------------+