Using Datefirst in a view

[AccessUser22]

Is there anyway to set DateFirst in a view? I am doing a series of invoicing views in which I sum up weekly employee info and then link it back in with it's daily data. There are about 5-6 query levels that I use to do this to satisfy different business rules for the invoicing. Problem is they want the weeks to start on Saturday instead of Sunday which I can do by setting DateFirst = 6. However, I can only do that in a stored proc. I need to do this at the initial level of the views in order to get the queries to run and total properly for each week. And since I can't reference a stored proc in a view or select data from it, I don't see how I'm supposed to use this.

Is there any way to use DateFirst in a view?

The only solution I can think of is to create a stored proc that sets the DateFirst = 6, gathers the invoice data for the period, and dumps the data into a temp table for the other queries to run off of. This stored proc would be executed first within my stored proc I use to run the invoice.

But is there a better way where I won't have to create a temp table at all? Anyone have any ideas?

 

[AlexCuse]

I don't have any similar views I can test on, but have you tried selecting from the view after setting datefirst? I'm not sure if this will effect your results or not. If it does, it may just be a matter of setting datefirst at the start of each of your queries.

Another option might be to use the sql from your view as a derived table in your queries, where you will be able to manipulate datefirst.

You might also be able to change the default setting for your db, although I am less certain on this.

A wise man once said
"The only thing normal about database guys is their tables".

 

[AccessUser22]

As far as I can see I can't change the default setting on this. It would be very helpful if I could though to ensure the query runs properly each time when looking at weekly data. I can't set the datefirst at the start of all my queries since it cannot be used in a view. And I do not need to set it on each query since the first view is where I pull in my data and based on the date create a field called "week" that I then reference throughout the other queries.

That being said, I have about 5-7 views that run on top of this initial view in a series to determine the final invoicing costs. I'm most likely going to set it up through the temp table idea that I had above, but if there is a way to actually change the default setting on this db, that would be great.

 

[AlexCuse]

have you tried selecting from the view after setting datefirst? Are results the same or not?

just try this:

set datefirst 7
select * from myview
where something = something

set datefirst 6
select * from myview
where something = something

Try to tailor the where clause so that it only returns one row that can be easily compared. If you get different results, you may only have to mess with datefirst when running your actual queries on the view.


A wise man once said
"The only thing normal about database guys is their tables".

 

[AccessUser22]

Ahh, I see wear you are going with this. Let me check.

Yes, it appears to carry through for all queries executed if you put it at the top. Very nice. I never would have thought to try that out myself. Thanks!

 

[SQLDenis]

set (datefirst) is for the whole entire session

Lookup SET, SET (described)in BOL for a more detailed explanation

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AccessUser22]

Yes SQLDenis, you are right. I didn't know it worked like that and I'm glad it does. Thanks.

 

[AccessUser22]

One more question, is there an easy way to get around the '53rd' week? I need to include this hangover week with the first week of the year. So for Dec 30th and 31st of 2006 I'd like these to be counted as week 1 of 2007 instead of week 53 or 2006. I could do a CASE statement in the bottom level query I guess that says if it's 53 change it to 1. I just didn't know if there was another way.

Thanks.

 

[AlexCuse]

I think CASE is a pretty good solution here (provided you are also dealing with years)

--sorta, I don't have your field names and such
case when week = 53 then 1 else week end as adj_week,
case when week = 53 then year + 1 else year end as adj_year

If you don't include the year though, this could be a problem as 12/30/06 would be counted the same as 1/1/06.

What is the desired data set to come from this (last 3 months, last year, last 3 years worth of invoicing costs)

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[AccessUser22]

usually we would be pulling a month's worth of data and they would want to see it on a weekly basis. You are right about the year thing. Good catch. Right now I'm just showing weeks though but I suppose I can insert in a field for year so during the group by the two years won't be combined. Thanks for pointing that out.

 

[AlexCuse]

No problem. Glad you got it to work.

Alex

A wise man once said
"The only thing normal about database guys is their tables".