Calendar Test for Semi Monthly Date Ranges, Sql Server

Calendar Test for Semi Monthly Date Ranges

Language: Layout:

--/* ----- Calendar Test */
    ;with n as (
      select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t(n)
    )
    , cal as (
      select DateValue=convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1, '20160101'))
        from n as a 
          cross join n as b 
          cross join n as c 
          cross join n as d
    )
    select
          --DateValue=convert(varchar(10),DateValue,121)
          minDate =convert(varchar(10),min(DateValue),121)
        , maxDate =convert(varchar(10),max(DateValue),121)
        , FromDatetime=convert(varchar(10),dateadd(day, (1- (day(DateValue)/16)) - (day(DateValue)%16), DateValue),121)
        , ThruDatetime=convert(varchar(10),case 
            when day(DateValue)>15 
              then dateadd(m, datediff(m, -1, DateValue), 0) 
              else convert(varchar(10),dateadd(day, 16 - day(DateValue), DateValue),121) 
            end,121)
        , GordonFrom  = convert(varchar(10),dateadd(day, 1  - day(DateValue), cast(DateValue as date)),121)
        , GordonThru  = convert(varchar(10),dateadd(day, 16 - day(DateValue), cast(DateValue as date)),121)
      from cal
      where datevalue >= '20160101' 
        and datevalue <  '20170101'
      --/*
      group by 
          convert(varchar(10),dateadd(day, (1- (day(DateValue)/16)) - (day(DateValue)%16), DateValue),121)
        , convert(varchar(10),case 
            when day(DateValue)>15 
              then dateadd(m, datediff(m, -1, DateValue), 0) 
              else convert(varchar(10),dateadd(day, 16 - day(DateValue), DateValue),121) 
            end,121)
        , convert(varchar(10),dateadd(day, 1  - day(DateValue), cast(DateValue as date)),121)
        , convert(varchar(10),dateadd(day, 16 - day(DateValue), cast(DateValue as date)),121)
      order by FromDateTime

View schema
Execution time: 0,02 sec, rows selected: 24, rows affected: 0, absolute service time: 0,16 sec

	minDate	maxDate	FromDatetime	ThruDatetime	GordonFrom	GordonThru
1	2016-01-01	2016-01-15	2016-01-01	2016-01-16	2016-01-01	2016-01-16
2	2016-01-16	2016-01-31	2016-01-16	2016-02-01	2016-01-01	2016-01-16
3	2016-02-01	2016-02-15	2016-02-01	2016-02-16	2016-02-01	2016-02-16
4	2016-02-16	2016-02-29	2016-02-16	2016-03-01	2016-02-01	2016-02-16
5	2016-03-01	2016-03-15	2016-03-01	2016-03-16	2016-03-01	2016-03-16
6	2016-03-16	2016-03-31	2016-03-16	2016-04-01	2016-03-01	2016-03-16
7	2016-04-01	2016-04-15	2016-04-01	2016-04-16	2016-04-01	2016-04-16
8	2016-04-16	2016-04-30	2016-04-16	2016-05-01	2016-04-01	2016-04-16
9	2016-05-01	2016-05-15	2016-05-01	2016-05-16	2016-05-01	2016-05-16
10	2016-05-16	2016-05-31	2016-05-16	2016-06-01	2016-05-01	2016-05-16
11	2016-06-01	2016-06-15	2016-06-01	2016-06-16	2016-06-01	2016-06-16
12	2016-06-16	2016-06-30	2016-06-16	2016-07-01	2016-06-01	2016-06-16
13	2016-07-01	2016-07-15	2016-07-01	2016-07-16	2016-07-01	2016-07-16
14	2016-07-16	2016-07-31	2016-07-16	2016-08-01	2016-07-01	2016-07-16
15	2016-08-01	2016-08-15	2016-08-01	2016-08-16	2016-08-01	2016-08-16
16	2016-08-16	2016-08-31	2016-08-16	2016-09-01	2016-08-01	2016-08-16
17	2016-09-01	2016-09-15	2016-09-01	2016-09-16	2016-09-01	2016-09-16
18	2016-09-16	2016-09-30	2016-09-16	2016-10-01	2016-09-01	2016-09-16
19	2016-10-01	2016-10-15	2016-10-01	2016-10-16	2016-10-01	2016-10-16
20	2016-10-16	2016-10-31	2016-10-16	2016-11-01	2016-10-01	2016-10-16
21	2016-11-01	2016-11-15	2016-11-01	2016-11-16	2016-11-01	2016-11-16
22	2016-11-16	2016-11-30	2016-11-16	2016-12-01	2016-11-01	2016-11-16
23	2016-12-01	2016-12-15	2016-12-01	2016-12-16	2016-12-01	2016-12-16
24	2016-12-16	2016-12-31	2016-12-16	2017-01-01	2016-12-01	2016-12-16