Adding 2 text fields together

is it possible to concatenate two fields of record text into one field of
record text?
Thanks In Advance,
MPMYou'll need to create a trigger. An example might be:
CREATE TRIGGER SetFullName ON Customers
FOR INSERT, UPDATE
Not for replication
AS
/* set fullname for Customers */
update Customers
set Customers.FullName = inserted.[FirstName] + ' ' +
inserted.[Surname]
from inserted
where Customers.CustomerID = inserted.CustomerID
"MANCPOLYMAN" wrote:

> is it possible to concatenate two fields of record text into one field of
> record text?
> Thanks In Advance,
> MPM

Adding 2 text fields together

is it possible to concatenate two fields of record text into one field of
record text?
Thanks In Advance,
MPM
You'll need to create a trigger. An example might be:
CREATE TRIGGER SetFullName ON Customers
FOR INSERT, UPDATE
Not for replication
AS
/* set fullname for Customers */
update Customers
set Customers.FullName = inserted.[FirstName] + ' ' +
inserted.[Surname]
from inserted
where Customers.CustomerID = inserted.CustomerID
"MANCPOLYMAN" wrote:

> is it possible to concatenate two fields of record text into one field of
> record text?
> Thanks In Advance,
> MPM

Adding 2 text fields together

is it possible to concatenate two fields of record text into one field of
record text?
Thanks In Advance,
MPMYou'll need to create a trigger. An example might be:
CREATE TRIGGER SetFullName ON Customers
FOR INSERT, UPDATE
Not for replication
AS
/* set fullname for Customers */
update Customers
set Customers.FullName = inserted.[FirstName] + ' ' +
inserted.[Surname]
from inserted
where Customers.CustomerID = inserted.CustomerID
"MANCPOLYMAN" wrote:
> is it possible to concatenate two fields of record text into one field of
> record text?
> Thanks In Advance,
> MPM

Add times

SQL Statement.

Adding times together.

I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.

They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?

Below is the SQL statement of getting times from my database if its any help.

SQL Statement

SELECT LENGTH_OF_VISIT FROM NOV.MAIN;

Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

Hi Ric

This forum is specific to SQL Server Data Mining. You'll probably get a better answer by posting to the T-SQL forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can extract the parts of your datetime field using the DATEPART function as:

SELECT DATEPART(LENGTH_OF_VISIT, hh) as Hour, DATEPART(LENGTH_OF_VISIT, mm) as AS Minute from Nov.Main.

Then you can wrap an aggregate ADD function to add the values from different records.

SELECT ADD(DATEPART(LENGTH_OF_VISIT, hh) * 60 + DATEPART(LENGTH_OF_VISIT, mm)) from Nov.Main

Please look at the documentation of DATEPART for more usage examples

Hope this helps

Add space between matrix row group

I have a 2 row groups in a matrix and I need to add some space between the top one so that all the columns don't run together. Is there a way to do this?

Click on the row that you want to add the space below, go to the properties window and increase your 'Height' value. Also, make sure that the 'VerticalAlign' field is set to 'Top'. This will add empty space between that row and the one directly below it.

Hope this helps.

Jarret

|||I guess I wasn't clear. I dont need space between the rows, but between the last column in a row group and the first column in the next row group. Does that make sense?|||

Click on the last column in the matrix, and in the properties window, increase the Padding-Right value. You might want to increase the Width of that column to handle the additional padding. Does this do what you are trying to accomplish?

Hope this helps.

Jarret

|||

No, padding won't help. Here's my layout. I need more space between the subject areas.

ELA

Math

Science

Social Studies

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

17%

46%

37%

1%

14%

36%

30%

20%

20%

32%

23%

24%

40%

37%

9%

15%

9%

57%

30%

4%

22%

49%

16%

13%

22%

50%

13%

15%

14%

44%

22%

21%

16%

36%

44%

4%

13%

39%

22%

26%

27%

33%

21%

18%

23%

40%

17%

20%

|||That's what it currently looks like. How do you want it to look?|||

So, you are wanting something like a 'spacer' column? What if you add another blank column to you report? Right click your textbox below the 'Advanced' column and select 'Add Column', then just leave it blank.

Jarret

|||

ELA					Math
Below Basic	Basic	Proficient	Advanced		Below Basic	Basic		Proficient	Advanced
17%	46%	37%	1%		14%	36%		30%	20%
9%	57%	30%	4%		22%	49%		16%	13%
16%	36%	44%	4%		13%	39%		22%	26%

See the space I've added between ELA and Math sections? Thats what I want.

|||That adds a second column of data that will repeat. So instead of one column under advanced I have two.|||Would you still want the tables to appear horizontally next to each other? Have you considered placing the matrix in a list control and adding a groupping by subject? This would effectively give you a matrix per subject but they would be layed out vertically.|||I have to lay them out horizontally bc of how many rows there are. If I do it vertically the report gets too long to read.|||Does anybody have a clue how to do this? It seems like it'd be so simple...

Add Date only to Time only = datetime ?

I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
CarlIf I understand you, you want use date from EVT_DT, use time from EVT_TM put them together and retrieve datetime: '2005-12-05 13:59:00'

if I'm right, transform dates to varchar, cut what u need, concatenate strings and transform it back to date time:

declare @.EVT_DT datetime
declare @.EVT_TM datetime

set @.EVT_DT = '2005-12-05 00:00:00'
set @.EVT_TM = '2005-12-06 13:59:00'

select
cast(
cast(@.EVT_DT as varchar(11)) + ' ' +
cast(datepart(hh, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(mi, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(ss, @.EVT_TM) as varchar(2))
as datetime)

2005-12-05 13:59:00.000|||I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
Carl

Just so you are aware, using your examples above, SQL would "treat" EVT_DT as 38689.00000 and EVT_TM as 38690.58264. So, when you try to add them together, you're getting an unexpected value. A better practice would be to store the completed Date/Time together in a single column and then deal with the issue of selecting for a specific data by using BETWEEN.

Regards,

hmscott