Basic Question on Join

Hi,

I'm pretty much a database beginner and have what I think is a basic
question:

If I have a table which has a 'status' column and I can have say three
statuses: "active", "pending", "inactive". Is it better to break these
out into a different "Statuses" table?

On the one hand it seems to me it would be better to be in a different
table since I'll index this column because I'll be in a where clause
sometimes and I think indexing it would be easier than indexing the
actual words. On the other hand, are joins going to slow queries down
too much? I read something about creating my own sql server 2000
datatype - would this be a good place to do such a thing?

Thanks for your help. (And if you could provide a reason why one
solution is better than the other so I can start understanding better,
I'd be very appreciative.)

Thanks.nick wrote:
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)
> Thanks.

If you're storing addresses should you have 50 separate tables, one for
each state?

Zach|||"nick" <nickgieschen@.hotmail.com> wrote in message
news:305cab98.0411191325.29e48efc@.posting.google.c om...
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)

Having a seperate lookup table may give you a better performance, testing is
the best way to determine. The lookup table could have an numeric value
that corresponds to active, pending, inactive and then you store the numeric
value in your main table. You wouldn't necessarily have to join to the
lookup table, you could just say WHERE Status = 1.

Using lookup tables can be more flexible for front-end coding because if
you have additions to your lookup choices you can simply edit the table
rather than digging around in the front-end code.

I wouldn't think a user-defined datatype would be useful for this.

hth|||nick (nickgieschen@.hotmail.com) writes:
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?

Basic question? It is actually one that I run into when I'm database design
every now and when, and the answer is not always the same. Mainly it is a
judgement's call. But these days, I tend to go for the table.

I like to first point that when I decide whether to add a table, or to
just have a column with some distinct values, performance has none to with
that decision. More important is the aspect of maintenance, the possibility
to present the value, extensibility and whether the value is important
enough to deserve a table.

Having a single column like:

status chat(1) NOT NULL CHECK (status IN ('A', 'P', 'I'))

has the advantage that it's up little space in the documentation, and
so easier to grasp. But if the value is to be presented in a GUI, the
GUI gets responsible for doing the translation (or the user gets to see
A, P and I. Another issue, is whether you need to add a new value. For
various reasons when we change tables, our standard routine is to take
the long way and reload the data into a new table. A bit expensive for
a changed CHECK constraint.

If you have a column with a lookup-table like:

status char(1) NOT NULL REFERENCES statusvalues (status)

this is more work for me initially, because I have to create an INSERT-
file with the statusvalues, and there is one more table in the database.
(In our case actually two, because there would also be a name table
that gives translations in different languages.). But if I later need
to add a new value, it's just a matter of changing that INSERT-file and
no need to reload the table. And with all strings in the database, the
GUI gets a simpler job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Basic Query: Alternatives to Group By for nText column

I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

http://64.225.154.232/temp_dbDiagram.gif

I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.

SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESCTry to cast youe text column to varchar(1000).|||What if the nText-field contains more than 1000 characters? What if it contains more than 4000 characters which I believe is the limit for nVarChar? I have this very same situation and am yet to find a solution...

-Tuukka

Basic formatting questions

I have two questions concerning formatting output in SQL.

I have a processer speed column that I would like to make usable. A sample entry is 2793, which I would like to be returned as '2.8 GHz'. I am using the following successfully, but when I try to append the ' GHz' it gives me a conversion error (I have tried putting both a cast and convert around the round statement).
ROUND(CONVERT(DECIMAL,PCCPUSpeed_IN) / 1000,1) + ' GHz'

Secondly, I have a RAM colum that stores values such as 534753280. I would like the results to fallin into 64 Mb buckets. So values between 512000 & 532480 should return '512 Mb'. I realize I can do this using a CASE statement, but is there a better way?

Thanks,

RobAll formatting really ought to be done on the client or at least in the middle tier. Doing formatting in the database is suicidal from both a performance and a scaling perspective in the long term.

-PatP

Bar width

My question:
- How can I change the width of the bars in a simple column chart diagram
generally?
- I've got the problem that all my bars look like lines.Hi,
Hope it can help...
you can use the "PointWidth" element
(see following :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSRDL/htm/rsp_ref_rdl_elements_fp_87lg.asp)
good luck
"Hawkeye" wrote:
> My question:
> - How can I change the width of the bars in a simple column chart diagram
> generally?
> - I've got the problem that all my bars look like lines.
>

Bar Chart with trendline

Is there a way to have a column bar chart and have a moving average or
trendline inlcuded in the chart? Right now I have a simple column bar chart
which displays number of orders on the y axis and time of day on the x axis.
I have been requested to add a trendline for number of orders on the chart.
--
Thanks,
EricOne solution is you could add the Trend or Average figures to your Bar Graph
and then change the style of this data series to be a line as follows:
1) Select properties on on the Chart.
2) Click on the Data tab.
3) Choose the Trend/Average data series under Values:
4) Click on Edit... button.
5) Click on the Appearance tab.
6) Click on 'Plot data as line' tick box.
Obviously you will need to calculate your trend/average as part of your data
retrieval. I normally write Table-value functions within the SQL
Server/Oracle database to return the data and allow me to perform these sort
of calculations.
"EricJ" wrote:
> Is there a way to have a column bar chart and have a moving average or
> trendline inlcuded in the chart? Right now I have a simple column bar chart
> which displays number of orders on the y axis and time of day on the x axis.
> I have been requested to add a trendline for number of orders on the chart.
> --
> Thanks,
> Eric

bar chart problem

I have read through all posts regarding 'bar chart" or "column chart", but
did not find simliar situation which would resolve my problem, although it
looks simple.
A table/dataset cotains two columns, first ProductID, second
SalesAmountPercentageChange. I linked the bar chart with the dataset, I added
in "values" =!Field.SalesAmountPercentageChange, I added in "series groups"
=!Fields.ProductID. This way, I can have the bars displayed in different
color, which would be the same color if I add the !Fileds.ProductID into
"Category group".
Now the trouble is this, although the SalesAmoutPercentageChange is SAME for
different ProductID ( I have 5 product with 39% change for each) as designed,
the bar length VARIES (!!!) for different ProductID, which does not make
sense. Also, there are five 39% change corresponding to 5 products spread out
along the horizontal-axis (in my opinion should be named as X-axis, yet funny
enough it is actually Y-axis in RS).
I am quite confused. I tried many different combinations, turning things on
and off, still can not make it work. Any help will be greatly appreciated.
Also, I am trying to add into the bar chart another element
"TotalPercentageChange", not sure how to do that. It looks I can not simply
add a value2 on top of the value "!Field.SalesAmountPercentageChange". I
figured that it is necessary to pass a new dataset into chart, this new
dataset would contain the total + original table. Total is something
accessible in the report scope, the question is, where does it reside, how
can I use select statement to pick it out and combine it with original table
into a new one?
$10 for solving this problem, just give me your paypal account :).
--
SincerelyDid you get this resolved?
If not, can you post your RDL? In addition, if you have SP1 installed, there
should be a data cache file (.rdl.data) in the same directory as your .rdl
file. The .rdl.data would show the actual data values you are using.
Alternatively, it would help if you can reproduce the issue with the sample
databases (Northwind or Adventure Works) and post the corresponding RDL
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:247C97F2-9783-45C2-8ACA-E085075AFBDE@.microsoft.com...
> I have read through all posts regarding 'bar chart" or "column chart", but
> did not find simliar situation which would resolve my problem, although it
> looks simple.
> A table/dataset cotains two columns, first ProductID, second
> SalesAmountPercentageChange. I linked the bar chart with the dataset, I
added
> in "values" =!Field.SalesAmountPercentageChange, I added in "series
groups"
> =!Fields.ProductID. This way, I can have the bars displayed in different
> color, which would be the same color if I add the !Fileds.ProductID into
> "Category group".
> Now the trouble is this, although the SalesAmoutPercentageChange is SAME
for
> different ProductID ( I have 5 product with 39% change for each) as
designed,
> the bar length VARIES (!!!) for different ProductID, which does not make
> sense. Also, there are five 39% change corresponding to 5 products spread
out
> along the horizontal-axis (in my opinion should be named as X-axis, yet
funny
> enough it is actually Y-axis in RS).
> I am quite confused. I tried many different combinations, turning things
on
> and off, still can not make it work. Any help will be greatly appreciated.
> Also, I am trying to add into the bar chart another element
> "TotalPercentageChange", not sure how to do that. It looks I can not
simply
> add a value2 on top of the value "!Field.SalesAmountPercentageChange". I
> figured that it is necessary to pass a new dataset into chart, this new
> dataset would contain the total + original table. Total is something
> accessible in the report scope, the question is, where does it reside,
how
> can I use select statement to pick it out and combine it with original
table
> into a new one?
> $10 for solving this problem, just give me your paypal account :).
>
>
> --
> Sincerely|||If the <Min></Min> element is missing in the RDL file, then the chart will
use the "autoscale" mode. I.e. the minimum value of all datapoints in the
chart will become the minimum value for the y-axis.
If a Min element is present (but the content is not a valid number), then
the chart control will not use the autoscale mode.
Note: removing a previously specified Min-value in the chart properties
dialog in report designer will only remove the Min element in the underlying
RDL file if the Min-field in the dialog is completely cleared out before
hitting OK.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:E59D78EF-7B4C-4B33-8C3E-3C5D649674DF@.microsoft.com...
> This is an absolute bug. I digged more.
> I made a new chart, adding data set, then value, then series, and it
works,
> But the old chart with problem is still there. I compared the xml section
for
> these two charts side by side, line by line.
> I found only one line of difference, the working chart's rdl has <Min>
> </Min>, the other does not. I went to the report layout and accessed the
> chart property of not working one. in "Y axis" --> "Scale" --> "minimum",
I
> typed in a space, (any other character works two), then it works. all bar
> length goes back to normal. If I do not type anything, then it won't work.
> I believe under the hood, there is no default setting for min, and this
> causes serious problem.
> I am going to reproduce this problem in Northwind db and post the rdl. I
> hope it will be reproduced soon.
> By far, this is a bug in bar chart, I am pretty sure.
>
> "Robert Bruckner [MSFT]" wrote:
> > Did you get this resolved?
> > If not, can you post your RDL? In addition, if you have SP1 installed,
there
> > should be a data cache file (.rdl.data) in the same directory as your
.rdl
> > file. The .rdl.data would show the actual data values you are using.
> > Alternatively, it would help if you can reproduce the issue with the
sample
> > databases (Northwind or Adventure Works) and post the corresponding RDL
> > file.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
> > news:247C97F2-9783-45C2-8ACA-E085075AFBDE@.microsoft.com...
> > >
> > > I have read through all posts regarding 'bar chart" or "column chart",
but
> > > did not find simliar situation which would resolve my problem,
although it
> > > looks simple.
> > >
> > > A table/dataset cotains two columns, first ProductID, second
> > > SalesAmountPercentageChange. I linked the bar chart with the dataset,
I
> > added
> > > in "values" =!Field.SalesAmountPercentageChange, I added in "series
> > groups"
> > > =!Fields.ProductID. This way, I can have the bars displayed in
different
> > > color, which would be the same color if I add the !Fileds.ProductID
into
> > > "Category group".
> > >
> > > Now the trouble is this, although the SalesAmoutPercentageChange is
SAME
> > for
> > > different ProductID ( I have 5 product with 39% change for each) as
> > designed,
> > > the bar length VARIES (!!!) for different ProductID, which does not
make
> > > sense. Also, there are five 39% change corresponding to 5 products
spread
> > out
> > > along the horizontal-axis (in my opinion should be named as X-axis,
yet
> > funny
> > > enough it is actually Y-axis in RS).
> > >
> > > I am quite confused. I tried many different combinations, turning
things
> > on
> > > and off, still can not make it work. Any help will be greatly
appreciated.
> > >
> > > Also, I am trying to add into the bar chart another element
> > > "TotalPercentageChange", not sure how to do that. It looks I can not
> > simply
> > > add a value2 on top of the value "!Field.SalesAmountPercentageChange".
I
> > > figured that it is necessary to pass a new dataset into chart, this
new
> > > dataset would contain the total + original table. Total is something
> > > accessible in the report scope, the question is, where does it reside,
> > how
> > > can I use select statement to pick it out and combine it with original
> > table
> > > into a new one?
> > >
> > > $10 for solving this problem, just give me your paypal account :).
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Sincerely
> >
> >
> >

Baffled! Cant figure out how to do this query. Is it even possible?

I have an "Issues" table for my technicians. An issue can be on "hold"
or "assigned".
I want to get a count for each tech with a column showing number of
issues on hold and a column for number of issues assigned. It would
look like this --

Tech Num_Assigned Num_On_Hold
Fred 3 10
Carol 6 7

I can get each column separately, but I want both in the same answer
table!
Is that too much to ask? :)You need to do a self join on the table. Without your table
definition, it would be something like this

SELECT TI1.Tech,
Num_Assigned = COUNT(TI1.TechID),
Num_On_Hold = COUNT(TI2.TechID)
FROM TechIssues TI1, TechIssues TI2
WHERE TI1.TechID = TI2.TechID
GROUP BY TI1.Tech

Now, the above assumes that all techs have issues assigned AND issues
on HOLD. You'd need to UNION a couple more of these to handle where
Techs have records assigned but not on hold and vice versa. But this
should get you started.

Hope it helps
Teresa Masino|||jonescv@.gw.ccsd.net wrote:
> I have an "Issues" table for my technicians. An issue can be on "hold"
> or "assigned".
> I want to get a count for each tech with a column showing number of
> issues on hold and a column for number of issues assigned. It would
> look like this --
> Tech Num_Assigned Num_On_Hold
> Fred 3 10
> Carol 6 7
>
> I can get each column separately, but I want both in the same answer
> table!
> Is that too much to ask? :)

Here's a guess:

SELECT tech,
COUNT(CASE WHEN status = 'assigned' THEN 1 END),
COUNT(CASE WHEN status = 'hold' THEN 1 END)
FROM your_table
GROUP BY tech ;

--
David Portas
SQL Server MVP
--