Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 477 | Views in the last 30 days: 1
 
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