|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate a query with correlated OrderDate and DueDate ranges.
-- Period: 2004 MAR & APR - May 1st is not included, used as a limit
-- SQL inner join - SQL between dates - SQL between datetimes - SQL between two dates
DECLARE @PeriodStart datetime, @PeriodEnd datetime
SET @PeriodStart = '2004-03-01'
SET @PeriodEnd = '2004-05-01'
SELECT 'Due Date Period from '+CAST(DATEADD(DD,21,@PeriodStart) as varchar)+
' to '+ CAST(DATEADD(ms,-3, DATEADD(DD,21,@PeriodEnd)) as varchar)
-- Due Date Period from Mar 22 2004 12:00AM to May 21 2004 11:59PM
USE AdventureWorks;
SELECT OrderDate=convert(varchar,poh.OrderDate,101),
DueDate = convert(varchar,pod.DueDate,101),
poh.PurchaseOrderID,
VendorName = v.[Name],
ProductName = p.[Name],
UnitPrice,
Quantity = pod.OrderQty,
pod.LineTotal,
poh.VendorID,
pod.ProductID
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
INNER JOIN Purchasing.Vendor v
ON poh.VendorID = v.VendorID
INNER JOIN Production.Product p
ON pod.ProductID = p.ProductID
-- The BETWEEN operator would include @PeriodEnd as well
WHERE poh.OrderDate >= @PeriodStart AND OrderDate < @PeriodEnd
AND pod.DueDate >= DATEADD(DD,21,@PeriodStart)
AND pod.DueDate < DATEADD(DD,21,@PeriodEnd)
ORDER BY PurchaseOrderID
GO
/* Partial results
OrderDate DueDate PurchaseOrderID VendorName ProductName
03/08/2004 03/22/2004 1885 Australia Bike… Thin-Jam Lock Nut 11
03/08/2004 03/22/2004 1886 Beaumont Bikes Chainring
03/08/2004 03/22/2004 1886 Beaumont Bikes Chainring Bolts
03/08/2004 03/22/2004 1886 Beaumont Bikes Chainring Nut
03/08/2004 03/22/2004 1887 Bergeron … Lock Nut 16
03/08/2004 03/22/2004 1887 Bergeron … Lock Nut 17
*/ |
