Create Calendar table from existing ERP table

Transact-SQL
1

I have a table in our ERP system which lists the days in a year that are marked in the system as Working Days (0) and Non-Working Days (1). There is 1 calendar row per year per charging code (BH/NBH).

For example, if I run this query:

SELECT CODE,[YEAR],DAYS#1, DAYS#2, DAYS#3, DAYS#4, DAYS#5, DAYS#6, DAYS#7
FROM Calendars
WHERE [YEAR] = 2019

I get:

+------+------+--------+--------+--------+--------+--------+--------+--------+
| CODE | YEAR | DAYS#1 | DAYS#2 | DAYS#3 | DAYS#4 | DAYS#5 | DAYS#6 | DAYS#7 |
+------+------+--------+--------+--------+--------+--------+--------+--------+
| BH   | 2019 |      1 |      0 |      0 |      0 |      1 |      1 |      0 |
| NBH  | 2019 |      0 |      0 |      0 |      0 |      1 |      1 |      0 |
+------+------+--------+--------+--------+--------+--------+--------+--------+

But I need to transform this so that there is a line for each day for each charging code and indicate the working / non-working days like this:

+------+------------+---------+
| CODE |    DATE    | NONWORK |
+------+------------+---------+
| BH   | 2019-01-01 |       1 |
| BH   | 2019-01-02 |       0 |
| BH   | 2019-01-03 |       0 |
| BH   | 2019-01-04 |       0 |
| BH   | 2019-01-05 |       1 |
| BH   | 2019-01-06 |       1 |
| BH   | 2019-01-07 |       0 |
+------+------------+---------+

How can I do this so that it processes all calendars in existence for all years and charging codes? Here is a table creation script to assist:

CREATE TABLE [dbo].[Calendars](
	[CODE] [varchar](4) NULL,
	[YEAR] [varchar](4) NULL,
	[DAYS#1] [numeric](2, 0) NULL,
	[DAYS#2] [numeric](2, 0) NULL,
	[DAYS#3] [numeric](2, 0) NULL,
	[DAYS#4] [numeric](2, 0) NULL,
	[DAYS#5] [numeric](2, 0) NULL,
	[DAYS#6] [numeric](2, 0) NULL,
	[DAYS#7] [numeric](2, 0) NULL
	)

and test data:

INSERT INTO CALENDARS (CODE, YEAR, DAYS#1, DAYS#2, DAYS#3, DAYS#4, DAYS#5, DAYS#6, DAYS#7) VALUES ('BH',2019,1,0,0,0,1,1,0)

INSERT INTO CALENDARS2 (CODE, YEAR, DAYS#1, DAYS#2, DAYS#3, DAYS#4, DAYS#5, DAYS#6, DAYS#7) VALUES ('NBH',2019,0,0,0,0,1,1,0)

Any assistance would be appreciated.

2

Hi

You can use tally table or other methods
Below

https://www.dotnetheaven.com/article/generate-sequence-of-dates-using-tally-tables-in-sql-server

1 Like
3

Thank you - this looks useful.

Kind regards
Martyn

4 
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
--INSERT INTO dbo.New_Table ( ... )
SELECT 
    C.CODE,
    DATEADD(DAY, number, year_Jan_01) AS DATE,
    CASE (number + 1) % 7 WHEN 1 THEN DAYS#1 WHEN 2 THEN DAYS#2 WHEN 3 THEN DAYS#3
        WHEN 4 THEN DAYS#4 WHEN 5 THEN DAYS#5 WHEN 6 THEN DAYS#6 ELSE DAYS#7 END AS NONWORK
FROM dbo.CALENDARS C
CROSS APPLY (
    SELECT CAST(CAST(YEAR * 10000 + 0101 AS varchar(8)) AS date) AS year_Jan_01
) AS calc1
INNER JOIN cte_tally1000 t ON t.number BETWEEN 0 AND 
    CASE WHEN DATEADD(DAY, 365, year_Jan_01) < DATEADD(YEAR, 1, year_Jan_01) 
         THEN 364 ELSE 365 END
ORDER BY CODE, DATE
2 Likes
5

Scott

Thank you - this works perfectly and I can build up the rest of the calendar columns from here.

You guys officially rock!

Martyn

6

You're welcome!

But one correction. I changed the way I checked leap year while coding but forgot to change the WHEN condition, the values need to be flipped:
CASE WHEN DATEADD(DAY, 365, year_Jan_01) < DATEADD(YEAR, 1, year_Jan_01)
THEN 365 ELSE 364 END /* rather than THEN 364 ELSE 365 END */

7

Hi, I noticed a slight anomaly when looking through the data. The Friday is marked as a working day (0) in the ERP calendar, but in the results here it shows as a non-working day (1), and it seems to do this for every Friday date. Here is some sample output for January 2016. The 1st January is a non-working day, but it is duplicated for every year even though it only appears once in the calendars table, and then every Friday is shown as a non-working day, giving only 4 working days in a standard week:

+------+------------+---------+
| CODE |    DATE    | NONWORK |
+------+------------+---------+
| BH   | 2016-01-01 |       1 |
| BH   | 2016-01-01 |       1 |
| BH   | 2016-01-02 |       1 |
| BH   | 2016-01-03 |       1 |
| BH   | 2016-01-04 |       0 |
| BH   | 2016-01-05 |       0 |
| BH   | 2016-01-06 |       0 |
| BH   | 2016-01-07 |       0 |
| BH   | 2016-01-08 |       1 |
| BH   | 2016-01-09 |       1 |
| BH   | 2016-01-10 |       1 |
| BH   | 2016-01-11 |       0 |
| BH   | 2016-01-12 |       0 |
| BH   | 2016-01-13 |       0 |
| BH   | 2016-01-14 |       0 |
| BH   | 2016-01-15 |       1 |
| BH   | 2016-01-16 |       1 |
| BH   | 2016-01-17 |       1 |
+------+------------+---------+

How best to correct this please, as otherwise it does what is needed?

Many thanks
Martyn

8

I don't know for sure, although in 2016 Fri was New Year's Day, which is a holiday. But you seem to have only one row per year. There must be -- or at least should be -- some other table or something which controls holidays vs nonwork days.

9

Yes, I think the issue lies around the fact that there is one row per year per calendar type in the calendars table in the ERP, so there are columns right up to DAYS#366. If I extend the case statement from your code to go up to 366 then it seems to work and display all of the non-working days but it does make for a long case statement:

CASE(CASE (number + 1) % 366 WHEN 1 THEN DAYS#1 WHEN 2 THEN DAYS#2 WHEN 3 THEN DAYS#3 etc. upto WHEN 366 THEN DAYS#366

Is there anyway to shorten that easily?

10

Not that I can think of off the top of my head. I'm extremely busy today so I don't be able to help with that in the near term.

11

That's OK, I think I can live with it from here. Thanks very much for all your help, have a great weekend.

Kind regards
Martyn

12

hi

i tried to do this ... hope it helps :slight_smile: :slight_smile:
i love any feedback

i did not take into account leap years!!!
why am i posting this ... when Scott has already given the solution

i am doing my own thinking and way
if there are any mistakes please point them out !!!!

drop create data ... 
 drop table #Calendars 
 go 

create table #Calendars 
(
CODE  varchar(100) , 
[YEAR] int , 
DAYS#1  int , 
DAYS#2  int , 
DAYS#3  int , 
DAYS#4  int , 
DAYS#5  int , 
DAYS#6  int , 
DAYS#7 int 
)
go 

 insert into #Calendars select 'BH'  ,   2019,1,0,0,0,1,1,0 
 insert into #Calendars select 'NBH' ,   2019,0,0,0,0,1,1,0 
 go 

select * from #Calendars 
go
SQL .... 
; WITH cte 
     AS (SELECT N= 1, 
                Cast('2019-01-01' AS DATE) AS startdate 
         UNION ALL 
         SELECT n + 1, 
                Dateadd(day, 1, startdate) 
         FROM   cte 
         WHERE  n + 1 <= 365), 
     cte_table 
     AS (SELECT 'BH' AS code, 
                1    AS nonwork, 
                1    AS [day] 
         UNION ALL 
         SELECT 'BH', 
                0, 
                2 AS [day] 
         UNION ALL 
         SELECT 'BH', 
                0, 
                3 AS [day] 
         UNION ALL 
         SELECT 'BH', 
                0, 
                4 AS [day] 
         UNION ALL 
         SELECT 'BH', 
                1, 
                5 AS [day] 
         UNION ALL 
         SELECT 'BH', 
                1, 
                6 AS [day] 
         UNION ALL 
         SELECT 'BH', 
                0, 
                7 AS [day] 
         UNION ALL 
         SELECT 'NBH' AS code, 
                0     AS nonwork, 
                1     AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                0, 
                2 AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                0, 
                3 AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                0, 
                4 AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                1, 
                5 AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                1, 
                6 AS [day] 
         UNION ALL 
         SELECT 'NBH', 
                0, 
                7 AS [day]) 
SELECT CASE 
         WHEN Datepart(day, b.startdate)%7 = 0 THEN 7 
         ELSE Datepart(day, b.startdate)%7 
       END, 
       * 
FROM   cte_table a 
       JOIN cte b 
         ON CASE 
              WHEN Datepart(day, b.startdate)%7 = 0 THEN 7 
              ELSE Datepart(day, b.startdate)%7 
            END = a.day 
ORDER  BY code, 
          b.startdate 
OPTION (maxrecursion 10000); 

go

image
image.png825×711 118 KB