Unique Date Range Fields in SQL Server 2008

Unique date range fields in SQL Server 2008

The trigger below should work - it is also possible to do this with check contraints, but the logic shown in this post kind of hurts my head.

CREATE TRIGGER [dbo].[DateRangeTrigger]
   ON  [dbo].[TargetTable]
   FOR INSERT, UPDATE
AS 
BEGIN

IF EXISTS (SELECT t.starttime, t.endtime FROM TargetTable t
        Join inserted i
        On (i.starttime > t.starttime AND i.starttime < t.endtime AND i.UniqueId <> t.UniqueId) 
           OR (i.endtime < t.endtime AND i.endtime > t.starttime AND i.UniqueId <> t.UniqueId)
           OR (i.starttime < t.starttime AND i.endtime > t.endtime AND i.UniqueId <> t.UniqueId)
        )
BEGIN
    RAISERROR ('Inserted date was within invalid range', 16, 1)
    IF (@@TRANCOUNT>0)
        ROLLBACK
END

END

Enforcing unique date range fields in SQL Server 2008

I think this condition is more appropriate:

IF EXISTS ( SELECT  * --No need to choose columns in an EXISTS
        FROM    tbl_Example t1
         inner join
                tbl_Example t2
                  on
                     t1.StockCode = t2.StockCode and
                     t1.Type = t2.Type and
                     t1.ValidFrom < t2.ValidTo and
                     t2.ValidFrom < t1.ValidTo and
                     t1.ID <> t2.ID
        where
            t1.ID in (select ID from inserted))
BEGIN
     RAISERROR ('Date range cant overlap existing date ranges for given StockCode and Type', 16, 1)
     ROLLBACK --We're in a trigger, we *must* be in a transaction
END

It uses the simpler condition for detecting overlaps - an overlap exists if both rows start before the other row ends.

SQL: How to count the number of unique days between date ranges that may overlap?

Probably the simplest way would be to create an auxiliary table of dates.

CREATE TABLE Dates(D DATE PRIMARY KEY)

/*
Load dates between 1990-01-01 and 2049-12-31*/
INSERT INTO Dates
SELECT TOP (21915) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1)), '19891231')
         AS N
   FROM master..spt_values v1,
        master..spt_values v2;

Then you can do

DECLARE @FchRef DATE = '20110501';
DECLARE @FchCotDesde DATE = DATEADD(MONTH, -24, @FchRef);

SELECT [IdPersona],
       COUNT(DISTINCT Dates.D) AS Contributions
FROM Contributions  
JOIN Dates ON Dates.D BETWEEN fecha_ingreso AND fecha_egreso
WHERE Dates.D BETWEEN DATEADD(MONTH, -24, @FchRef) AND @FchRef
GROUP BY IdPersona

Generate Dates between date ranges

Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.

EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here's some code to create and populate a table.

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.

DECLARE @StartDate DATE = '20110901'
      , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

Performant, and no recursion.

SQL how to convert row with date range to many rows with each date

Using some sample data...

create table data (begindate datetime, enddate datetime, data int);
insert data select 
 '20130101', '20130104', 7 union all select
 '20130105', '20130106', 9;

The Query: (Note: if you already have a numbers/tally table - use it)

select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join master..spt_values v on v.type='P'
       and v.number between 0 and datediff(d, begindate, enddate)
order by adate;

Results:

|                       COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 |    7 |
| January, 02 2013 00:00:00+0000 |    7 |
| January, 03 2013 00:00:00+0000 |    7 |
| January, 04 2013 00:00:00+0000 |    7 |
| January, 05 2013 00:00:00+0000 |    9 |
| January, 06 2013 00:00:00+0000 |    9 |

---

Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need

;WITH Numbers(number) AS (
  select top(100) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
  cross join sys.columns c
  cross join sys.columns d
  )
select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;

SQL Fiddle Demo

SQL query to display records with no related record within a specific date range

I would do this with a LEFT OUTER JOIN:

select * 
from db.main m
left outer join db.notes n on m.sysid = n.main_id 
    and n.[date] > dateadd(dd, -30, getdate())) 
where n.main_id is null
order by m.ile_number

While loop in SQL Server 2008 iterating through a date-range and then INSERT

SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

Part 3 is specifically relevant as it deals with dates.

Assuming you don't have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates.

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;

I have skipped some detail on how this works as it is covered in the linked article, in essence it starts with a hard coded table of 10 rows, then joins this table with itself to get 100 rows (10 x 10) then joins this table of 100 rows to itself to get 10,000 rows (I stopped at this point but if you require further rows you can add further joins).

At each step the output is a single column called N with a value of 1 (to keep things simple). At the same time as defining how to generate 10,000 rows, I actually tell SQL Server to only generate the number needed by using TOP and the difference between your start and end date - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). This avoids unnecessary work. I had to add 1 to the difference to ensure both dates were included.

Using the ranking function ROW_NUMBER() I add an incremental number to each of the rows generated, then I add this incremental number to your start date to get the list of dates. Since ROW_NUMBER() begins at 1, I need to deduct 1 from this to ensure the start date is included.

Then it would just be a case of excluding dates that already exist using NOT EXISTS. I have enclosed the results of the above query in their own CTE called dates:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT  Date
FROM    Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])

Example on SQL Fiddle

---

If you were to create a calendar table (as described in the linked articles) then it may not be necessary to insert these extra rows, you could just generate your result set on the fly, something like:

SELECT  [Timestamp] = c.Date,
        t.[FruitType],
        t.[NumOffered],
        t.[NumTaken],
        t.[NumAbandoned],
        t.[NumSpoiled]
FROM    dbo.Calendar AS c
        LEFT JOIN dbo.MyTable AS t
            ON t.[Timestamp] = c.[Date]
WHERE   c.Date >= @StartDate
AND     c.Date < @EndDate;

---

ADDENDUM

To answer your actual question your loop would be written as follows:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
    BEGIN
        INSERT INTO MyTable ([Timestamp])
        VALUES (@CurrentDate);
    END

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END

Example on SQL Fiddle

I do not advocate this approach, just because something is only being done once does not mean that I should not demonstrate the correct way of doing it.

---

FURTHER EXPLANATION

Since the stacked CTE method may have over complicated the set based approach I will simplify it by using the undocumented system table master..spt_values. If you run:

SELECT Number
FROM master..spt_values
WHERE Type = 'P';

You will see that you get all the numbers from 0 -2047.

Now if you run:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';

You get all the dates from your start date to 2047 days in the future. If you add a further where clause you can limit this to dates before your end date:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;

Now you have all the dates you need in a single set based query you can eliminate the rows that already exist in your table using NOT EXISTS

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Finally you can insert these dates into your table using INSERT

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Hopefully this goes some way to showing that the set based approach is not only much more efficient it is simpler too.

How do I write a SQL query for a specific date range and date time using SQL Server 2008?

Infact this worked for me

 SELECT * 
 FROM myTable
 WHERE CAST(ReadDate AS DATETIME) + ReadTime BETWEEN '2010-09-16 5:00PM' AND '2010-09-21 9:00AM'

---

Related Topics