Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Week Starts On Saturday?

By Terry Steadman,

This function: fn_Week_Period_Act() was designed based on accounting (payroll) periods that start on a Saturday. This can be applied against a set of records like timesheets or checks. Then this information can be aggregated based on the accounting week (sat - fri) instead of the calendar week (sun - sat). The function is an inline table function with 1 (datetime) parameter. This can be called by itself as:

SELECT
  CurrentDate,
  WeekID,
  StartWeek,
  EndWeek
FROM dbo.fn_Week_Period_Act(CURRENT_TIMESTAMP)

This can also be JOINed or CROSS APPLYed to an existing table as:

SELECT
  ts.Emp_ID,
  ts.Name,
  ts.Work_Date,
  wpa.WeekID,
  wpa.StartWeek,
  wpa.EndWeek
FROM dbo.TimeSheets AS ts
CROSS APPLY dbo.fn_Week_Period_Act(ut.Work_Date) AS wpa

Have a good day and enjoy.

Terry Steadman

Total article views: 470 | Views in the last 30 days: 2
 
Related Articles
FORUM

Cross Apply

Cross Apply is Slow

FORUM

using aggregate function on calculated member value in tablix

apply aggregate function in parent rowgroup

FORUM

function or procedure for dynamic generation of weeks for given month period.

function or procedure for dynamic generation of weeks for given month period. lets take if i give ra...

FORUM

Cross Apply in Sql

Using Cross Apply

ARTICLE

The Cascading (CROSS) APPLY

An old trick nowadays but one which is still underused here on SSC is the cascading CROSS APPLY – wh...

Tags
@@datefirst    
datefirst    
saturday    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones