inserting dummy variables - 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 2005 Forums

Transact-SQL (2005)

inserting dummy variables

Author

Topic

shaw
Starting Member

15 Posts

Posted - 2007-05-21 : 15:12:49

hi there,

my table looks like:

prime-------date_value----------------h1-----h2---h3........ h24
1-----------7/1/2001 1:00:00 AM------null---null--null......null
2-----------7/1/2001 2:00:00 AM------null---null---null.....null
3
4
5
6
7
8
.
.
.
.
.
30720

I would like to insert the number "1" under the column h1 if my date value includes 1:00:00 AM. So for every date (regardless of year,month and day) where I am at 1:00:00 am, I want to put 1 in h1 column and zeros to the h2, h3,h4,..h24 colums.

By the same logic, i would like to insert "1" under the column h2 if my date value includes 2:00:00 AM and zeros to the h1, h3, h4,...h24.

Any ideas how to proceed?

thanks

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 15:15:33

If I've understood you correctly it sounds horrific!

Why not Compute the values for h1, ... h24, when you Query the database, rather than actually STORING them?

Kristen

pvedi
Starting Member

6 Posts

Posted - 2007-05-21 : 16:15:51

This might help to rearchitect your Table/Query
Example Code:
Select Datepart(hh, getDate()) Hour

shaw
Starting Member

15 Posts

Posted - 2007-05-21 : 17:55:04

hi kristen,
i need to store all those zeros to capture an effect of the hours and i realize it is a waste of space.

i did the following:

UPDATE dumy
SET H1 = CASE WHEN DATEPART(hh,Date_Value) = 1
THEN 1
ELSE 0 END,

SET H2 = CASE WHEN DATEPART(hh,Date_Value) = 2
THEN 1
ELSE 0 END,

SET H3 = CASE WHEN DATEPART(hh,Date_Value) = 3
THEN 1
ELSE 0 END,

SET H4 = CASE WHEN DATEPART(hh,Date_Value) = 4
THEN 1
ELSE 0 END

SET H5 = CASE WHEN DATEPART(hh,Date_Value) = 5
THEN 1
ELSE 0 END
SET H6 = CASE WHEN DATEPART(hh,Date_Value) = 6
THEN 1
ELSE 0 END
SET H7 = CASE WHEN DATEPART(hh,Date_Value) = 7
THEN 1
ELSE 0 END
SET H8 = CASE WHEN DATEPART(hh,Date_Value) = 8
THEN 1
ELSE 0 END
SET H9 = CASE WHEN DATEPART(hh,Date_Value) = 9
THEN 1
ELSE 0 END
SET H10 = CASE WHEN DATEPART(hh,Date_Value) = 10
THEN 1
ELSE 0 END
SET H11 = CASE WHEN DATEPART(hh,Date_Value) = 11
THEN 1
ELSE 0 END
SET H12 = CASE WHEN DATEPART(hh,Date_Value) = 12
THEN 1
ELSE 0 END
SET H13 = CASE WHEN DATEPART(hh,Date_Value) = 13
THEN 1
ELSE 0 END
SET H14 = CASE WHEN DATEPART(hh,Date_Value) = 14
THEN 1
ELSE 0 END
SET H15 = CASE WHEN DATEPART(hh,Date_Value) = 15
THEN 1
ELSE 0 END
SET H16 = CASE WHEN DATEPART(hh,Date_Value) = 16
THEN 1
ELSE 0 END
SET H17 = CASE WHEN DATEPART(hh,Date_Value) = 17
THEN 1
ELSE 0 END
SET H18 = CASE WHEN DATEPART(hh,Date_Value) = 18
THEN 1
ELSE 0 END
SET H19 = CASE WHEN DATEPART(hh,Date_Value) = 19
THEN 1
ELSE 0 END
SET H20 = CASE WHEN DATEPART(hh,Date_Value) = 20
THEN 1
ELSE 0 END
SET H21 = CASE WHEN DATEPART(hh,Date_Value) = 21
THEN 1
ELSE 0 END
SET H22 = CASE WHEN DATEPART(hh,Date_Value) = 22
THEN 1
ELSE 0 END
SET H23 = CASE WHEN DATEPART(hh,Date_Value) = 23
THEN 1
ELSE 0 END
SET H00 = CASE WHEN DATEPART(hh,Date_Value) = 00
THEN 1
ELSE 0 END

but i have AM's and PM's rather than 13, 14, 15's. so above would not work i guess.
i could not see it because it gave an error:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SET'.

i could not see why.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 18:37:07

shaw -- you should post your questions in the "Beginning SQL" forum.

If your table has columns A and B, and you want to select from that table and also return a formula as column C, you just write:

select a,b, <some formula> as c
from yourtable

for example, to have column c be the sum of a + b, you write:

select a,b, a+b as c
from yourtable

There's no variables, and nothing to set. It just becomes another column in your result set.

- Jeff
http://weblogs.sqlteam.com/JeffS

shaw
Starting Member

15 Posts

Posted - 2007-05-21 : 21:49:25

hi jeff,

i just was unable to understand how your reply is in any way related to what I am asking in my first post.
i am neither trying to select columns from my table nor trying to sum my fields.
but thanks for your reply.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 22:04:27

I am trying to tell you that you do not need to update a table with these values, you can simply calculate them on-the-fly using a simple SELECT.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 01:46:44

"i could not see why"

You only need SET once, separate each assignment with a comma.


UPDATE MyTable
SET Col1 = ExpressionA,
    Col2 = ExpressionB

Are you trying to make a CrossTab/pivot table? If so see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=More+Dynamic+CrossTabs

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources