Week Number in SQL Server 2005

Last Post 10 Apr 2007 08:17 PM by SwePeso. 55 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 3 of 3 << < 123
Author Messages
SQLUSA
New Member
New Member

--
09 Apr 2007 04:31 PM
rm,

You are a super-duper DBA. Why are you hiding behind a lousy alias?

Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/
SQLUSA
New Member
New Member

--
09 Apr 2007 04:31 PM
Sanjay,

How many did you bail out?

Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/
SQLUSA
New Member
New Member

--
09 Apr 2007 04:47 PM
Nice catch Peter! Happy to see that there are sharp brains in Sweden!

I used to work for the Bank of Sweden on SQL Server 6.5. Was in beautiful Stockholm twice.

Check this one out:

DECLARE @date datetime, @LastWeek int
SET @date = '20061231'

SET @LastWeek = datediff(week, convert(varchar(4), Year(@Date))+'0101', @date)+1

SELECT CASE
WHEN DATENAME(week, @date) = @LastWeek
AND DATEPART(weekday, @date) != 7
THEN 1
ELSE DATENAME(week, @date)




Kalman Toth, SQL Server Architect
SQL Server 2005 Training - http://www.sqlusa.com
SQLUSA
New Member
New Member

--
09 Apr 2007 04:55 PM
Russell,

What is your last name?

I never used SET DATEFIRST. Frankly, I would have to study it before I decide using it. I don't have a need for that. I can see that it may be useful. When I worked on the Tel Aviv Stock Exchange Data Warehouse product with SQL 7, had to use some esoteric date settings.

I am thrilled to learn from you guys, because you bring up real life problems and real life solutions.

I know a lot, but I don't know everything.

Meanwhile my 15 years of experience with SQL Server (and sybase: THE MOTHER) hopefully will benefit you.

Kalman Toth, SQL Server Architect
SQL Server 2005 Training - http://www.sqlusa.com
sanjayattray
New Member
New Member

--
09 Apr 2007 06:06 PM
Kalman,

No body is expert in any given subject. If some one says that he is an expert in some programming language or administration, then he is either lying or trying to fool others. Every body needs help from time to time and this forum is one source of solving issues with sql server. As far as I remember I never said that I am an expert. And I don't know to how many I bailed out, but as I had said before also that there were guys like russell, rm, nosepicker, Ole (I didn't see him for long on this forum), and others who helped me out from time to time.

And man please grow up. Why are you trying to pull things so far. Just stop responding to this thread and close this issue.
SwePeso
New Member
New Member

--
09 Apr 2007 08:06 PM
quote:

Originally posted by: SQLUSA
What brought you back?


A cry for help.
SwePeso
New Member
New Member

--
09 Apr 2007 08:16 PM
quote:

Originally posted by: SQLUSA
Nice catch Peter! Happy to see that there are sharp brains in Sweden!
...
Check this one out:

DECLARE @date datetime, @LastWeek int
SET @date = '20061231'

SET @LastWeek = datediff(week, convert(varchar(4), Year(@Date))+'0101', @date)+1

SELECT CASE
WHEN DATENAME(week, @date) = @LastWeek
AND DATEPART(weekday, @date) != 7
THEN 1
ELSE DATENAME(week, @date)


Even worse!

This is a sample with your new "improved" query.


DATE Kalman Peso
---------- -- --
2006-12-01 1 48
2006-12-02 48 48
2006-12-03 1 49
2006-12-04 1 49
2006-12-05 1 49
2006-12-06 1 49
2006-12-07 1 49
2006-12-08 1 49
2006-12-09 49 49
2006-12-10 1 50
2006-12-11 1 50
2006-12-12 1 50
2006-12-13 1 50
2006-12-14 1 50
2006-12-15 1 50
2006-12-16 50 50
2006-12-17 1 51
2006-12-18 1 51
2006-12-19 1 51
2006-12-20 1 51
2006-12-21 1 51
2006-12-22 1 51
2006-12-23 51 51
2006-12-24 1 52
2006-12-25 1 52
2006-12-26 1 52
2006-12-27 1 52
2006-12-28 1 52
2006-12-29 1 52
2006-12-30 52 52
2006-12-31 1 1
2007-01-01 1 1
2007-01-02 1 1
2007-01-03 1 1
2007-01-04 1 1
2007-01-05 1 1
2007-01-06 1 1
2007-01-07 1 2
2007-01-08 1 2
2007-01-09 1 2
2007-01-10 1 2
2007-01-11 1 2
2007-01-12 1 2
2007-01-13 2 2
2007-01-14 1 3
2007-01-15 1 3
2007-01-16 1 3
2007-01-17 1 3
2007-01-18 1 3
2007-01-19 1 3
2007-01-20 3 3
2007-01-21 1 4
2007-01-22 1 4
2007-01-23 1 4
2007-01-24 1 4
2007-01-25 1 4
2007-01-26 1 4
2007-01-27 4 4
2007-01-28 1 5
2007-01-29 1 5
2007-01-30 1 5
2007-01-31 1 5


Didn't I tell you to TEST your suggestion before posting them?
SQLUSA
New Member
New Member

--
10 Apr 2007 01:48 AM
I give up Peter. You can fix it.
SwePeso
New Member
New Member

--
10 Apr 2007 02:24 AM
Fix what?
My first attempt is still working.
skt5000
New Member
New Member

--
10 Apr 2007 02:52 AM
I agree with you on the experience issue. I forget that although I'm not at your level (or pesomannen or rm), it wan't too long ago that I learned how to use the CASE statement, which I then immediatley tried to make the solution to all my problems, which then caused more problems, etc. I have also had the advantage of learning SQL on a closed system, I could inadvertently drop a database and it wouldn't affect the company (which runs on an AS400). It is obvious to me that Kalman has no real-world experience as many of his soultions are of the variety "tell your boss to invest $100,000 in new hardware" or "buy more disk space, it's cheap". If I offered that type of solution I would be fired and replaced, and deservedly so. It would be easy for someone who didn't know better to institute one of Kalman's solutions, which could be like introducing a virus to your system. I encourage other's reading this read to ignore Kalman's responses and not do his "homework" exercises. Kalman often chimes in first, but it's better to wait for other opinions.

Jim
SQLUSA
New Member
New Member

--
10 Apr 2007 02:05 PM
Peter,

I really appreciate that you reviewed the SQL Server 2005 Best Practices page:

My responses in caps:

How to generate a date sequence ( why update later? ) - IT WORKS THAT WAY
How to find the second highest salary ( why not use MIN? ) - BECAUSE TOP WORKS
How to create a function to count characters in a string ( Ever heard of trailing spaces? ) - IT WORKS ANYHOW
How to calculate the median ( Heard of TOP 50 PERCENT? ) - IT WORKS ANYHOW
How to generate a sequence ( People don't use the number range from -50 to 50? ) - I DID NOT FIND THIS ONE
How to count the days in a month excluding Sundays ( Speed anyone? ) - CRITICISM ACCEPTED

Kalman Toth
SQL Server Training - http://www.sqlusa.com/bestpractices2005
SQLUSA
New Member
New Member

--
10 Apr 2007 03:55 PM
skt5000,

You would be fired if you recommend buying more CHEAP disk? Or more CHEAP memory?

Are you for real?

We live in the golden age of computer science! DISK, MEMORY, (CPU yes or no) are cheap and plentiful.

I don't see any reasonable IT manager would take this as negative.

Comments?

Kalman Toth, SQL Server Architect
SQL Server 2005 Training - http://www.sqlusa.com/bestpractices
SwePeso
New Member
New Member

--
10 Apr 2007 07:58 PM
How to generate a date sequence:
quote:

select TOP 2000 ID=identity(int,1,1),
[Date]=dateadd(day, -366, convert(char(10),getdate(),112))
into #Date
from sys.objects o1
cross join sys.objects o2

update #Date set [Date] = Dateadd(day, ID, [Date])

select * from #Date
The biggest flaw is that everything is hardwired! A thing you accuse other people to do wrong...

Either use this techique posted by me
http://www.sqlservercentral.com/col...retire.asp
Very useful for date sequences up to 10 years.

Or this one for REAL SPEED!
http://www.sqlteam.com/forums/topic...C_ID=61519
It generates hundred of thousands of date in a second!
SwePeso
New Member
New Member

--
10 Apr 2007 08:08 PM
How to create a function to count characters in a string:

No, it does not work for counting SPACES!!!!!!

Why don't you accept criticizm for flawed functions? Try your suggestion with any VARCHAR that have trailing spaces.

HINT: IT DOES NOT WORK.

Use DATALENGTH instead of LEN.

//Peter
SwePeso
New Member
New Member

--
10 Apr 2007 08:17 PM
How to calculate Monday's dates in a month?

See my comments about creating a DATE SEQUENCE.




Now when I have corrected errors and suggested more efficient algorithms, I think I am entitled to reimburement. I suggest you pay me 10 dollars per sold package.
skt5000
New Member
New Member

--
11 Apr 2007 02:19 AM
Yes Kalman, I am for real. I live in the real world and have a real job. I am currently rewriting stored procedures to make them more efficient. I could have told my boss to just invest a bunch of money in hardware - buy more this, get more that, and things will go much faster - but that really isn't always a solution is it? In the real world I have to get things done now. In the real world I work for a small company that flies by the seat of their pants. In the real world I used to work for a very large company that would form a committe to see if the fire in the 3rd floor wastebasket should be put out. I am paid to do my job and solve the problems presented me. If the problem presented were to decide what hardware to purchase and what upgrades to make, I would present those solutions. But in the real world I solve the problems my boss presents, not throw them back at him. Why don't you on your next assignment do something you weren't asked to do and then charge for it. Post you experiences here, let us know what you learned.

Jim
You are not authorized to post a reply.
Page 3 of 3 << < 123


Acceptable Use Policy