Create a list of dates on the fly - SQL

I've recently had the requirement to produce a list of the last 30 days on the fly (I didn't have the option to refer to a 'Numbers' table). This was to be used as a 'Command' in Crystal Reports and joined to a database table to produce 'missing dates'.

Using the system table, spt_values, the first stage was to produce a list of numbers:

SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29

This is then extended to produce a list of the last 30 dates.

SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
) AS D

This was with help from both Chris Morris & Kevin McKelvey at http://www.sqlservercentral.com/

CTE solution here



 

DateName function - SQL

SELECT DATENAME(month, GETDATE()) AS 'MonthName';

Column aliases and derived tables in TSQL 2005 - SQL

SELECT YearMonth, COUNT(JobLogID) AS JobCount
FROM
(SELECT JobLogID, CONVERT(VARCHAR(7), JobLogSignOffTimeStamp, 121) AS YearMonth
FROM JobLog WHERE JobLogSignOff ='1') AS J
GROUP BY YearMonth
ORDER BY YearMonth;

Row numbering in TSQL 2005 - SQL

New to SQL Server 2005 is ROW_NUMBER function.

SELECT
JobLog.JobLogID,
Establishment.EstablishmentName,
JobSubType.JobSubTypeDescription,
JobLog.JobLogSignOffTimeStamp,
ROW_NUMBER() OVER(ORDER BY EstablishmentName ) AS rownumNonDeterministic,
ROW_NUMBER() OVER(ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumDeterministic,
ROW_NUMBER() OVER(PARTITION BY EstablishmentName ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumPartitioning
FROM JobLog
INNER JOIN
Establishment ON JobLog.JobLog_EstablishmentID = Establishment.EstablishmentID  
INNER JOIN
JobSubType ON JobLog.JobLog_JobSubTypeID = JobSubType.JobSubTypeID
WHERE JobLog.JobLogSignOff = '1'
ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp

Useful DBCC commands - SQL

Link to Brad McGehee's Useful DBCC commands