debug sql - 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)

debug sql

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-29 : 13:50:28

I have the following sql:

select count(myid) as count from users where myDate between dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')-1)/7)*7,-3) and dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')+1)/7)*7,+3)

What this does it the date that I pass in is always different -- I always want to get friday 00:01 pm till thursday 23:59

The above works for all days but fridays where it shows the count of the past week rather then the future week.

Can someone help me debug?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 14:15:49

select
	count(myid) as count
from
	users
where
	myDate >=
	dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')-1)/7)*7,-3) and
	myDate <
	dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')-1)/7)*7,+4)

quote:
Originally posted by esthera

I have the following sql:

select count(myid) as count from users where myDate between dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')-1)/7)*7,-3) and dateadd(dd,(datediff(dd,-3,convert(datetime,'2005/03/25')+1)/7)*7,+3)

What this does it the date that I pass in is always different -- I always want to get friday 00:01 pm till thursday 23:59

The above works for all days but fridays where it shows the count of the past week rather then the future week.

Can someone help me debug?

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-29 : 14:23:14

that works for friday but wehn I put in the date of the thursday before it shows the follwoing week and not the friday before until and including that thursday.
any ideas?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 15:07:04

I am not sure I understand exactly what you want.

Pleaee show the exact date range you would want for any day of the week. Maybe you could pick 7 consecutive days, and show the From Date and To Date you would want for each.

quote:
Originally posted by esthera

that works for friday but wehn I put in the date of the thursday before it shows the follwoing week and not the friday before until and including that thursday.
any ideas?

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-29 : 15:32:24

if it's march 24 it should show from the friday before till thursday 23:59 -- so march 18 to march 24

if it's march 23 then it will be march 18 to march 24

if it's march 25 then it will be
march 25 to april 1

if it's march 26 then it will be
march 25 to april 1

if it's march 27 then it will be
march 25 to april 1

always showing the weeks entries from friday until thursday night

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 17:13:30

I think you would find this easier if you used the Start of Week Function in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Use that script to create the function in your database.

Then your query would be:

select
	count(myid) as count
from
	users
where
	myDate >= dbo.F_START_OF_WEEK('2005/03/25',6)	and
	myDate <  dateadd(dd,7,dbo.F_START_OF_WEEK('2005/03/25',6))

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-30 : 02:16:43

my problem is I don't want the week to start with sunday.
I need it exactly from friday time 001 till thursday 23:59 and any datetime in between those times should bring up all entries between teh friday to thursday.

Any other suggestions?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-30 : 08:32:19

Did you even look at th code I gave you to see what it returned? If you had, you would be able to see the date range.

quote:
Originally posted by esthera

my problem is I don't want the week to start with sunday.
I need it exactly from friday time 001 till thursday 23:59 and any datetime in between those times should bring up all entries between teh friday to thursday.

Any other suggestions?

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-30 : 08:34:52

I got it!
Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources