By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Service]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ServiceName] varchar(50) NOT NULL,
CONSTRAINT [PK_Service]
PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
CREATE TABLE [dbo].[ServiceRate]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ServiceId] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[CountryId] [int] NOT NULL,
CONSTRAINT [PK_ServiceRate]
PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
ALTER TABLE [dbo].[ServiceRate] WITH CHECK
ADD CONSTRAINT [FK_ServiceRate_Service]
FOREIGN KEY([ServiceId]) REFERENCES [dbo].[Service] ([Id])
ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [FK_ServiceRate_Service]
ALTER TABLE [dbo].[ServiceRate] WITH CHECK
ADD CONSTRAINT [CK_ServiceRate_StartDate_EndDate]
CHECK (([StartDate] <= COALESCE([EndDate], '9999-12-31')))
ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [CK_ServiceRate_StartDate_EndDate]
Insert Into Service Values(1),(2)
Insert into ServiceRate(serviceid, startdate, enddate, countryid)
Values
(1, '2023-01-01', NULL, 0)
,(1, '2023/01/01', NULL, 53)
,(1, '2023/01/01', NULL, 70)
,(2, '2023/01/01', NULL, 0)
,(2, '2023/01/01', NULL, 53)
7 rows affected
Select * from ServiceRate
| Id | ServiceId | StartDate | EndDate | CountryId |
|---|---|---|---|---|
| 1 | 1 | 2023-01-01 00:00:00.000 | null | 0 |
| 2 | 1 | 2023-01-01 00:00:00.000 | null | 53 |
| 3 | 1 | 2023-01-01 00:00:00.000 | null | 70 |
| 4 | 2 | 2023-01-01 00:00:00.000 | null | 0 |
| 5 | 2 | 2023-01-01 00:00:00.000 | null | 53 |
Create Trigger CheckDateData ON ServiceRate
INSTEAD OF Insert
AS
IF EXISTS (
Select Top 1 A.StartDate, A.EndDate, B.StartDate, B.EndDate
from ServiceRate A
INNER JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
AND B.StartDate >= A.StartDate
AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
)
RAISERROR ('Conflict in date insert',16,1)
INSERT INTO ServiceRate(serviceid, startdate, enddate, countryid)
Select b.serviceid, b.startdate, b.enddate, b.countryid
from ServiceRate A
RIGHT JOIN Inserted B ON B.ServiceID = A.ServiceId AND B.CountryID = A.CountryID
AND B.StartDate >= A.StartDate
AND ISNULL(B.EndDate,'9999-12-31') <= ISNULL(A.EndDate,'9999-12-31')
WHERE A.StartDate is null
Insert into ServiceRate(serviceid, startdate, enddate, countryid)
Values
(1, '2023/01/01', '2023/02/28', 0)
Msg 50000 Level 16 State 1 Line 11
Conflict in date insert
select * from servicerate
| Id | ServiceId | StartDate | EndDate | CountryId |
|---|---|---|---|---|
| 1 | 1 | 2023-01-01 00:00:00.000 | null | 0 |
| 2 | 1 | 2023-01-01 00:00:00.000 | null | 53 |
| 3 | 1 | 2023-01-01 00:00:00.000 | null | 70 |
| 4 | 2 | 2023-01-01 00:00:00.000 | null | 0 |
| 5 | 2 | 2023-01-01 00:00:00.000 | null | 53 |