CODEWITHSUNDEEP
sqlsql-serverdate-range
SilverFish
SilverFish

Reputation: 1106

SQL Server SELECT unique date range

I am using SQL server 2016. I have to insert a record in the table with a date range. There can be only one active Flash news item (Title). Table has StartDate and StopDate. Criteria for inserting record is that there is no overlapping range since only one record can be active at a given time. For the record being inserted StartDate cannot be null, while StopDate could be Null.

SQL it is not working correctly. If @numRows comes out to be 0, then I should be able to insert data.

--table and data script
CREATE TABLE [dbo].[FlashNewsItem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](150) NOT NULL,
    [NavigateToURL] [varchar](250) NULL,
    [StartDate] [smalldatetime] NOT NULL,
    [StopDate] [smalldatetime] NULL,
    [UpdateEmpID] [char](7) NULL,
    [UpdateDate] [smalldatetime] NULL,
 CONSTRAINT [PK_FlashNewsItem] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[FlashNewsItem] ON 

INSERT [dbo].[FlashNewsItem] ([ID], [Title], [NavigateToURL], [StartDate], [StopDate], [UpdateEmpID], [UpdateDate]) 
VALUES (2, N'Weekend Outage', N'https://example.com/sites/Outage_Impact/Report.pdf ', CAST(N'2019-10-15T00:00:00' AS SmallDateTime), CAST(N'2019-10-16T00:00:00' AS SmallDateTime), N'12345', CAST(N'2019-10-31T00:00:00' AS SmallDateTime))
INSERT [dbo].[FlashNewsItem] ([ID], [Title], [NavigateToURL], [StartDate], [StopDate], [UpdateEmpID], [UpdateDate]) 
VALUES (3, N'AWS Public cloud tech talk', N'https://example.com/sites/SiteAssets/index.html', CAST(N'2019-10-30T00:00:00' AS SmallDateTime), CAST(N'2019-12-02T00:00:00' AS SmallDateTime), N'12345', NULL)
SET IDENTITY_INSERT [dbo].[FlashNewsItem] OFF

--Sql:
    DECLARE @StartDate VARCHAR(10), @StopDate VARCHAR(10), @numRows int;
    Set @StartDate ='11/30/2019'
    SEt @StopDate = '12/05/2019'  --null-- '12/01/2019'

SELECT  @numRows = Count(*) 
    FROM [dbo].[FlashNewsItem]
    WHERE ((CONVERT(varchar(10), StartDate, 101)  > @StartDate AND 
    CONVERT(varchar(10), StopDate, 101)  < @StartDate) -- Starts within an already used time slot
    OR 
    (CONVERT(varchar(10), StartDate, 101) < ISNULL(@StopDate, '2079-06-06')  
    AND  CONVERT(varchar(10), StopDate, 101)  >= ISNULL(@StopDate, '2079-06-06') )) -- Ends within an already used time slot

  print @numRows

Upvotes: 0

Views: 504

Answers (1)

Isa Ataseven
Isa Ataseven

Reputation: 176

if I understand correctly you want to insert new data but new data date should not be between any row date.

    --Sql:
    DECLARE @StartDate VARCHAR(10), @StopDate VARCHAR(10), @numRows int;
    Set @StartDate ='2019-11-30'
    SEt @StopDate = '2019-05-12' 

    SELECT  @numRows = Count(*) 
    FROM [dbo].[FlashNewsItem]
    WHERE (@StartDate >= StartDate AND @StartDate <= StopDate) OR
          (@StopDate >= StartDate AND @StopDate <= StopDate)
    print @numRows

Upvotes: 1

Related Questions