-- Crear tabla de fechas
BEGIN
DECLARE @f AS TABLE (id INT IDENTITY(1,1), Fecha DATE);
DECLARE @fini AS DATETIME; SET @fini = '2018-01-01';
DECLARE @ffin AS DATETIME; SET @ffin = '2018-12-31';
IF(@fini >= @ffin) SET @fini = DATEADD(d, -1, @ffin);
WHILE (@fini <= @ffin)
BEGIN
INSERT INTO @f VALUES (@fini);
SET @fini = DATEADD(d, 1, @fini);
END
declare @festivos as table (festivo date unique);
insert into @festivos (festivo) values ('2018-01-01'), ('2018-01-06'),
('2018-04-26'), ('2018-04-27'), ('2018-05-01'), ('2018-05-02'),
('2018-05-15'), ('2018-08-15'), ('2018-10-12'), ('2018-11-01'),
('2018-11-09'), ( '2018-12-06'), ('2018-12-08'), ('2018-12-25'),
('2018-12-31');
END
-- Ver laborables del año
; with z as
(
select fecha, (case datepart(weekday, fecha) when 2 then 'Lun' when 3 then 'Mar' when 4 then 'Mié' when 5 then 'Jue' when 6 then 'Vie' else'' end) diaSem
from @f
where datepart(weekday, fecha) not in (1,7)
and fecha not in (select festivo from @festivos)
)
select fecha, diaSem, row_number() over (order by fecha) orden
from z
order by fecha
-- Crear tabla de fechas BEGIN DECLARE @f AS TABLE (id INT IDENTITY(1,1), Fecha DATE);
END
-- Ver laborables del año ; with z as ( select fecha, (case datepart(weekday, fecha) when 2 then 'Lun' when 3 then 'Mar' when 4 then 'Mié' when 5 then 'Jue' when 6 then 'Vie' else'' end) diaSem from @f where datepart(weekday, fecha) not in (1,7) and fecha not in (select festivo from @festivos) ) select fecha, diaSem, row_number() over (order by fecha) orden from z order by fecha