inserting dummy variables

[lagazetta]

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

 

[monksnake]

There has to be a better way get your end result than the way you want to go about it. With all those extra columns and 23 of each being null, that is not very normalized.

What exactly are you trying to do?

<.

 

[SQLDenis]

let me get this straight...you will have 23 unused columns in every row? don't you think that this is BAD design to say it mildly? to store 1 values you will use 24 *1 byte + the null allow flag overhead

What a waste of IO and space!!!!!

why don't you store the value only and then use a CASE statement in the SELECT part to show it (PIVOT)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[monksnake]

Do you want to find out how many rows of each hour you have?

<.

 

[bborissov]

UPDATE MyTable
       SET H1 = CASE WHEN DATEPART(hh,Date_Value) = 1
                     THEN 1
                     ELSE 0 END,
...
       SET H13 = CASE WHEN DATEPART(hh,Date_Value) = 13
                     THEN 1
                     ELSE 0 END,
...
       SET H24 = CASE WHEN DATEPART(hh,Date_Value) = 24
                     THEN 1
                     ELSE 0 END

Of course this tests only for hour part, I am not sure if you want these fields to be 1 ONLY when the time is full:
01:00:00 and if the time is 01:00:01 to be zero.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[lagazetta]

i would like to run a regression at the end if I can complete the table in the way that I described.

i want to test whether an hour of the day has a significant effect on my dependent variable. the most convenient to do this is to define a dummy variable that takes the value 1 if the observation has some attribute or characteristic, and the value 0 otherwise.

therefore in dates where I am at 1:00:00 AM, I will put a "1" for h1 column, and put "0"'s (not nulls) on the other hour columns (h2,h3,....h00)

was i able to make my point clearer?

 

[SQLDenis]

>>was i able to make my point clearer?

I know what you want and thew code that Boris gave you will do that
However this design is completely nonsense

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[lagazetta]

thanks bborissov, I am trying your code and hopefully it works.

and,thanks SQLdenis for your comments.

whether or not this design is non-sense is out of the question here. econometric theory suggests that this should be done and time/resource efficiency is not something that is targeted here.

 

[onpnt]

whether or not this design is non-sense is out of the question here. econometric theory suggests that this should be done and time/resource efficiency is not something that is targeted here.

I'm saving that one for any and every job interview I have from this day on. I'm going to ask the company if this is how they work. If they say yes thank god I'll be able to get out before working with them

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[monksnake]

econometric theory suggests that this should be done and time/resource efficiency is not something that is targeted here.

That is what is wrong with today's society.


<.

 

[SQLDenis]

Isn't this better???

create table SomeTable (SomeHour int)


insert SomeTable values(datepart(hh,'2007-05-21 16:05:52.043'))
insert SomeTable values(datepart(hh,'2007-05-21 15:05:52.043'))
insert SomeTable values(datepart(hh,'2007-05-21 16:05:52.043'))
insert SomeTable values(datepart(hh,'2007-05-21 15:05:52.043'))
insert SomeTable values(datepart(hh,'2007-05-21 14:05:52.043'))
insert SomeTable values(datepart(hh,'2007-05-21 13:05:52.043'))

create index ix_hour on SomeTable (SomeHour)

then create this view

create view SomeView
as
select case SomeHour when 1 then 1 else 0 end Hour1,
 case SomeHour when 2 then 1 else 0 end Hour2,
 case SomeHour when 3 then 1 else 0 end Hour3,
 case SomeHour when 4 then 1 else 0 end Hour4,
 case SomeHour when 5 then 1 else 0 end Hour5,
 case SomeHour when 6 then 1 else 0 end Hour6,
 case SomeHour when 7 then 1 else 0 end Hour7,
 case SomeHour when 8 then 1 else 0 end Hour8,
 case SomeHour when 9 then 1 else 0 end Hour9,
 case SomeHour when 10 then 1 else 0 end Hour10,
 case SomeHour when 11 then 1 else 0 end Hour11,
 case SomeHour when 12 then 1 else 0 end Hour12,
 case SomeHour when 13 then 1 else 0 end Hour13,
 case SomeHour when 14 then 1 else 0 end Hour14,
 case SomeHour when 15 then 1 else 0 end Hour15,
 case SomeHour when 16 then 1 else 0 end Hour16,
 case SomeHour when 17 then 1 else 0 end Hour17,
 case SomeHour when 18 then 1 else 0 end Hour18,
 case SomeHour when 19 then 1 else 0 end Hour19,
 case SomeHour when 20 then 1 else 0 end Hour20,
 case SomeHour when 21 then 1 else 0 end Hour21,
 case SomeHour when 22 then 1 else 0 end Hour22,
 case SomeHour when 23 then 1 else 0 end Hour23,
 case SomeHour when 24 then 1 else 0 end Hour24
from SomeTable

then select from the view at least you will store a lot less in the table

select * from SomeView
where Hour16 =1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[lagazetta]

don't forget to ask them what they are doing and why they are doing it and what their primary objective is. then you may get a better offer.

i use sql for data manipulation purposes and feed my econometric software, not for executing queries in 5 seconds rather than 4 seconds.

 

[AlexCuse]

lagazetta -

All that econometric theory is concerned with is how the data is presented for statistical analysis. This does not necessarily mean it needs to be stored in your database that way.

What Denis has suggested is you store the value of the hour in one column, and then 'flatten' this to your byte column in a query that presents the information to <SAS, whatever> for analysis.

COnsider this example (I'm only doing five hours for simplicity's sake)

[COLOR=blue]declare[/color] @le [COLOR=blue]Table[/color] (fName [COLOR=blue]varchar[/color](10), mHour [COLOR=blue]tinyint[/color])

[COLOR=blue]insert[/color] @le
[COLOR=blue]select[/color] [COLOR=red]'Alex'[/color], 5
union all [COLOR=blue]select[/color] [COLOR=red]'Denis'[/color], 3
union all [COLOR=blue]select[/color] [COLOR=red]'Boris'[/color], 1
union all [COLOR=blue]select[/color] [COLOR=red]'LaGazeta'[/color], 1

[COLOR=green]--this is how it is stored
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] @le

[COLOR=green]--but you can present it like this and
[/color][COLOR=green]--even store the query as a view, which is treated like a table
[/color][COLOR=blue]select[/color] fName
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] mHour = 1 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color] [COLOR=blue]as[/color] Hour1
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] mHour = 2 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color] [COLOR=blue]as[/color] Hour2
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] mHour = 3 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color] [COLOR=blue]as[/color] Hour3
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] mHour = 4 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color] [COLOR=blue]as[/color] Hour4
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] mHour = 5 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color] [COLOR=blue]as[/color] Hour5
[COLOR=blue]from[/color] @le

Looking at that query it should become immediately clear that you can store your data properly (according to database theory) AND analyze it properly (according to econometric theory). The two need not be mutually exclusive.

If you are going to be working with databases a lot, I suggest you do a little reading from here:

http://www.r937.com/relational.html

This will teach you the basics of normalization, and you will find them very valuable to know.

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

i use sql for data manipulation purposes and feed my econometric software, not for executing queries in 5 seconds rather than 4 seconds.

Have you considered that the advice you are being given isn't simply for speed? It's fairly obvious from this and your previous questions on this forum that you are a beginner to both the SQL language and SQL Server, whereas the people who are offering you the advice have been doing this for a number of years and really know their stuff. They aren't offering advice for the sake of it, they simply know what problems you (or any other employee who has to deal with this structure now or in the future) will face if you continue down your chosen route. You would do well to listen to this advice, read up on the terms described to you and more importantly act upon it.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.