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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a weekly pay period calendar?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to create and populate a pay calendar table:

-- T-SQL create payroll calendar table and populate it with dates

USE AdventureWorks2008;

GO

 

CREATE TABLE HumanResources.PayCalendar (

  ID        INT    IDENTITY    NOT NULL,

  WeekStart CHAR(10)    NULL,

  WeekEnd   CHAR(10)    NULL,

  ModifiedDate datetime default (getdate()))

 

GO

 

INSERT HumanResources.PayCalendar (WeekStart, WeekEnd)

VALUES('2005/06/19','2005/06/25')

 

WHILE (SELECT MAX(WeekStart)

       FROM   HumanResources.PayCalendar) <= '2021/01/07'

  BEGIN

    INSERT HumanResources.PayCalendar (WeekStart, WeekEnd)

    SELECT CONVERT(CHAR,DATEADD(DAY,7,MAX(WeekStart)),111),

           CONVERT(CHAR,DATEADD(DAY,7,MAX(WeekEnd)),111)

    FROM   HumanResources.PayCalendar

  END

GO

 

SELECT * FROM HumanResources.PayCalendar

ORDER BY ID

GO

/*

 

ID    WeekStart   WeekEnd     ModifiedDate

1     2005/06/19  2005/06/25  2010-03-14 17:52:27.883

2     2005/06/26  2005/07/02  2010-03-14 17:52:27.890

3     2005/07/03  2005/07/09  2010-03-14 17:52:27.890

4     2005/07/10  2005/07/16  2010-03-14 17:52:27.890

5     2005/07/17  2005/07/23  2010-03-14 17:52:27.890

6     2005/07/24  2005/07/30  2010-03-14 17:52:27.890

7     2005/07/31  2005/08/06  2010-03-14 17:52:27.890

.....

*/

 

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