Add static and dynamic columns to a matrix

I

have received a request for a report that has a variable number of

columns that can be generated with a matrix, and then two columns that

are fixed, i.e. one per group. Here is what I mean:

Sect 1 Sect 2 Sect 3 Score Time
-- -
Person 1 X X 50% 8 Days
Person 2 X 12% 3 Days

Another

page-level group could have 2 Sections or 8 Sections. There should only

be one Score and Elapsed Time column. How can I add these fixed columns

to a matrix?

Thanks for any help.
Having dynamic and static columns side-by-side is not currently supported in a matrix. We are working on adding this for a future release. For now, as a workaround, you can add a table that contains two columns (for score and time) to the right side of the matrix, and add a table group on person so that the matrix and the table are grouped the same way on the row.

Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>

Actual frequency / count of a word

This is a strange request that has been submitted as "critical" feature of
an application, and I am not sure of how to approach the problem. The
application has a simple, perfectly good Full Text Search capability (it is
searching on "resumes" and "letters". Now we have been asked to allow the
user to specify the minimum number of "hits" for a particular search
criteria...
For example:
CREATE TABLE [dbo].[EmployeeResume]
(
[EmployeeId] [int] NOT NULL,
EmployeeId [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_EmployeeResume] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
INSERT INTO EmployeeResume
VALUES(100, 'Resume containing words such as POWERPOINT')
INSERT INTO EmployeeResume
VALUES(200, 'Resume containing words such as POWERPOINT EXCEL ACCESS')
INSERT INTO EmployeeResume
VALUES(300, 'Resume containing words such as POWERPOINT EXCEL EXCEL ACCESS')
For sake of simplicity I omit all the code related to the creation of Full
Text catalog on the table and column "Resume"...
Now, the statement...
SELECT EmployeeId FROM EmployeeResume WHERE CONTAINS (Resume, 'EXCEL')
would return 2 rows (EmployeeID 200, 300)
Now we want to allow the user to specify that the query should return only
rows where the word "EXCEL" appears a minimum of 2 times -- in other words,
the query above should only return EmployeeId 300 because the "count" of the
word "EXCEL" is >= 2.
Strange, I know. Anyone has a suggestion?
Thank you so much in advance.
Giorgio
The feature you are looking for is called the hit count and ships with most
of Microsoft's search products but not SQL FTS. What you need to do is to
run a query on your results set that does something like this (untested).
SELECT EmployeeId,
hitcount=(len(resume)-len(replace(Resume,'Excel','')) )/len('excel')FROM
EmployeeResume WHERE CONTAINS (Resume, 'EXCEL')
and (len(resume)-len(replace(Resume,'Excel',''))/len('excel')
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Giorgio Vidali" <gvidali@.earthlink.net> wrote in message
news:%23aHTotT$GHA.396@.TK2MSFTNGP05.phx.gbl...
> This is a strange request that has been submitted as "critical" feature of
> an application, and I am not sure of how to approach the problem. The
> application has a simple, perfectly good Full Text Search capability (it
> is searching on "resumes" and "letters". Now we have been asked to allow
> the user to specify the minimum number of "hits" for a particular search
> criteria...
>
> For example:
>
> CREATE TABLE [dbo].[EmployeeResume]
> (
> [EmployeeId] [int] NOT NULL,
> EmployeeId [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_EmployeeResume] PRIMARY KEY CLUSTERED
> (
> [EmployeeId] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> go
>
> INSERT INTO EmployeeResume
> VALUES(100, 'Resume containing words such as POWERPOINT')
>
> INSERT INTO EmployeeResume
> VALUES(200, 'Resume containing words such as POWERPOINT EXCEL ACCESS')
>
> INSERT INTO EmployeeResume
> VALUES(300, 'Resume containing words such as POWERPOINT EXCEL EXCEL
> ACCESS')
>
> For sake of simplicity I omit all the code related to the creation of Full
> Text catalog on the table and column "Resume"...
>
> Now, the statement...
>
> SELECT EmployeeId FROM EmployeeResume WHERE CONTAINS (Resume, 'EXCEL')
>
> would return 2 rows (EmployeeID 200, 300)
>
> Now we want to allow the user to specify that the query should return only
> rows where the word "EXCEL" appears a minimum of 2 times -- in other
> words, the query above should only return EmployeeId 300 because the
> "count" of the word "EXCEL" is >= 2.
>
> Strange, I know. Anyone has a suggestion?
>
> Thank you so much in advance.
>
> Giorgio
>
>