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.