Data modelling question

Using SQL 7. I have a table with 2 fields in it that I wish to relate
to a lookup table. DDL for table 1:

CREATE TABLE [dbo].[tblPedometerReadings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReading] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity1Minutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity2Minutes] [smallint] NULL
) ON [PRIMARY]

DDL for table 2:

CREATE TABLE [dbo].[tlkpOtherActivities] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[EquivalentSteps] [smallint] NOT NULL
) ON [PRIMARY]

In my database diagram, I want to create a 1-to-many relationship
between tblPedometerReadings.OtherActivity1 and
tlkpOtherActivities.OtherActivity AND between
tblPedometerReadings.OtherActivity2 and
tlkpOtherActivities.OtherActivitiy. I get a long error message when
attempting the relationship between OtherActivity2 and OtherActivity.
Is there another schema that would work better?

Thanks.(manning_news@.hotmail.com) writes:
> Using SQL 7. I have a table with 2 fields in it that I wish to relate
> to a lookup table. DDL for table 1:
>
> CREATE TABLE [dbo].[tblPedometerReadings] (
> [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReadingDate] [datetime] NULL ,
> [PedometerReading] [smallint] NULL ,
> [OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity1Minutes] [smallint] NULL ,
> [OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity2Minutes] [smallint] NULL
> ) ON [PRIMARY]
> DDL for table 2:
> CREATE TABLE [dbo].[tlkpOtherActivities] (
> [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [EquivalentSteps] [smallint] NOT NULL
> ) ON [PRIMARY]

Which version of SQL Server are you really using? The above scripts
does not work in SQL7 - I can tell that from the use of COLLATE
clauses.

> In my database diagram, I want to create a 1-to-many relationship
> between tblPedometerReadings.OtherActivity1 and
> tlkpOtherActivities.OtherActivity AND between
> tblPedometerReadings.OtherActivity2 and
> tlkpOtherActivities.OtherActivitiy. I get a long error message when
> attempting the relationship between OtherActivity2 and OtherActivity.
> Is there another schema that would work better?

Designwise it sounds OK (save that varchar(50) is a tad long for a
key value). If I understand you right, you get this error message
when using the diagram functionality in Enterprise Manager? I don't
use that tool, but it would somewhat easier to say anything useful,
if you could post the error message.

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

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

Here's the error message:

- Unable to create relationship
'FK_tblPedometerReadings_tlkpOtherActivities1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
on table 'tblPedometerReadings' may cause cycles or multiple cascade
paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
other FOREIGN KEY constraints.

OtherActivity can be "Biking-Hard", "Swimming-Easy", things like
that... If the customer doesn't make it clear what data will be
entered, then I leave it at varchar(50) until I get a better idea. I'm
toying with the idea of assigning an ID # to the activity and making
that the key.

Is a reflexive relationship what I need here?

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > Using SQL 7. I have a table with 2 fields in it that I wish to
relate
> > to a lookup table. DDL for table 1:
> > CREATE TABLE [dbo].[tblPedometerReadings] (
> > [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ReadingDate] [datetime] NULL ,
> > [PedometerReading] [smallint] NULL ,
> > [OtherActivity1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity1Minutes] [smallint] NULL ,
> > [OtherActivity2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity2Minutes] [smallint] NULL
> > ) ON [PRIMARY]
> > DDL for table 2:
> > CREATE TABLE [dbo].[tlkpOtherActivities] (
> > [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [EquivalentSteps] [smallint] NOT NULL
> > ) ON [PRIMARY]
> Which version of SQL Server are you really using? The above scripts
> does not work in SQL7 - I can tell that from the use of COLLATE
> clauses.
> > In my database diagram, I want to create a 1-to-many relationship
> > between tblPedometerReadings.OtherActivity1 and
> > tlkpOtherActivities.OtherActivity AND between
> > tblPedometerReadings.OtherActivity2 and
> > tlkpOtherActivities.OtherActivitiy. I get a long error message
when
> > attempting the relationship between OtherActivity2 and
OtherActivity.
> > Is there another schema that would work better?
> Designwise it sounds OK (save that varchar(50) is a tad long for a
> key value). If I understand you right, you get this error message
> when using the diagram functionality in Enterprise Manager? I don't
> use that tool, but it would somewhat easier to say anything useful,
> if you could post the error message.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(manning_news@.hotmail.com) writes:
> Using SQL2000.
> Here's the error message:
> - Unable to create relationship
> 'FK_tblPedometerReadings_tlkpOtherActivities1'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
> on table 'tblPedometerReadings' may cause cycles or multiple cascade
> paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
> other FOREIGN KEY constraints.

You never said that you had chosen cascading delete/update for the
relationship. You will be able to define the constraint, if you select
NO ACTION. (Which means that an attempt to delete a referenced row
in tlkpOtherActivities will result in an error.)

While SQL 2000 supports DELETE/UPDATE ON CASCADE on foreign keys,
there are many restrictions. In some cases they are necessary, in
some cases the SQL Server developers were a bit on the conservative
side when they disallow cascading.

I don't use cascading DRI myself, so I have not dug into the details.

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

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

Data Model: TypeCode Reference Tables

Hello World,
I have two two typcode reference tables with the following DDL:
CREATE TABLE [dbo].[LanguageType](
[Description] [varchar](255) NULL,
[LanguageTypeCode][char](6)NOT NULL
)
GO
ALTER TABLE dbo.LanguageType ADD CONSTRAINT
PK_LanguageType PRIMARY KEY CLUSTERED
(
LanguageTypeCode
)
GO
CREATE TABLE [dbo].[BookType](
[Description] [varchar](255) NULL,
[BookTypeCode] [char](6)NOT NULL
)
GO
ALTER TABLE dbo.BookType ADD CONSTRAINT
PK_BookType PRIMARY KEY CLUSTERED
(
BookTypeCode
)
GO
I need to create a template that has a referential constraints check against
the primary key of EITHER table above. For example,
ALTER TABLE dbo.Template ADD CONSTRAINT
PK_Template PRIMARY KEY CLUSTERED
(
TemplateID,
TypeCode -- Either LanguageTypeCode or BookTypeCode
)
What are the options that I have to implement this? I thought about
combining the Language and Book TypeCodes into one table, but we think that
would only complicate our data model. Please comment.Add a "parent" Table that has both TypeCoeds in it
Create Table TemplateTypeCode -- You can think of better name for this
abstraction
(TypeCode Char(6( Primary Key Not Null)
And alter both pf your other tables
Alter Table dbo.LanguageType
ADD CONSTRAINT FKTypeCode Foreign Key (LanguageTypeCode)
references TemplateTypeCode(TypeCode)
Alter Table dbo.BookType
ADD CONSTRAINT FKTypeCode Foreign Key (BookTypeCode)
references TemplateTypeCode(TypeCode)
Then create constraint on Template table agaist TemplateTypeCode Table
ALTER TABLE dbo.Template ADD CONSTRAINT
FK_TypeCodes (TypeCode)
references TemplateTypeCode(TypeCode)
"C TO" wrote:

> Hello World,
> I have two two typcode reference tables with the following DDL:
> CREATE TABLE [dbo].[LanguageType](
> [Description] [varchar](255) NULL,
> [LanguageTypeCode][char](6)NOT NULL
> )
> GO
> ALTER TABLE dbo.LanguageType ADD CONSTRAINT
> PK_LanguageType PRIMARY KEY CLUSTERED
> (
> LanguageTypeCode
> )
> GO
> CREATE TABLE [dbo].[BookType](
> [Description] [varchar](255) NULL,
> [BookTypeCode] [char](6)NOT NULL
> )
> GO
> ALTER TABLE dbo.BookType ADD CONSTRAINT
> PK_BookType PRIMARY KEY CLUSTERED
> (
> BookTypeCode
> )
> GO
> I need to create a template that has a referential constraints check again
st
> the primary key of EITHER table above. For example,
> ALTER TABLE dbo.Template ADD CONSTRAINT
> PK_Template PRIMARY KEY CLUSTERED
> (
> TemplateID,
> TypeCode -- Either LanguageTypeCode or BookTypeCode
> )
>
> What are the options that I have to implement this? I thought about
> combining the Language and Book TypeCodes into one table, but we think tha
t
> would only complicate our data model. Please comment.|||Thanks. Wouldn't this create more repeated data? How would you compare
creating a parent table with creating trigger to enforce referential
integrity?
"CBretana" wrote:
> Add a "parent" Table that has both TypeCoeds in it
> Create Table TemplateTypeCode -- You can think of better name for this
> abstraction
> (TypeCode Char(6( Primary Key Not Null)
> And alter both pf your other tables
> Alter Table dbo.LanguageType
> ADD CONSTRAINT FKTypeCode Foreign Key (LanguageTypeCode)
> references TemplateTypeCode(TypeCode)
> Alter Table dbo.BookType
> ADD CONSTRAINT FKTypeCode Foreign Key (BookTypeCode)
> references TemplateTypeCode(TypeCode)
> Then create constraint on Template table agaist TemplateTypeCode Table
> ALTER TABLE dbo.Template ADD CONSTRAINT
> FK_TypeCodes (TypeCode)
> references TemplateTypeCode(TypeCode)
>
> "C TO" wrote:
>|||A thing is either a type or a code, but not both. You also might want
to consider not blindly assigning columns a VARCHAR(255).
CREATE TABLE Languages
(language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
language_name CHAR(15) NOT NULL);
CREATE TABLE BookTypes
( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
categories?
book_description VARCHAR(25) NOT NULL);
against the primary key of EITHER table above. For example, ...
TypeCode -- Either LanguageTypeCode or BookTypeCode <<
No, you need a real relational design with well-defined attributes. It
is a total violation of just about every rule to have a column that
holds different attributes. That is less than First Normal Form.
table, but we think that would only complicate our data model. Please
comment. <<
That design flaw is called the "One True Lookup Table" and you can
Goolge for the painful details.
Here are some standards that might help:
1) If you need to know the language of a book, you can get it from the
fist two digits of the ISBN.
2) ISO 3166 standard reference with 2 letter, 3 letter and number
codes:
The standard ISO 639 provides two sets of language codes, one as a
two-letter code set (ISO 639-1:2002 Codes for the representation of
names of languages -- Part 1: Alpha-2 code) and another as a
three-letter code set (ISO 639-2:1998 Codes for the representation of
names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
also made available via the Web site of the ISO 639 Joint Advisory
Committee.|||CTO,
Creating a parent table is the way I'd go... You ARE creating repeated
data (copies) of the Keys, but that happens anytime you have a Foreign Key
to Primary Key Constraint. This technique is called "subclassing". It's
advantedge is that the FK On the Template table can use built in DRI
COnstraint which is better performance and cleaner than using Trigger...
"C TO" wrote:
> Thanks. Wouldn't this create more repeated data? How would you compare
> creating a parent table with creating trigger to enforce referential
> integrity?
>
> "CBretana" wrote:
>|||Hi CELKO,
Your input is really helpful and make me think harder. First of all, the
examples I provided related to Book and Language types were bad in this case
because language is an attribute of the book. But I really meant to provide
examples that would answer my question about "One True Lookup Table" and the
referential integrity enforment.
Secondly, I have heard you mentioned ISO before and felt very helpless
soemtimes because I did not how to get access to the codes. I've tried, many
of them required "placing an order in the shopping cart". BTW, in general,
how do I know which are accessible to the Public?
There were really book or language in this model, but I am interested in
using the universal, standard reference codes. Thanks.
"--CELKO--" wrote:

> A thing is either a type or a code, but not both. You also might want
> to consider not blindly assigning columns a VARCHAR(255).
> CREATE TABLE Languages
> (language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
> language_name CHAR(15) NOT NULL);
> CREATE TABLE BookTypes
> ( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
> categories?
> book_description VARCHAR(25) NOT NULL);
>
> against the primary key of EITHER table above. For example, ...
> TypeCode -- Either LanguageTypeCode or BookTypeCode <<
> No, you need a real relational design with well-defined attributes. It
> is a total violation of just about every rule to have a column that
> holds different attributes. That is less than First Normal Form.
>
> table, but we think that would only complicate our data model. Please
> comment. <<
> That design flaw is called the "One True Lookup Table" and you can
> Goolge for the painful details.
> Here are some standards that might help:
> 1) If you need to know the language of a book, you can get it from the
> fist two digits of the ISBN.
> 2) ISO 3166 standard reference with 2 letter, 3 letter and number
> codes:
> The standard ISO 639 provides two sets of language codes, one as a
> two-letter code set (ISO 639-1:2002 Codes for the representation of
> names of languages -- Part 1: Alpha-2 code) and another as a
> three-letter code set (ISO 639-2:1998 Codes for the representation of
> names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
> also made available via the Web site of the ISO 639 Joint Advisory
> Committee.
>|||Thanks. If that is the only common practice, then I would go that route too.
Talking about DRI, is that still available in SQL 2005?
"CBretana" wrote:
> CTO,
> Creating a parent table is the way I'd go... You ARE creating repeated
> data (copies) of the Keys, but that happens anytime you have a Foreign Ke
y
> to Primary Key Constraint. This technique is called "subclassing". It's
> advantedge is that the FK On the Template table can use built in DRI
> COnstraint which is better performance and cleaner than using Trigger...
> "C TO" wrote:
>|||Correction: I meant "there was not any book or language..."
"C TO" wrote:
> Hi CELKO,
> Your input is really helpful and make me think harder. First of all, the
> examples I provided related to Book and Language types were bad in this ca
se
> because language is an attribute of the book. But I really meant to provid
e
> examples that would answer my question about "One True Lookup Table" and t
he
> referential integrity enforment.
> Secondly, I have heard you mentioned ISO before and felt very helpless
> soemtimes because I did not how to get access to the codes. I've tried, ma
ny
> of them required "placing an order in the shopping cart". BTW, in general,
> how do I know which are accessible to the Public?
> There were really book or language in this model, but I am interested in
> using the universal, standard reference codes. Thanks.
>
>
> "--CELKO--" wrote:
>|||Hi CELKO,
I overlooked a point you pointed out about "A thing is either a type or a
code". Do you mean in general a type is usually a popular thing which can be
understood easily in a spoken/written language (e.g. Type of Language:
English, Spanish...) and the code is usually used for application only (e.g
.
EN, SP) ? So you were questioning why the column was named that way?
You further said, "You also might want to consider not blindly assigning
columns a VARCHAR(255)." What do you mean by that?
Say, I have a type of a thing that is not so common or not to have easily
standardized type, having a description column would allow further
explaination about that type. Why not?
"--CELKO--" wrote:

> A thing is either a type or a code, but not both. You also might want
> to consider not blindly assigning columns a VARCHAR(255).
> CREATE TABLE Languages
> (language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
> language_name CHAR(15) NOT NULL);
> CREATE TABLE BookTypes
> ( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
> categories?
> book_description VARCHAR(25) NOT NULL);
>
> against the primary key of EITHER table above. For example, ...
> TypeCode -- Either LanguageTypeCode or BookTypeCode <<
> No, you need a real relational design with well-defined attributes. It
> is a total violation of just about every rule to have a column that
> holds different attributes. That is less than First Normal Form.
>
> table, but we think that would only complicate our data model. Please
> comment. <<
> That design flaw is called the "One True Lookup Table" and you can
> Goolge for the painful details.
> Here are some standards that might help:
> 1) If you need to know the language of a book, you can get it from the
> fist two digits of the ISBN.
> 2) ISO 3166 standard reference with 2 letter, 3 letter and number
> codes:
> The standard ISO 639 provides two sets of language codes, one as a
> two-letter code set (ISO 639-1:2002 Codes for the representation of
> names of languages -- Part 1: Alpha-2 code) and another as a
> three-letter code set (ISO 639-2:1998 Codes for the representation of
> names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
> also made available via the Web site of the ISO 639 Joint Advisory
> Committee.
>|||>> I did not how to get access to the codes. I've tried, many of them
required "placing an order in the shopping cart". BTW, in general, how
do I know which are accessible to the Public? <<
ISO makes its money from sales, while ANSI makes its money from
membership fees. ISOP is therefore not as open about things as ANSI.
You have to Google around like crazy sometimes to find out about a
Standard if you do not know the ISO number or a good search phrase for
the title. I think that thre might be market for a pocket guidebook to
various standard codes -- the name, source, purpose and brief
history/description of each one.

data manipulation group by problem

Hi I have a data manipulation query. I have the table below which gets
populated on a daily basis
CREATE TABLE [dbo].[FreeSpace] (
[Drive] [char] (1) not null ,
[MB_Free] [int] not null ,
[day_time] [datetime] default getdate()NOT NULL
) ON [PRIMARY]
insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
populates table
I run the query included below but the data comes out as example below
drive monday tuesday etc....
c 100mb null
c null 100mb
d 200mb null
d null 200mb
I would like to display like this
drive monday tuesday etc....
c 100mb 100mb
D 200mb 200mb
My query below
SELECT Drive,
case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Monday,
case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Tuesday,
case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Wednesday,
case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Thursday,
case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Friday,
case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Saturday,
case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Sunday
from FreeSpace
group by Drive,datepart(dd, day_time),MB_Free
order by drive
thanks for any help
SammySammy
See if this helps you
SELECT Drive,
MAX(case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end) as Monday,
MAX(case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end )as Tuesday
......
from FreeSpace
group by Drive
order by drive
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:E4C10338-8829-42B6-A3B9-F87B1224EFF9@.microsoft.com...
> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy|||Thanks Uri
"Sammy" wrote:

> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy