Days are not detected - 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

General SQL Server Forums

New to SQL Server Programming

Days are not detected

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-07 : 20:58:27

Hi, I used the following query to update my columns according to the change of time and days of the week. But my query is not working as it should. Today is Tuesday and the time now is 9am so according to my query Mon_Night column should be updated but Sat_Night column is being updated when i execute the query. Please advice..

declare @Weekday bit, @hour int

select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

Update Weekly set
Sat_Night=CASE WHEN (@Weekday= 1 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Sun_Day=CASE WHEN (@Weekday= 1 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Sun_Night=CASE WHEN (@Weekday= 2 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Mon_Day=CASE WHEN (@Weekday= 2 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Mon_Night=CASE WHEN (@Weekday= 3 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Tue_Day=CASE WHEN (@Weekday= 3 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Tue_Night=CASE WHEN (@Weekday= 4 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Wed_Day=CASE WHEN (@Weekday= 4 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Wed_Night=CASE WHEN (@Weekday= 5 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Thu_Day=CASE WHEN (@Weekday= 5 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Thu_Night=CASE WHEN (@Weekday= 6 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Fri_Day=CASE WHEN (@Weekday= 6 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END,

Fri_Night=CASE WHEN (@Weekday= 7 and (@hour> 7 AND @hour<=19))

THEN ALD.EngTime ELSE NULL END,

Sat_Day=CASE WHEN (@Weekday= 7 and (@hour > 18 AND @hour < 7))

THEN ALD.EngTime ELSE NULL END
from ALD join Weekly on Weekly.TesterID = ALD.TesterID

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-07 : 21:21:42

I haven't completely worked through the code, but I can see right away that your ranges have overlaps and gaps.

For Sat_Night you have @hour <= 19, but then for Sun_Day you have @hour > 18, which includes 19, so that overlaps the <= 19 from Sat_Night.

For Sat_Night you have @hour > 7, but then for Sun_Day you have @hour < 7, so where is 7 going to go?

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-07 : 22:07:18

oh ya i didnt notice that, but even after i change the time as below im still getting my sat_night column filled not mon_night column..Help please..Urgent.

declare @Weekday bit, @hour int

select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

Update Weekly set

Sat_Night=CASE WHEN (@Weekday= 1 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Sun_Day=CASE WHEN (@Weekday= 1 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Sun_Night=CASE WHEN (@Weekday= 2 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Mon_Day=CASE WHEN (@Weekday= 2 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Mon_Night=CASE WHEN (@Weekday= 3 and (@hour>= 7 or @hour<19))
THEN ALD.EngTime ELSE NULL END,

Tue_Day=CASE WHEN (@Weekday= 3 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Tue_Night=CASE WHEN (@Weekday= 4 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Wed_Day=CASE WHEN (@Weekday= 4 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Wed_Night=CASE WHEN (@Weekday= 5 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Thu_Day=CASE WHEN (@Weekday= 5 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Thu_Night=CASE WHEN (@Weekday= 6 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Fri_Day=CASE WHEN (@Weekday= 6 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,

Fri_Night=CASE WHEN (@Weekday= 7 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Sat_Day=CASE WHEN (@Weekday= 7 and (@hour >= 19 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END

from ALD join Weekly on Weekly.TesterID = ALD.TesterID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 00:54:05

How can Hour variable be both greater than or equal to 19 AND less than 7 simultaneously?

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-08 : 01:41:38

even if i change AND to OR command the result is still the same

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 03:55:02

Not all comparisons should be OR's.

Fri_Night=CASE WHEN (@Weekday= 7 and (@hour>= 7 AND @hour<19))
THEN ALD.EngTime ELSE NULL END,

Sat_Day=CASE WHEN (@Weekday= 7 and (@hour >= 19 OR @hour < 7))
THEN ALD.EngTime ELSE NULL END

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-08 : 04:34:49

ya thats what i did and the results doesnt change..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 04:39:51

Maybe you should declare @weekday as tinyint instead of BIT?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-08 : 05:00:36

the datepart 'dw' is dependant on what you have set for @@datefirst

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 05:03:41

quote:
Originally posted by elancaster

the datepart 'dw' is dependant on what you have set for @@datefirst

The only difference is that you interpret a date with wrong weekday, but the weekday should be there anyway.

But if you declare the variable @weekday as bit, the only to possible values (besides null) is 1 and 0.
Weekday 0 is stored as 0 which is correct, but all other weekdays (1-6) are stored as 1 using BIT.

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-08 : 21:35:59

Thanks guys.. It works when i changed BIT to tinyInt. Now I need do a little addition in Mon_Day case, Before it updates the new data on that day, I want it to clear the entire table and then start to fill the column as usual for the next 7 days. I used the following code to delete the entire table and fill in back the TesterIDs, but i dont know how and where to put the code correctly.thanks..

Delete from Weekly

Insert Into Weekly(TesterID)
Select TesterID
FROM ALD where TesterID IN ('CMT32' , 'CMT31' , 'CMT02','CMT33','CMT04','CMT36','CMT37','CMT38','CMT03','CMT05','CMT42','CMT34','CMT41','CMT40','CMT29','CMT30','CMT01','CMT39','CMT35')

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-08 : 21:41:36

Hi, i think there is a problem in the query, I didnt notice this for the previous post i posted. The columns are updated correctly but it deletes other columns data as well. When I execute now, it fills Tue_Night column, but when I change the time of my pc to 9pm, it is filling Wed_Day column as I want but it is clearing Tue_Night column as well. How to fix this?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-08 : 23:18:20

You need to add a WHERE clause that specifies what rows to update, you are using CASEs to specify how to selectively update the columns, but every single row is being updated.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-08 : 23:30:45

Can you give example where i should do that please...

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-09 : 03:53:18

Can anyone please help me on this.Urgent..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 03:58:21

Maybe because you say/write "DELETE other columns".
They are not "deleted", rather than UPDATED to NULL?

Please be more specific about the problem you are having. If you can't specify what is wrong, how do you expect us to help?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 03:59:07

Try to change "ELSE NULL" to "ELSE {the column name being updated here} END".

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-09 : 05:37:23

Now it works.How do I get the average of the columns once they are updated and then insert the average value of each columns into another table called AverageEngTime at column named Average. I guess I can use the following command but i dont know where to put it correctly in each cases. My AverageEngTime table consist of 2 columns, "Shifts" and "Average". Advice please..

Insert Into AverageEngTime(Average)
SELECT AVG(Sat_Night)
FROM `Weekly` where Shifts = 'Sat_Night'

Update Weekly set

Sat_Night=CASE WHEN (@Weekday= 1 and (@hour>= 7 AND @hour<19))

THEN ALD.EngTime ELSE Sat_Night END,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:40:20

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources