/*
CIS 324

CHAPTER 7

Dates

*/

/*
Dates pp. 239

--pp. 255 DATE
GETDATE()
DATEDIFF()
DATEPART(); YEAR(), MONTH(), DAY()
DATEADD()

Date values in SQL are enclosed in quotes ''

*/

SELECT GETDATE() AS ServerTime

/*
Marketing just called. They want a list of orders placed during a 
promotional period (07/09/1996 - 07/12/1996).

*/

USE Northwind

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '07/09/1996' AND '07/12/1996'

--Returns the hour portion of the server time
SELECT DATEPART(hour, GETDATE())
SELECT DATEPART(year, GETDATE())
SELECT DATEPART(month, GETDATE())
SELECT DATEPART(dayofyear, GETDATE())

SELECT GETDATE() AS ServerTime


/*
The director of logistics just called. She needs a report that lists 
orders that shipped more than 7 days after the order was placed in the 
month of January 1997.

She reminded you that our COO has set as an strategic objective
that all orders ship within 7 days. How did we do in January 1997?

This is a typical example of an MIS exception report

*/
--Code a little test a lot! Build queries incrementally.

--Retrieve orders in 1997
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 1997

--Restrict result to january 
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 1997 AND 
      MONTH(OrderDate) = 1

--Compute the 7 day difference.
--I often put the DATEDIFF in the SELECT to spot check...
--Only show orders WHERE DaysToShip > 7

SELECT DATEDIFF(day, OrderDate, ShippedDate) AS DaysToShip, *
FROM Orders
WHERE YEAR(OrderDate) = 1997 AND 
      MONTH(OrderDate) = 1

SELECT DATEDIFF(day, OrderDate, ShippedDate) AS DaysToShip, *
FROM Orders
WHERE YEAR(OrderDate) = 1997 AND 
      MONTH(OrderDate) = 1 AND
      DATEDIFF(day, OrderDate, ShippedDate) > 7

--Order the output (the most late first?)
SELECT DATEDIFF(day, OrderDate, ShippedDate) AS DaysToShip, *
FROM Orders
WHERE YEAR(OrderDate) = 1997 AND 
      MONTH(OrderDate) = 1 AND
      DATEDIFF(day, OrderDate, ShippedDate) > 7
ORDER BY DATEDIFF(day, OrderDate, ShippedDate) DESC

/*

DATEDIFF ( datepart , startdate , enddate ) 

The director of logistics loved the report, but she wondered
if we could list all orders with a projected order date that
reflects our strategic goal? That is, derive a column that displays
a projected ship date.

DATEADD(datepart, number, date) 

*/

--You can use the alias in the ORDER BY
SELECT DATEADD(DAY, 7, OrderDate) AS EstimatedShipDate, * 
FROM Orders
WHERE YEAR(ORderDate) = 1997 AND
      MONTH(OrderDate) = 1
ORDER BY EstimatedShipDate

SELECT DATEADD(DAY, 7, OrderDate) AS EstimatedShipDate, * 
FROM Orders
WHERE YEAR(ORderDate) = 1997 AND
      MONTH(OrderDate) = 1 AND
      DATEADD(DAY, 7, OrderDate)= '1997-01-08 00:00:00.000'
ORDER BY EstimatedShipDate

SELECT *
FROM (SELECT DATEADD(DAY, 7, OrderDate) AS EstimatedShipDate, * 
      FROM Orders
      WHERE YEAR(ORderDate) = 1997 AND
            MONTH(OrderDate) = 1) AS OrdersWithShipDate
WHERE EstimatedShipDate = '1997-01-08 00:00:00.000'
ORDER BY EstimatedShipDate