DateFirst function and Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DateFirst function and Views

Author

Topic

CDBanks
Starting Member

23 Posts

Posted - 2005-08-09 : 12:31:20

I am working on a view that I will eventually pull into subsequent views for simple labor calculations. The problem I am having is that I need the week to run Monday thru Sunday not the SQL default Sunday thru Saturday.
I can get this to work using the "Set DateFirst 1" command in SQL but when attempting to use that in a view I am unable to save the view b/c of an odbc error.

Below is my SQL:
-- ***************************************
-- * Labor Employee Summary Position.SQL *
-- ***************************************

SET DATEFIRST 1

SELECT DATEPART(YY, a.DateOfBusiness) AS [Year],
DATEPART(WK, a.DateOfBusiness) AS [Week],
a.FKStoreId AS [Store],
b.LastName + ', ' + b.FirstName AS [Employee],
a.FKEmployeeNumber AS [EmpNo],
c.ShortName,
SUM(a.TotalMinutes) AS [TotMin],
a.Rate,
SUM(a.CCTips) AS [TotCCTips],
SUM(a.DeclaredTips) AS [TotDecTips],
SUM(a.Sales) AS [TotSales]

FROM AlohaUser.dpvHstGndShift a INNER JOIN
AlohaUser.EmployeeByStore b ON a.FKStoreId = b.Owner AND a.FKEmployeeNumber = b.EmployeeNumber INNER JOIN
AlohaUser.JobCode c ON a.FKJobCodeId = c.JobCodeId

WHERE (a.FKStoreId = 1)
AND (a.Invalid = 'n')
AND (DATEPART(WK,a.DateOfBusiness) = DATEPART(WK,GETDATE())-1)
AND (DATEPART(YY,a.DateOfBusiness) = DATEPART(YY,GETDATE()))

GROUP BY DATEPART(YY, a.DateOfBusiness),
DATEPART(WK, a.DateOfBusiness),
a.FKStoreId,
b.LastName + ', ' + b.FirstName,
a.FKEmployeeNumber,
c.ShortName,
a.Rate

ORDER BY [Employee], [Week], [Store][/size=6]

Any advise would be greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-09 : 13:13:54

Not a lot of response here so I'll throw in MHO:

I guess you've realized that you can't use "set datefirst 1" as part of a view definition. You may have also realized that whatever DATEFIRST setting that was in affect when the view was created has no bearing on the results (ie: if someone using the view has datefirst set to 7).

One solution is to create a Calander table that has a weekOfYear column set as you need it. Then you can join to that table instead of using datepart functions.

Be One with the Optimizer
TG

CDBanks
Starting Member

23 Posts

Posted - 2005-08-09 : 13:21:46

Thanks for your response. I was hoping to come up with a cleaner solution. I have created a similar tables in the past related to time to time decimals. I just hated the thought that it could be done throug qry analyzer and not through a view.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-09 : 13:39:47

I haven't tested this, but what about the idea of using a stored procedure and setting datefirst inside the sproc or just before it and having the sproc return the resultset? Or if you need to use the results like a normal table in a join or something, perhaps a function that returns a table?

---------------------------
EmeraldCityDomains.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-09 : 13:48:54

You could also pick a monday and use datediff in combination with '/' and '%' to manage info...

by the way... week numbers (as you are running into here) are not very precise for identifying a date range. why not use the actual date range itself?? like beginDate and enddate.

also:


--since '1/1/1900' was a monday,
Declare @MondayNum int
Set @MondayNum=2 --Week Starts Sunday

Select 
	isnull(nullif((datediff(dy,'1/1/1900',getdate())+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+1)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+2)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+3)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+4)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+5)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+6)+@MondayNum)%7,0),7)


Set @MondayNum=1 --Week Starts Monday

Select 
	isnull(nullif((datediff(dy,'1/1/1900',getdate())+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+1)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+2)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+3)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+4)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+5)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+6)+@MondayNum)%7,0),7)

Set @MondayNum=4 --Week Starts Friday

Select 
	isnull(nullif((datediff(dy,'1/1/1900',getdate())+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+1)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+2)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+3)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+4)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+5)+@MondayNum)%7,0),7),
	isnull(nullif((datediff(dy,'1/1/1900',getdate()+6)+@MondayNum)%7,0),7)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-09 : 15:37:10

You can use the start of week function in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

This code will also give you Monday on or before any date, just replace getdate() with the date you want the Monday for.

select Monday = dateadd(dd,(datediff(dd,-53690,getdate())/7)*7,-53690)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources