----------------------------------------------------------------------
-- Datetime Related Problems and Temporal Queries
-- Itzik Ben-Gan, Solid Quality Learning
----------------------------------------------------------------------

SET NOCOUNT ON;
USE tempdb;
GO

----------------------------------------------------------------------
-- Getting language-independent weekday
----------------------------------------------------------------------

SELECT DATEPART(weekday, GETDATE() + @@DATEFIRST - 1);

SELECT DATEDIFF(day, 0, GETDATE()) % 7 + 1;

-- Performance test
SET NOCOUNT ON;

DECLARE 
  @i          AS INT,
  @iterations AS INT,
  @start      AS DATETIME,
  @weekday    AS DATETIME,
  @overhead   AS INT;

SET @iterations = 1000000;

-- Overhead
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @i = @i + 1;
END
SET @overhead = DATEDIFF(ms, @start, GETDATE());

-- Technique 1 - Based on DATEPART and @@DATEFIRST
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @weekday = DATEPART(weekday, GETDATE() + @@DATEFIRST - 1);
  SET @i = @i + 1;
END
SELECT
  'Technique 1' AS technique,
  DATEDIFF(ms, @start, GETDATE()) - @overhead AS ms;

-- Technique 2 - Based on offset from base date
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @weekday = DATEDIFF(day, 0, GETDATE()) % 7 + 1;
  SET @i = @i + 1;
END
SELECT
  'Technique 2' AS technique,
  DATEDIFF(ms, @start, GETDATE()) - @overhead AS ms;

/*
technique   ms
----------- -----------
Technique 1 1550

technique   ms
----------- -----------
Technique 2 640
*/
GO

----------------------------------------------------------------------
-- No separation between date and time
----------------------------------------------------------------------

-- Extract just the date
SELECT CAST(
         CONVERT(CHAR(8), GETDATE(), 112) -- 'YYYYMMDD'
         AS DATETIME);

SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME);

SELECT DATEADD(
         day,
         DATEDIFF(day, '19000101', GETDATE()),
         '19000101');

SELECT DATEADD(
         day,
         DATEDIFF(day, 0, GETDATE()),
         0);
GO

-- Comparing performance
SET NOCOUNT ON;

DECLARE 
  @i          AS INT,
  @iterations AS INT,
  @start      AS DATETIME,
  @dt         AS DATETIME,
  @overhead   AS INT;

SET @iterations = 1000000;

-- Overhead
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @i = @i + 1;
END
SET @overhead = DATEDIFF(ms, @start, GETDATE());

-- Technique 1 - Convert to character string and back
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @dt = CAST(
              CONVERT(CHAR(8), GETDATE(), 112)
              AS DATETIME);
  SET @i = @i + 1;
END
SELECT
  'Technique 1' AS technique,
  DATEDIFF(ms, @start, GETDATE()) - @overhead AS ms;

-- Technique 2 - Convert to integer and back
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @dt = CAST(CAST(GETDATE() AS INT) AS DATETIME);
  SET @i = @i + 1;
END
SELECT
  'Technique 2' AS technique,
  DATEDIFF(ms, @start, GETDATE()) - @overhead AS ms;

-- Technique 3 - Based on DATEDIFF
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= @iterations
BEGIN
  SET @dt = DATEADD(
              day,
              DATEDIFF(day, 0, GETDATE()),
              0);
  SET @i = @i + 1;
END
SELECT
  'Technique 3' AS technique,
  DATEDIFF(ms, @start, GETDATE()) - @overhead AS ms;

/*
technique   ms
----------- -----------
Technique 1 1676

technique   ms
----------- -----------
Technique 2 633

technique   ms
----------- -----------
Technique 3 783
*/
GO

-- Extract just the time

-- Accuracy millisecond
SELECT DATEADD(
         ms,
         DATEDIFF(
           ms,
           DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0), -- date only
           GETDATE()),
         0);

SELECT CAST(
         CONVERT(CHAR(12), GETDATE(), 114) -- 'hh:mi:ss:mmm'
         AS DATETIME);

-- Accuracy second
SELECT DATEADD(
         second,
         DATEDIFF(
           second,
           DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0), -- date only
           GETDATE()),
         0);

SELECT CAST(
         CONVERT(CHAR(8), GETDATE(), 114) -- 'hh:mi:ss:mmm'
         AS DATETIME);

-- First day of the month
SELECT DATEADD(
         month,
         DATEDIFF(month, 0, GETDATE()),
         0);

-- Last day of the month
SELECT DATEADD(
         month,
         DATEDIFF(month, 0, GETDATE()) + 1,
         0) - 1;

-- First day of the year
SELECT DATEADD(
         year,
         DATEDIFF(year, 0, GETDATE()),
         0);

-- Last day of the year
SELECT DATEADD(
         year,
         DATEDIFF(year, 0, GETDATE()) + 1,
         0) - 1;

-- First minute of the hour
SELECT DATEADD(
         hour,
         DATEDIFF(hour, 0, GETDATE()),
         0);

-- Last minute of the hour
SELECT DATEADD( minute, -1,
         DATEADD(
           hour,
           DATEDIFF(hour, 0, GETDATE()) + 1,
           0) );

-- Last occurrence of a weekday

-- Last Monday (Inclusive)
SELECT DATEADD(
         day,
         DATEDIFF(
           day,
           0, -- Base Monday date
           GETDATE()) /7*7,
         0); -- Base Monday date

-- Formatted in one line
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7, 0);

SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()) /7*7, 1);

-- Last Sunday
SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()) /7*7, 6);

-- Last Tuesday
SELECT
  DATEADD(
    day,
    DATEDIFF(day, 0 /* Base Date */, GETDATE()) -- diff
      - (DATEDIFF(day, 1 /* Base Tuesday Date */, GETDATE()) % 7), -- offset
    0 /* Base Date */);

-- Last Sunday
SELECT
  DATEADD(
    day,
    DATEDIFF(day, 0 /* Base Date */, GETDATE())
      - (DATEDIFF(day, 6 /* Base Sunday Date */, GETDATE()) % 7),
    0 /* Base Date */);

-- Last Tuesday
SELECT
  DATEADD(
    day,
    DATEDIFF(day, 0 /* Base Date */, GETDATE())
      - (DATEPART(weekday, GETDATE() + @@DATEFIRST
                             - 2 /* datefirst is Tuesday */) - 1),
    0 /* Base Date */);

-- Last Sunday
SELECT
  DATEADD(
    day,
    DATEDIFF(day, 0 /* Base Date */, GETDATE())
      - (DATEPART(weekday, GETDATE() + @@DATEFIRST
                             - 7 /* datefirst is Sunday */) - 1),
    0 /* Base Date */);

-- Last Monday (Inclusive - Reminder)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7, 0);

-- Last Monday (Exclusive)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()-1) /7*7, 0);

-- Next Monday (Inclusive)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()-1) /7*7 + 7, 0);

-- Next Tuesday (Inclusive)
SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()-1) /7*7 + 7, 1);

-- Next Sunday (Inclusive)
SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()-1) /7*7 + 7, 6);

-- Next Monday (Exclusive)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7 + 7, 0);

-- Next Tuesday (Exclusive)
SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()) /7*7 + 7, 1);

-- Next Sunday (Exclusive)
SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()) /7*7 + 7, 6);

-- date of the first day of the current month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);

-- next occurrence of a weekday, inclusive (next Monday in this example)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()-1) /7*7 + 7, 0);

-- date of the first occurrence of a Monday in this month
SELECT DATEADD(day, DATEDIFF(day, 0, 
  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- fmd
    -1) /7*7 + 7, 0);

-- date of the first occurrence of a Tuesday in this month
SELECT DATEADD(day, DATEDIFF(day, 1, 
  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- fmd
    -1) /7*7 + 7, 1);

-- date of the last day of the current month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)-1;

-- date of the last occurrence of a weekday (Monday in this example)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7, 0);

-- last occurrence of a Monday of the current month
SELECT DATEADD(day, DATEDIFF(day, 0,
  (DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)-1) -- lmd
  ) /7*7, 0);

-- last occurrence of a Tuesday of the current month
SELECT DATEADD(day, DATEDIFF(day, 1,
  (DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)-1) -- lmd
  ) /7*7, 1);

-- first occurrence of a Monday in the current year
SELECT DATEADD(day, DATEDIFF(day, 0, 
  DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) -- fmd
    -1) /7*7 + 7, 0);

-- first occurrence of a Tuesday in the current year
SELECT DATEADD(day, DATEDIFF(day, 1, 
  DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) -- fmd
    -1) /7*7 + 7, 1);

-- last occurrence of a Monday in the current year
SELECT DATEADD(day, DATEDIFF(day, 0,
  (DATEADD(year, DATEDIFF(year, 0, GETDATE())+1, 0)-1) -- lmd
  ) /7*7, 0);

-- last occurrence of a Tuesday in the current year
SELECT DATEADD(day, DATEDIFF(day, 1,
  (DATEADD(year, DATEDIFF(year, 0, GETDATE())+1, 0)-1) -- lmd
  ) /7*7, 1);

-- Week start
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7, 0) AS weekstart;

-- Week end
SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()-1) /7*7 + 7, 6) AS weekend;

---------------------------------------------------------------------
-- The birthday problem
---------------------------------------------------------------------

-- Add two employees
SET NOCOUNT ON;
USE Northwind;

INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
  VALUES('Leaping', 'George', '19720229');
INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
  VALUES('Today', 'Mary', CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME));

WITH Args1 AS
(
  SELECT LastName, FirstName, BirthDate,
    DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
    CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
  FROM dbo.Employees
),
Args2 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    DATEADD(year, Diff, BirthDate) AS BDCur,
    DATEADD(year, Diff + 1, BirthDate) AS BDNxt
  FROM Args1
),
Args3 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
      THEN 1 ELSE 0 END AS BDCur,
    BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
      THEN 1 ELSE 0 END AS BDNxt
  FROM Args2
)
SELECT LastName, FirstName, BirthDate,
  CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
FROM Args3;

---------------------------------------------------------------------
-- Age
---------------------------------------------------------------------

-- Expression to calculate age
DECLARE @birth_date AS DATETIME;
SET @birth_date = '19710212'; -- specify birthdate here

SELECT
  DATEDIFF(year, @birth_date, GETDATE())
  - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE())
            < 100 * MONTH(@birth_date) + DAY(@birth_date)
         THEN 1 ELSE 0
    END;
GO

-- Calculate age of employees
SELECT EmployeeID, FirstName, LastName, BirthDate,
  DATEDIFF(year, BirthDate, GETDATE())
  - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE())
            < 100 * MONTH(BirthDate) + DAY(BirthDate)
         THEN 1 ELSE 0
    END AS Age
FROM dbo.Employees;

-- Cleanup
DELETE FROM dbo.Employees WHERE EmployeeID > 9;
GO

---------------------------------------------------------------------
-- Grouping Time Intervals
---------------------------------------------------------------------
USE tempdb
GO

IF OBJECT_ID('Sessions') IS NOT NULL
  DROP TABLE Sessions
GO

CREATE TABLE Sessions
(
  surrogate_key int           NOT NULL IDENTITY(1, 1),
  username      varchar(10)   NOT NULL,
  starttime     smalldatetime NOT NULL,
  endtime       smalldatetime NOT NULL,
  CONSTRAINT PK_Sessions_surrogate_key
    PRIMARY KEY NONCLUSTERED(surrogate_key),
  CONSTRAINT CHK_endtime_gteq_starttime
    CHECK (endtime >= starttime)
)

INSERT INTO Sessions VALUES('User1', '20011201 08:00', '20011201 08:30')
INSERT INTO Sessions VALUES('User1', '20011201 08:30', '20011201 09:00')
INSERT INTO Sessions VALUES('User1', '20011201 09:00', '20011201 09:30')

INSERT INTO Sessions VALUES('User1', '20011201 10:00', '20011201 11:00')
INSERT INTO Sessions VALUES('User1', '20011201 10:30', '20011201 12:00')
INSERT INTO Sessions VALUES('User1', '20011201 11:30', '20011201 12:30')

INSERT INTO Sessions VALUES('User2', '20011201 08:00', '20011201 10:30')
INSERT INTO Sessions VALUES('User2', '20011201 08:30', '20011201 10:00')
INSERT INTO Sessions VALUES('User2', '20011201 09:00', '20011201 09:30')

INSERT INTO Sessions VALUES('User2', '20011201 11:00', '20011201 11:30')
INSERT INTO Sessions VALUES('User2', '20011201 11:32', '20011201 12:00')
INSERT INTO Sessions VALUES('User2', '20011201 12:04', '20011201 12:30')

INSERT INTO Sessions VALUES('User3', '20011201 08:00', '20011201 09:00')
INSERT INTO Sessions VALUES('User3', '20011201 08:00', '20011201 08:30')
INSERT INTO Sessions VALUES('User3', '20011201 08:30', '20011201 09:00')

INSERT INTO Sessions VALUES('User3', '20011201 09:30', '20011201 09:30')

CREATE CLUSTERED INDEX idx_ci_Sessions_username ON Sessions(username)
GO

-- Group overlapping sessions for each user
-- Desired results:
/*
username   starttime                                              endtime                                                
---------- ------------------------------------------------------ ------------------------------------------------------ 
User1      2001-12-01 08:00:00                                    2001-12-01 09:30:00
User1      2001-12-01 10:00:00                                    2001-12-01 12:30:00
User2      2001-12-01 08:00:00                                    2001-12-01 10:30:00
User2      2001-12-01 11:00:00                                    2001-12-01 11:30:00
User2      2001-12-01 11:32:00                                    2001-12-01 12:00:00
User2      2001-12-01 12:04:00                                    2001-12-01 12:30:00
User3      2001-12-01 08:00:00                                    2001-12-01 09:00:00
User3      2001-12-01 09:30:00                                    2001-12-01 09:30:00
*/

---------------------------------------------------------------------
-- Grouping Time Intervals
---------------------------------------------------------------------

-- Session Groups Start Times
SELECT DISTINCT username, starttime
FROM Sessions AS S1
WHERE NOT EXISTS
  (SELECT *
   FROM Sessions AS S2
   WHERE S2.username = S1.username
     AND S2.starttime < S1.starttime
     AND S2.endtime >= S1.starttime)

-- Session Groups End Times
SELECT DISTINCT username, endtime
FROM Sessions AS S1
WHERE NOT EXISTS
  (SELECT *
   FROM Sessions AS S2
   WHERE S2.username = S1.username
     AND S2.endtime > S1.endtime
     AND S2.starttime <= S1.endtime)
GO

-- Session Groups Start and End Times
SELECT
  username,
  starttime,
  (SELECT MIN(endtime)
   FROM (...EndTimes...) AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM (...StartTimes...) AS S
GO

SELECT
  username,
  starttime,
  (SELECT MIN(endtime)
   FROM (SELECT DISTINCT username, endtime
         FROM Sessions AS S1
         WHERE NOT EXISTS
           (SELECT *
            FROM Sessions AS S2
            WHERE S2.username = S1.username
              AND S2.endtime > S1.endtime
              AND S2.starttime <= S1.endtime)) AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM (SELECT DISTINCT username, starttime
      FROM Sessions AS S1
      WHERE NOT EXISTS
        (SELECT *
         FROM Sessions AS S2
         WHERE S2.username = S1.username
           AND S2.starttime < S1.starttime
           AND S2.endtime >= S1.starttime)) AS S
GO

WITH StartTimes AS
(
  SELECT DISTINCT username, starttime FROM Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.starttime < S1.starttime
       AND S2.endtime >= S1.starttime)
),
EndTimes AS
(
  SELECT DISTINCT username, endtime FROM Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.endtime > S1.endtime
       AND S2.starttime <= S1.endtime)
)
SELECT username, starttime,
  (SELECT MIN(endtime) FROM EndTimes AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM StartTimes AS S;
GO

---------------------------------------------------------------------
-- Group By Week
---------------------------------------------------------------------
USE Northwind;

SELECT 
  weekstart,
  weekstart + 6 AS weekend,
  COUNT(*) AS numorders
FROM (SELECT OrderDate,
        OrderDate -
        DATEPART(weekday,
          OrderDate + @@DATEFIRST - 1) + 1 AS weekstart
      FROM Orders) AS O
GROUP BY weekstart
ORDER BY weekstart;
GO

---------------------------------------------------------------------
-- Working Days
---------------------------------------------------------------------

-- Creation script for function fn_workingdays
CREATE FUNCTION dbo.fn_workingdays
(
  @from_dt   AS DATETIME,
  @to_dt     AS DATETIME,
  @datefirst AS INT
) RETURNS INT
AS
BEGIN
  RETURN
  (
    SELECT
      days/7*5 + days%7
        - CASE WHEN 6 BETWEEN from_weekday AND to_weekday
                 THEN 1 ELSE 0 END
        - CASE WHEN 7 BETWEEN from_weekday AND to_weekday
                 THEN 1 ELSE 0 END
    FROM ( SELECT days, from_weekday,
             from_weekday + days%7 - 1 AS to_weekday
           FROM ( SELECT
                    DATEDIFF(day, @from_dt, @to_dt) + 1 AS days,
                    DATEPART(weekday, @from_dt + @@DATEFIRST - @datefirst)
                      AS from_weekday
                ) AS D1
         ) AS D2
  );
END
GO

SELECT dbo.fn_workingdays('20070212', '20070223', 1) 
SELECT dbo.fn_workingdays('20070212', '20070223', 7)