1

Using this data (also shown in this fiddle):

create table dbo.records (
  id int not null primary key identity(1, 1),
  startdatetime datetime not null,
  enddatetime datetime not null
);

insert into dbo.records (startdatetime, enddatetime)
values ('2014-01-01 12:00:00', '2014-01-05 12:00:00'),
 ('2014-01-10 12:00:00', '2014-01-15 12:00:00');

What query could I use to get the result set below?

2014-01-01 12:00:00   2014-01-02 00:00:00
2014-01-02 00:00:00   2014-01-03 00:00:00
2014-01-03 00:00:00   2014-01-04 00:00:00
2014-01-04 00:00:00   2014-01-05 00:00:00
2014-01-05 00:00:00   2014-01-05 12:00:00
2014-01-10 12:00:00   2014-01-11 00:00:00
2014-01-11 00:00:00   2014-01-12 00:00:00
2014-01-12 00:00:00   2014-01-13 00:00:00
2014-01-13 00:00:00   2014-01-14 00:00:00
2014-01-14 00:00:00   2014-01-15 00:00:00
2014-01-15 00:00:00   2014-01-15 12:00:00

I would also like the solution to be relatively simple to swap between day/month/year ranges. i have included a month fiddle and a [year fiddle](sqlfiddle.com/#!3/ab1d7/1 fiddles) these show example data and expected results.

closed as unclear what you're asking by ypercubeᵀᴹ, RolandoMySQLDBA, Shawn Melton, Mark Storey-Smith, Michael - sqlbot Oct 11 '14 at 2:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Are you asking how to format the dates? – James Anderson Oct 9 '14 at 7:55
  • no, i want to take a single record row and convert them into multiple rows based on range of days or months or years – glend Oct 9 '14 at 7:59
  • How exactly would this result look if the range was months or years? After all, neither rows have data more than a week apart, so it's not entirely clear what your requirements are. – Aaron Bertrand Oct 9 '14 at 14:13
2

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev.

Please note this is intended as a demo, so please double-check the results:

USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'calendar' )
EXEC('CREATE SCHEMA calendar')
GO

-- DROP TABLE calendar.main
IF OBJECT_ID('calendar.main') IS NULL
BEGIN

    CREATE TABLE calendar.main (
        calendarId      INT IDENTITY(1,1) NOT NULL,
        calendarDate    DATE NOT NULL,
        calendarDay     INT NOT NULL,
        calendarMonth   INT NOT NULL,
        calendarYear    INT NOT NULL,
        [dayOfYear]     INT NOT NULL,
        [week]          INT NOT NULL,
        [quarter]       INT NOT NULL,
        datenameDay     VARCHAR(9) NOT NULL,
        datenameMonth   VARCHAR(9) NOT NULL,
        academicMonth   INT NOT NULL,
        academicYear    INT NOT NULL,
        isWeekday       BIT NULL,
        isWeekend       BIT NULL,
        lastDayOfMonth  BIT NULL,

    CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    -- Add indexes
    CREATE INDEX idx_calendar__main__calendarYear ON calendar.main ( calendarYear )
    CREATE INDEX idx_calendar__main__calendarMonth ON calendar.main ( calendarMonth )
    CREATE INDEX idx_calendar__main__calendarDay ON calendar.main ( calendarDay )
    CREATE INDEX idx_calendar__main__calendarYearMonthDay ON calendar.main ( calendarYear, calendarMonth, calendarDay )

    CREATE INDEX idx_calendar__main__academicYear ON calendar.main ( academicYear )
    CREATE INDEX idx_calendar__main__academicMonth ON calendar.main ( academicMonth )
    CREATE INDEX idx_calendar__main__academicYearMonth ON calendar.main ( academicYear, academicMonth )

    --!!TODO add any indexes you need

    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekday
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekend
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR lastDayOfMonth
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicMonth>=(1) AND academicMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicYear>=(1999) AND academicYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarDay>=(1) AND calendarDay<=(31)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarMonth>=(1) AND calendarMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarYear>=(2000) AND calendarYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([dayOfYear]>=(1) AND [dayOfYear]<=(366)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([quarter]>=(1) AND [quarter]<=(4)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([week]>=(1) AND [week]<=(54)))

END
GO


-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM calendar.main )
BEGIN

    -- Populate calendar table
    WITH cte AS
    (
    SELECT 0 x
    UNION ALL
    SELECT x + 1
    FROM cte
    WHERE x < 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
    )
    INSERT INTO calendar.main ( calendarDate, calendarDay, calendarMonth, calendarYear, [dayOfYear], [week], [quarter], datenameDay, datenameMonth, academicMonth, academicYear, isWeekday, isWeekend, lastDayOfMonth )
    SELECT
        calendarDate,
        DAY( calendarDate ) cDay, 
        MONTH( calendarDate ) cMonth, 
        YEAR( calendarDate ) cYear, 
        DATENAME( dayOfYear, calendarDate ) cdayOfYear, 
        DATENAME( week, calendarDate ) cWeek, 
        DATENAME( quarter, calendarDate ) cQuarter, 
        DATENAME( dw, calendarDate ) cdw, 
        DATENAME( month, calendarDate ) cMonthName,
        MONTH( academicDate ) aMonth,
        YEAR( academicDate ) aYear,
        CASE WHEN DATENAME( dw, calendarDate ) Not In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DATENAME( dw, calendarDate ) In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DAY( DATEADD( day, 1, calendarDate ) ) = 1 THEN 1 ELSE 0 END

    FROM
        (
        SELECT 
            DATEADD( day, x, '1 Jan 2000' ) calendarDate,
            DATEADD( month, -7, DATEADD( day, x, '1 Jan 2000' ) ) academicDate
        FROM cte
        ) x
    WHERE calendarDate < '1 Jan 2031'
    OPTION ( MAXRECURSION 0 )


    ALTER INDEX ALL ON calendar.main REBUILD

END
GO



IF OBJECT_ID('dbo.records') IS NOT NULL DROP TABLE dbo.records
CREATE TABLE dbo.records (
    Id              INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    period          VARCHAR(10) NOT NULL,
    startDateTime   DATETIME NOT NULL,
    endDateTime     DATETIME NOT NULL
)
GO

INSERT INTO dbo.records ( period, startDateTime, endDateTime )
VALUES
    ( 'day', '2014-01-01 06:10:10', '2014-01-05 18:20:30' ),
    ( 'day', '2014-01-10 04:40:20', '2014-01-15 02:50:10' ),
    ( 'day', '2014-01-11 04:10:10', '2014-01-11 08:00:10' ),

    ( 'month', '2014-01-05 14:20:30', '2014-06-05 12:30:40' ),
    ( 'month', '2014-10-10 06:50:40', '2014-12-15 04:10:20' ),
    ( 'month', '2014-10-10 06:10:50', '2014-10-10 06:30:10' ),

    ( 'year', '2015-03-05 14:10:30', '2018-02-06 05:20:40'),
    ( 'year', '2019-02-04 12:32:23', '2022-03-09 22:34:12'),
    ( 'year', '2020-04-05 06:05:04', '2020-06-03 09:07:45')

GO

DECLARE @period VARCHAR(10) = 'year'    -- 'day'|'month'|'year'

IF @period = 'day'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( SELECT TOP 1 * FROM calendar.main c WHERE c.calendarDate Between r.startDateTime And r.endDateTime ) c
    WHERE r.period = 'day'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate ) c2
    WHERE r.period = 'day'
    ORDER BY Id, startDateTime, endDateTime

END
ELSE IF @period = 'month'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( 
            SELECT TOP 1 * 
            FROM calendar.main c 
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime 
              AND c.calendarDay = 1
              ) c
    WHERE r.period = 'month'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate AND c2.calendarDay = 1 ) c2
    WHERE r.period = 'month'
      AND c.calendarDay = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE IF @period = 'year'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( 
            SELECT TOP 1 * 
            FROM calendar.main c 
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime 
              AND c.dayOfYear = 1
              ) c
    WHERE r.period = 'year'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarYear > c.calendarYear 
                AND c2.dayOfYear = 1 ) c2
    WHERE r.period = 'year'
      AND c.dayOfYear = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE
    RAISERROR( 'Invalid period specified (%s)', 16, 1, @period )
GO
  • the rule is not a rule, the rule is user data, the data will be used to generate graphs divided into either raw, day, month or year. how would i swap out day for month or year using your solution? i dont understand what the commented where query does. – glend Oct 9 '14 at 10:55
  • OK, in your test data, the start date is the same as the end date (from the previous record) for most of the records, EXCEPT for the penultimate record. To me, it looks like a missing record (ie where is 4-5th Jan?), but you might have a business rule which explains it. If you uncomment my WHERE clause, the records return exactly as per your test data, if you leave it commented out, the results include what I think is a missing record. – wBob Oct 9 '14 at 11:12
  • Re "swap out day for month or year", please provide some sample data and expected results for those scenarios. – wBob Oct 9 '14 at 11:15
  • my bad, my expected result was inaccurate, you were right, i was missing data, post updated. – glend Oct 9 '14 at 11:25
  • lol glad I wasn't going mad. Do you think you could extend my example to work for month and year? It wouldn't be that hard. Give it a try and post back if you need help. Re using PHP, it would be easier if you implemented it as a stored procedure I think. – wBob Oct 9 '14 at 11:48
1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years.

;WITH x AS 
(
  SELECT TOP (366) n = number
    FROM master.dbo.spt_values 
    WHERE type = N'P' ORDER BY number),
y AS
(
  SELECT 
    rs = r.startdatetime,
    re = r.enddatetime,
    s = DATEADD(DAY, x.n,   r.startdatetime), 
    e = DATEADD(DAY, x.n+1, r.startdatetime)
  FROM dbo.records AS r INNER JOIN x
  ON r.enddatetime >= DATEADD(DAY, x.n, r.startdatetime)
),
z AS
(
  SELECT s, e, rs, re,
    r1 = ROW_NUMBER() OVER (PARTITION BY rs ORDER BY s),
    r2 = ROW_NUMBER() OVER (PARTITION BY rs ORDER BY s DESC)
  FROM y
)
SELECT 
  s = CASE WHEN r1 = 1 THEN rs ELSE CONVERT(DATETIME,CONVERT(DATE,s)) END,
  e = CASE WHEN r2 = 1 THEN re ELSE CONVERT(DATETIME,CONVERT(DATE,e)) END
FROM z
ORDER BY s;

On SQL Server 2012 or better, I think you could do this a little more concisely with functions like LAG and LEAD.

Much more on efficient ways to generate sets:

0

This should do what you need.

 DECLARE @Interval NVARCHAR(10), @I DATETIME, 
@IntervalMin DATETIME, @IntervalMax DATETIME

--- Set Interval DAY or MONTH or YEAR -----------------

SET @Interval = 'DAY' 

-------------------------------------------------------

DECLARE @Records TABLE
(StartDateTime DATETIME
 ,EndDateTime DATETIME)

INSERT INTO @Records
SELECT StartDateTime, EndDateTime
From records

DECLARE @Results TABLE
(StartDate DATETIME
 ,EndDate DATETIME)

WHILE EXISTS(SELECT * FROM @Records)
BEGIN
  SELECT TOP 1 @IntervalMin = startdatetime, @IntervalMax = enddatetime
  FROM @Records

  SET @I = @IntervalMin

  WHILE (@I < @IntervalMax)
  BEGIN
    IF @Interval = 'DAY'
    BEGIN
      INSERT INTO @Results
      SELECT @I, CASE WHEN CONVERT(DATE, DATEADD(DAY, 1, @I)) > @IntervalMax THEN @IntervalMax ELSE CONVERT(DATE, DATEADD(DAY, 1, @I)) END

      SET @I = CONVERT(DATE, DATEADD(DAY, 1, @I))
    END

    IF @Interval = 'Month'
    BEGIN
      INSERT INTO @Results
      SELECT @I, CASE WHEN CONVERT(DATE, DATEADD(MONTH, 1, @I)) > @IntervalMax THEN @IntervalMax ELSE CONVERT(DATE, DATEADD(MONTH, 1, @I)) END

      SET @I = CONVERT(DATE, DATEADD(MONTH, 1, @I))
    END

    IF @Interval = 'YEAR' 
    BEGIN
      INSERT INTO @Results
      SELECT @I, CASE WHEN CONVERT(DATE, DATEADD(YEAR, 1, @I)) > @IntervalMax THEN @IntervalMax ELSE CONVERT(DATE, DATEADD(YEAR, 1, @I)) END

      SET @I = CONVERT(DATE, DATEADD(YEAR, 1, @I))
    END

    IF @Interval NOT IN ('DAY','MONTH','YEAR')
      SELECT 'Non valid interval selected!'

  END

  DELETE FROM @Records 
  WHERE startdatetime = @IntervalMin 
  AND enddatetime = @IntervalMax
END

SELECT *
FROM @Results
  • No that fails to respect the time, for example record 1 has a duration of 24h, the desired result is 12h. this also changes the original data. it is required that the original data is not changed. – glend Oct 9 '14 at 9:25
  • Does this edit fix the issues? – James Anderson Oct 9 '14 at 9:36
  • it does. using php it seems harder to implement than wBobs solution though. – glend Oct 9 '14 at 10:48
  • OK but it does give you the ability to select day, month or year – James Anderson Oct 9 '14 at 11:10
  • @user3406701 "using php" what does that have to do with it? Put the T-SQL code in a stored procedure, not in PHP code. The PHP code shouldn't have 800 lines of T-SQL in it. – Aaron Bertrand Oct 9 '14 at 14:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.