DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to get rolling 30 days of data?

The Microsoft SQL Server T-SQL datedadd function can be used for selection. An example follows. The second argument can be negative or positive. The first argument can be week, month, year and more.

-- SELECT INTO create test table with remapping OrderDate to recent values

-- AdventureWorks sample data OrderDate ends in 2004

USE tempdb;

;WITH CTE AS

(SELECT DayOffset=DATEDIFF(DAY, max(OrderDate), GETDATE())

 FROM AdventureWorks2008.Sales.SalesOrderHeader)

SELECT SalesOrderID, OrderDate=convert(date,DATEADD(day, DayOffset,OrderDate)),

       Sale=TotalDue

INTO SOHeader

FROM AdventureWorks2008.Sales.SalesOrderHeader soh

CROSS JOIN CTE

GO

 

-- Rolling last 30 days of data, tomorrow will return different set

SELECT * FROM SOHeader

WHERE  OrderDate >= DATEADD(day,-30, getdate())

ORDER BY OrderDate desc

GO

DROP TABLE SOHeader

/*

SalesOrderID      OrderDate   Sale

75084             2018-03-15  132.60

75085             2018-03-15  18.7187

75086             2018-03-15  8.7848

75087             2018-03-15  38.664

75088             2018-03-15  125.9258

....

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE