Week Starts On Saturday?

Question

Post reply

Week Starts On Saturday?

terrance.steadman

SSCrazy

Points: 2097
More actions
February 28, 2012 at 8:14 pm

#252357

Comments posted to this topic are about the item Week Starts On Saturday?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

I believe the following simple formula will do the same thing for you. No need for a function here at all.

DATEADD(dd,DATEDIFF(dd,-2,SomeDateColumn)/7*7,-2)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrance.steadman SSCrazy Points: 2097 More actions · Answer 2

Greetings Jeff,

Thank you for your code adaptation to find the start of the week based on integer truncation. Definitely an interesting and faster way to find the start. After that - a simple matter of +7 to get the end. I would like to know though, would you consider it faster to use a "BETWEEN Start AND End" when organizing a series of records into week period sets or is there an easy way to also find the week period of the year as well? My considerations is that when working with timesheets, I usually want to group each person's set of timesheets into weekly periods for finding total hours. I usually am working with several million records so speed tweaks are always helpful.

It is always a joy to learn from others who know more than me and are willing to instruct in better ways.

Thank you for your time.

Terry Steadman

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

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).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Any additional thoughts on this, Terry?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrance.steadman SSCrazy Points: 2097 More actions · Answer 5

Greetings Jeff,

Sorry I did not reply for a bit. Been in a bit of a crunch month here at work. Your idea does sound good to me. From the StartDate, I can get the EndDate easily enough. I had also considered holding the Week ID if it was possible though. Especially since around the turn of the year the same week can easily belong to 2 different year periods. The only other reason I can currently think of for pulling the Week ID is that it can easily be fitted into a calculation for finding the invoice period. This usually follows a 4-4-5 type of pattern.

This is an inherited database I am currently working on. I try to make changes to the structure, like adding columns, to make it better and faster. But, I also have to be carefull as the interface or reports will break if I make the wrong change. Much that I have learned has been self taught and the rest from the School Of Hard Knocks. So, being able to talk to you and others on this site is like a breath of fresh air. I am able to learn simple basics that I had never known before and ways to make a database better.

I do have other questions about how to re-write some SQL scripts to make them better. But I am also held back by the sheer size of the scripts (several are well over 2,000+ lines) and the need to follow security rules and keep the data and database information safe and protected. If I had the chance to sit down with a few experts from this group and talk shop, I think that I would be able to find much better ways to design my scripts and where a lot of my mistakes are. Most of my working experience has been in windows application programming and design. Not in SQL design so my logic is a bit off. It still works thankfully, but I know that it could be much better.

Thank you for your time.

Terry Steadman

P.S.

Sorry for the rambling.:crazy: