1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
| CREATE TABLE dbo.type_jour
(
type_jour_id tinyint NOT NULL IDENTITY(0,1)
CONSTRAINT PK_type_jour PRIMARY KEY
, libelle varchar(8) NOT NULL
CONSTRAINT UQ_type_jour__libelle UNIQUE
)
GO
INSERT INTO dbo.type_jour (libelle) VALUES ('semaine'), ('weekend')
GO
CREATE TABLE dbo.calendrier
(
calendrier_id int NOT NULL IDENTITY
CONSTRAINT PK_leCalendrier PRIMARY KEY
, jour date NOT NULL
CONSTRAINT UQ_calendrier__jour UNIQUE
, type_jour_id tinyint NOT NULL
CONSTRAINT FK_calendrier__type_jour_id FOREIGN KEY(type_jour_id) REFERENCES dbo.type_jour
)
GO
WITH
N AS (SELECT NULL AS v UNION ALL SELECT NULL)
, N1 AS (SELECT A.v FROM N AS A CROSS JOIN N AS B)
, N2 AS (SELECT A.v FROM N1 AS A CROSS JOIN N1 AS B)
, N3 AS (SELECT A.v FROM N2 AS A CROSS JOIN N2 AS B)
, N4 AS (SELECT A.v FROM N3 AS A CROSS JOIN N3 AS B)
, RN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS rn
FROM N4
)
INSERT INTO dbo.calendrier
(
jour
, type_jour_id
)
SELECT d.jour
, CASE DATENAME(weekday, d.jour)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 1
ELSE 0
END AS type_jour_id
FROM (
SELECT DATEADD(day, rn, CAST('20160101' AS date)) AS jour
FROM RN
WHERE rn < DATEDIFF(day, CAST('20160101' AS date), CAST('20170101' AS date))
) AS d
GO
CREATE VIEW uneVue
AS
SELECT i
FROM dbo.ma_table
WHERE EXISTS
(
SELECT *
FROM dbo.calendrier AS c
INNER JOIN dbo.type_jour AS tj
ON c.type_jour_id = tj.type_jour_id
WHERE c.jour = CAST(GETDATE() AS date)
AND tj.libelle = 'weekend'
) |
Partager