Hi Terry,

Thanks for the feedback. Heh... since you recognized it as "integer truncation", I'm pretty sure that I don't actually know more than you. 🙂 I just get real lucky when it comes to T-SQL.

Turning back to your good questions and if I understand them correctly, I don't believe that the "Saturday Week Start" calculation should appear in any of the T-SQL query you may write. All it would do, whether used as a function or not, would be to make for less SARGable and more difficult code to write.

Instead, I believe it should be embedded in the table as a persisted, calculated column. I believe you'll agree when you see the following test.

Here's a 2 million row test table for 5,000 employees over the course of 13 years (2000 through 2012). The hours aren't meant to be accurate... just to be present. It's just a simulation of what you might have.

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('dbo.TimeSheetEntry','U') IS NOT NULL

DROP TABLE dbo.TimeSheetEntry

;

GO

--===== Create the "TimeSheet" test table

CREATE TABLE dbo.TimeSheetEntry

(

TimeSheetEntryID INT IDENTITY(1,1),

Date DATETIME,

EmployeeID INT,

ChargeCode CHAR(2),

Hours DECIMAL(9,2),

WeekStartDate AS DATEADD(dd,DATEDIFF(dd,-2,Date)/7*7,-2) PERSISTED

)

;

CREATE UNIQUE CLUSTERED INDEX IX_TimeSheetEntry_Composite01

ON dbo.TimeSheetEntry (WeekStartDate, EmployeeID, TimeSheetEntryID)

;

ALTER TABLE dbo.TimeSheetEntry

ADD CONSTRAINT PK_TimeSheetEntry PRIMARY KEY NONCLUSTERED (TimeSheetEntryID)

;

--===== Populate the "TimeSheet" test table with a million entries.

INSERT INTO dbo.TimeSheetEntry

(Date, EmployeeID, CHargeCode, Hours)

SELECT TOP 2000000

Date = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2013') + CAST('2000' AS DATETIME),

EmployeeID = ABS(CHECKSUM(NEWID())) % 5000 + 1,

ChargeCode = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))

+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),

Hours = CAST(RAND(CHECKSUM(NEWID())) * 40 AS DECIMAL(9,2)) --Note rounding

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

Once that persisted column is in place and used in the Clustered Index, everything becomes fast and easy. For example, to find the weekly information by month (assuming that you want the WeekStartDate to identify which month something may be in), you could do the following and it actually uses an INDEX SEEK instead of a TABLE SCAN or CLUSTERED INDEX SCAN (identical to a Table Scan, by the way). It'll not only sum up the weekly activity for each employee, but it will sum up the total hours for the month by employee and produce a grand total of hours.

SELECT EmployeeID,

StartDate = WeekStartDate,

TotalHours = SUM(Hours)

FROM dbo.TimeSheetEntry

WHERE WeekStartDate >= 'Feb 2012'

AND WeekStartDate < 'Mar 2012'

GROUP BY EmployeeID,WeekStartDate WITH ROLLUP

;

You can also include total hours by week along with all the other stuff the code above gives you just by changing one word.

SELECT EmployeeID,

StartDate = WeekStartDate,

TotalHours = SUM(Hours)

FROM dbo.TimeSheetEntry

WHERE WeekStartDate >= 'Feb 2012'

AND WeekStartDate < 'Mar 2012'

GROUP BY EmployeeID,WeekStartDate WITH CUBE

;

In 2008 and up, there are even more flexible grouping options but I'll let you discover those for yourself.

Shifting gears a bit, you could add another persisted column or two to assist with monthly reporting, etc. If you make the calculations deterministic, you can "persist" them and index them with nearly the same effectivness as an indexed view.

Because the aggregatations now go so fast, you could easily create high performance dynamic Cross-Tabs for monthly, yearly, and other reporting. You could also turn that query into a view (without the WHERE clause), make it so the NULL grouping markers mean something, and ad a column to indicate the level of detail and it may very well be that single view could meet all of your reporting needs (probably sans Cross-Tab, though).