Hello guys

We’re integrating MS SQL servers everywhere for our business along with long trusted technology partner Oracle & will try to include what we have “learned” in our labs within this blogosphere.

Our bio metric attendance system reports are designed with virtual dates (weekends when the employees are not punching, yet the reporting requires the whole month dates appearing in the final output, marking the date and day name for the date) from a virtual calendar created using connect by level pseudocolumn

CREATE OR REPLACE FORCE VIEW XXFPAUTOCALNDR_V
(
   EMPLOYEE_NUMBER,
   DAY_DATE,
   PUNCH_PERIOD
)
AS
       SELECT NULL EMPLOYEE_NUMBER,
              TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL) day_date,
              TO_CHAR (TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL),
                       'MMYYYY')
                 punch_period
         FROM DUAL
   CONNECT BY TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL <=
                 LAST_DAY (TRUNC (SYSDATE));

Well, MS SQL doesn’t provide connect by level approach, instead recursive CTE (common table expression) & let us see how we can create a virtual calendar using CTE on MS SQL

CREATE VIEW UnisVCalendar
as
WITH CTE(virtual_date) AS (
  SELECT cast('1/1/2016' as datetime) as virtual_date
  UNION ALL
  SELECT virtual_date+1
  FROM CTE 
  WHERE virtual_date <= GETDATE() 
)
SELECT * FROM CTE
GO

CTE allows referring the table itself, thus indirectly providing a level sort of reference.

Well, the downside of such a virtual calendar is, one should make sure that to use option(maxrecursion 0) to avoid “The maximum recursion 100 has been exhausted before statement completion” error

Select CONVERT(date,virtual_date,105) from UnisVCalendar
WHERE CONVERT(date,virtual_date,105) BETWEEN '01-JAN-2020' AND '31-JAN-2020'
option (maxrecursion 0)
GO

Now you can safely refer this virtual calendar at applicable situations.