How can I convert datetime to number of minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Try something like this

(datepart(hh, tblTasks.TaskDuration) * 60) + datepart(mm, tblTasks.TaskDuration)

|||

I tried this and it will return the number of minutes for the hours; however, the Parenthesis will not stay around the (datepart(hh, tblTasks.TaskDuration) * 60) in the view. So the (mm) are not being added.

Well it is adding time for the minutes but 30 is calculating to 10, so 03:30 is returning 190 minutes and it should be 210.

Any ideas?

|||I gave you the wrong datepart signifier, try datepart(n, tblTasks.TaskDuration)|||

Ok, so now that I have the number of minutes, can I convert this to hours and minutes. What I mean is the reporting tool needs a numeric column to sum on, so 03:15 needs to be 3.25.

Is this possible?

|||

I tried and it looks to be returning the correct format. If you have any comments, I appreciate them.

CONVERT (FLOAT, DATEPART(hh, dbo.tblVolunteerTasks.VTaskDuration) * 60 + DATEPART(n, dbo.tblVolunteerTasks.VTaskDuration)) / 60

How can I change the wrong inderted data

I wish someone can help in this.

One column in the table is varchar(64), normally it should contain data in the form
xxxxxxx/yyyyyyy
where xxxxx and yyyyyyy are numbers

A new software is inserting the data with following format
xxxxxxx/yyyyyyy/xxxxxxx/yyyyyyy

This causing problem for the program to read this data and it will be hard to change its code.

What can be the best way to control that from within the SQL?

All helps would be appreciatedStep 1: Fire software vendor that updated the code in a stupid way.

Here is one way to extract the data. You should be able to modify this a littel to get the removal of bad data.

create table test1
(id int identity(1, 1),
col1 varchar(20))

insert into test1 (col1) values ('123/456/789/000')
insert into test1 (col1) values ('123/456/000/789')
insert into test1 (col1) values ('123456789/456/7/000')
insert into test1 (col1) values ('123/456')

select substring (col1, charindex ('/', col1, charindex ('/', col1) + 1)+ 1, 20)
from test1
where col1 like '%/%/%/%'|||I came up with:

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(50))
GO

INSERT INTO myTable99 (Col2)
SELECT '1111111/2222222' UNION ALL
SELECT '3333333/4444444' UNION ALL
SELECT '5555555/6666666' UNION ALL
SELECT '7777777/8888888/9999999/0000000'
GO


SELECT Col2
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 1
UNION ALL
SELECT SUBSTRING(Col2,1,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1)))
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
UNION ALL
SELECT SUBSTRING(Col2,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+2,
LEN(Col2)-(LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+1))
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
GO

DROP TABLE myTable99
GO|||What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

blindman|||The bad data will keep coming always, I just want to put somthing to reshape the data and enter them in correct way

thanks for all

Originally posted by blindman
What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

blindman|||Thanks all for the help, regards,|||What you need is a trigger on your table that automatically verfies and modifies the data as it is entered, using logic like that suggested by brett and MCrowley.

Do you know how to write a trigger?

blindman|||Well, if the data is required to be in that format, then you can mess with them...

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(64) CHECK (LEN(COL2)-LEN(REPLACE(Col2,'/','')) = 1))
GO

INSERT INTO myTable99 (Col2)
SELECT '1111111/2222222' UNION ALL
SELECT '3333333/4444444' UNION ALL
SELECT '5555555/6666666'
GO

INSERT INTO myTable99 (Col2)
SELECT '7777777/8888888/9999999/0000000'
GO

SELECT Col2
FROM myTable99
GO

DROP TABLE myTable99
GO

I would stay away from the trigger...

But you may not have a choice, but to use one...

What is this table used for?

EDIT: How does the data get in? OLTP or Loads?

How can I change the column width?

I'm creating a C# program in Visual Studio. When I query the sql database, the results are put into a data grid view. However, the column widths are always too small when displayed. I have text that is not shown because the column width is not large enough. I tried going into Sql Server Management Studio Express and trying to modify the size property of the columns, but it won't let me change it (its grayed out). How can I fix this problem?Why would you want changing size on the back end server when you need changing size of your grid cells.
You can provide functionality to popup a full text when grid cell double-clicked.

If you'll change backend column size to accommodate your grid size (which is unheard of) you will just truncate existing data in a database and have part of data instead of full set of data.

To limit number of characters returned from a backend to a front end you can use Substring function.

For more help describe your intentions in more detailed and it would be a wise posting it on .NET forum.

Good Luck.

How can I change PRIMARY KEY using code?

Hi,
Assume I have a PRIMARY KEY on one column.
How can I change PRIMARY KEY onto a new column using code?
Do I have to recreate the table?
Thanks.Hi
Here is the code to solve your problem
ALTER TABLE <TABLE_NAME>
DROP CONSTRAINT <Constraint_Name>
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <Constraint_Name> PRIMARY KEY (<Column> )
best Regards,
Chandra
---
"§Chrissi§" wrote:

> Hi,
> Assume I have a PRIMARY KEY on one column.
> How can I change PRIMARY KEY onto a new column using code?
> Do I have to recreate the table?
> Thanks

How can I change a datetime column format?

How can I change a datetime column format?
and How can I make a subtotal'
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 2.
MIEMBRO ACTIVO DE ALIANZADEV> How can I change a datetime column format?
Use .NET formatcodes - e.g. on the Format property of a textbox. Details on
MSDN:
Standard datetime format strings:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp
Custom datetime format strings:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> and How can I make a subtotal'
Please read this section in RS BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_7vi0.asp;
in particular the section of how to add subtotals in report designer.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:O%23IAS9e3EHA.2696@.TK2MSFTNGP14.phx.gbl...
> How can I change a datetime column format?
>
> and How can I make a subtotal'
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 2.
> MIEMBRO ACTIVO DE ALIANZADEV
>sql

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
> >I made a mistake when I first created the column and just found out when I
> > tried to calculate. I tried using the design feature on Enterprise
> > Manager,
> > but got an error message that this couldn't be done. Any help is deeply
> > appreciate.
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.
Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.
|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I catch all errors of the stored at the same time?

I have a stored prcedure . In the stored I wrote 3 SQL statements, one is OK but 2 other statements have error as:

1. Invalid column name 'F2'

2. Invalid object name '##_152008049'.

I put the stored inside try block and catch error in catch block as the following. But I always catch only the first error : invalid column name F2 . How about the second statement?

How can I catch all the errors when I put the stored in try block. Now I don't want to add try..catch inside the store for each statement.

Begin try

exec mystored

End try

begin catch

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

end catch

You are only getting the first one, because when you encounter the first error, it will fall through to the catch block. Any statements after the error don't even get executed.|||Not all errors are of the same kind, there is a difference between statement abort and batch abort errors. But as the previous poster already said, there is no way to return to the next statement after the catch block was handled.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How can I cast a money field in a view to look like money

I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).

If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.

How can I cast so it is still money? by default the entries look like 100.0000.

They must remain a money datatype.

Try this out.

select'$' +cast (convert (decimal ( 10 , 2 ) , <column name> )as varchar )from <table name>

Hope this will help.|||

This still ends up as a varchar, so it will not work for me.

|||

Is there a way to cast to money and have it only show as 100.00 or can I only cast to decimal(10,2) to do this?

|||

As I have mentioned in my previous, for a similar question from you, what are you doing with the values? If its just for display purpose, use front end formatting functions.

|||

I think I can use the decimal(10,2) to work with my money issue. All though I would prefer that it remain as its original datatype.

I would love to use front end formatting; however, in this case it is not possible.

|||

Then you most likely have a serious design issue.

|||

If you have something constructive to say, please do so. Until you understand someone's underlying requirements you are as ignorant as the in experienced.

|||

If I needed more information to qualify my above statement, I would have asked for it. Fortunately, I don't.

Pointing out that what you are asking for would result in a poor design and you should probably rethink your process rather than implementation is hardly unconstructive. I do however, take offense to your statement, so I will end this conversation here. Good luck on getting help when you insult those around you.

PS. inexperienced is a single word.

|||

Motley,

It was not my intent to insult. What I hope for in these cases is not to be told that I'm wrong, as much as to be offered a constructive suggestion.

How can I automate the column titled as ID NUmber in my database, in VS 2005?

I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type

*i am using VS 2005 and VB language

Go into design view for the table

toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True

|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||

Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.

Maybe someone else can chime in here, how to do it some other way

|||

To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.

|||

i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?

|||

You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.

|||Remember, also - tinyint is defined like this:

tinyint

0 to 255

So - I'd recommend at least using INT

One last thing also - you said that your column is titled "ID Number"

My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
--
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can i add string type column as a measure

How can i add Fact table string type column as a measure.

What are you looking to do with the string?

Measures are usually numbers that can be summed (or some other additive/semi-additive function). If you have string datatypes in your fact table, and you're not just looking for a count, are those string values unique per row? If so, what you'll want to build is a degenerate dimension. If not, you'll want to pull that out into a separate dimension table and foreign key to it with an int surrogate key.

Search for the phrase "degenerate dimension" in the following paper:

http://msdn2.microsoft.com/en-us/library/ms345125(SQL.90).aspx

(Don't use ROLAP like he suggests unless MOLAP just doesn't work for you.)

how can i add heading in Matrix Report for columns and rows

How can i add a column heading for coulumns and row heading for rows in Matrix report created by wizard. i m using vs 2005 ..You need to create a Row Group or Column Group but at least from my perspective I have always stayed from Matrix's in MS RS and the reason why is - I cannot control the extrapolation of the matrix to the defined width of my report - probably a lousy reason - but I "fake" out based on a regular table layout a matrix look and feel with drill through's...
If the user wants to see everything then "click" and everything will be displayed - but for the first panel of data I provide as much possible releavant info and you want to see more then click on a link and I will navigate you to eternity! Also, when I navigate I keep the primary data field they clicked on as a parm and display this in the subsequent (called module) so the have a "path" of wheer they started there "infinite journey" from!
Best regards,

how can I add a unique key column to a table and generate its values automatically?

Hi, all,

I have a question about adding a unique key column to an existing table.

what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?

Thanks a lot in advance for any guidance.

Here is an example of how to add an identity column

create table TestID (SomeField varchar(49))
insert into TestID values ('1.1.9')
insert into TestID values ('2.2.2.10')
insert into TestID values ('2.2.2.8')
insert into TestID values ('2.2.2.9')
insert into TestID values ('1.1.7')
insert into TestID values ('1.1.8')
insert into TestID values ('1.2.1')
insert into TestID values ('1.1.1')
insert into TestID values ('1.1.10')
insert into TestID values ('1.1.10')
GO
select * from TestID
GO

alter table TestID add IdField int identity not null
Go
select * from TestID
GO

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi, thanks a lot.

Got it done, but the problem is: the new added identity column is not in order? like its values from 1 to 82 is actually not in the order, it is sorted out based on other columns values in the existing table. So how can I modify its values from 1 to 83 in the oder?

Thanks a lot.

|||Order is never guaranteed for a table, use ORDER BY to get the rows back in the order that you want|||Hi, thanks a lot.

How can I add a fiscal year column to a view

I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.

How can this be done?

I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.

Maybe you should clarify your problem a little bit more. What kind of data are FiscalYearStart and FiscalYearEnd? Can you provide us a sample dump?

If you have a series of disjoint datetime intervals representing fiscal years, a simple join would do...

|||

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

create table tblData
(DataDate datetime
)

insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')

insert into tblData values ('15-Oct-1998')
insert into tblData values ('15-Oct-1999')
insert into tblData values ('15-Oct-2000')
insert into tblData values ('15-Oct-2001')
insert into tblData values ('15-Oct-2002')
insert into tblData values ('15-Oct-2003')
insert into tblData values ('15-Oct-2004')

select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd

select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd

Note that the second query will return data even if it does not find a fiscal year match.

That's useful if you are concerned that you will forget to load tblGlobal on time at the start of a new fiscal year, or if data gets entered with an old or far future date.

It's important to understand that you can join on whatever you want to join on - you are not limited to just foreign key columns!

|||

Ok, my fiscalyearstart and end fields are datetime. The date would look like paymentdate, AmountPaid, from table tblPayments. The fiscalyearstart and end are in tblGlobal.

10/1/2007 12:00:00 PM, $1000.00

The fiscal year might be 7/1/yyyy to 6/30/yyyy or 10/1/yyyy to 9/30/yyyy

I was thinking it might be good figure out a way to put the fiscal year in a field with each payment, unless there is a way to do it in the view.

I appreciate any help you give.

|||

David this looks great. But I need to tell you one more thing, the reason I was looking to possibly have a field or column that showed the fiscal year for each payment is I will be using the view with a reporting tool. So I need a field to group on so I can show the sum of payments for each fiscal year.

Does it sound like this is something you can help with?

I greatly appreciate your help.

|||

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

create table tblData
(DataDate datetime
,DataValue int
)

insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')

insert into tblData values ('15-Oct-1998', 5)
insert into tblData values ('15-Oct-1999', 10)
insert into tblData values ('15-Oct-1999', 11)
insert into tblData values ('15-Oct-1999', 12)
insert into tblData values ('15-Oct-2000', 100)
insert into tblData values ('15-Oct-2000', 101)
insert into tblData values ('15-Oct-2000', 102)
insert into tblData values ('15-Oct-2001', 1000)
insert into tblData values ('15-Oct-2001', 1001)
insert into tblData values ('15-Oct-2001', 1002)
insert into tblData values ('15-Oct-2002', 2000)
insert into tblData values ('15-Oct-2003', 3000)
insert into tblData values ('15-Oct-2004', 4000)

select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

The group by is pretty standard sql, and allows you to produce summaries, counts, averages, etc for the group.

|||

I will give this a try and let you know how it goes. Thank you so much.

|||

David, or anyone that would know how to do this. I found that the reporting tool I must use will only accept views and will not let me join on a date range, only single fields.

I think what I need is something like this: (this is an uneducated thought)

I have a view of all payments ever made.

In my view I need an expression column that returns what fiscal year the payment was madebased on the month part of the fiscalyearstart and fiscalyearend fields from tblGlobal (not sure how we would join them in the view)

So if the payment date is 7/2/2007 and the fiscalyearstart month is 7 and the fiscalyearend month is 6 the expression column would return 2008

The actual fiscal year would range 7/1/2007 to 6/30/2008

I have no idea how to do this, this is just my thought.

|||

Why not do all the joins you need to do in a view in the database. They your reporting tool is just issuing a simple select statement:

create view some_view_name as
select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

Your report tool will surely allow you to add a where clause to a query using this view:

select * from some_view_name
where FiscalYear = 2002

ps - When you get the problem you asked for help on solved, you should really mark that thread as answered. If you have a new problem, you should start a new thread. That way, others who have a similar problem can find your thread and its answer.

|||

David,

You are very correct, but I had thought about it and if there was a way to not base this on the entire fiscalyearstart and end from the tblGlobal. What I mean is that if they need to base a report on multiple fiscal years then the between join would only allow them to see the fiscal year entered into the tblGlobal table. So I was hoping there was some way to do what you have done here and base it somehow on the months of the fiscal year not the actual fiscal year. Just incase they need to base the report on multiple fiscal years.

For example;

If they enter a payment on 7/2/2007 and the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 then the fiscal year the payment was entered is 2008 or If they enter a payment on 7/2/2007 and the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 then the fiscal year the payment was entered is 2007.

You may have a more educated idea, but this is just what I was thinking about.

I really do appreciate your help.

|||

This is the sample tblGlobal I guessed at:

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

It would allow you to store as many fiscal years as you want.

Are you telling me that tblGlobal only stores one record, and that FiscalYearStart and FiscalYearEnd are Month/Day, not a Month/Day/Year values?

Because it sure would have saved some time to know that two days ago!

You need to post the create table statements for the tables involved, along with sample data.

|||

Sorry, David,

Yes, the tblGlobal only has one record. The fiscalyearstart and end fields are datetime and do hold the entire date, like 7/1/2007.

Again sorry for missing this key detail. The data samples you have shown look fine to me.

|||

If you mean that if we have FiscalYearStart 2007-07-01, all days before 07/01 (1st of July) of year Y would be in fiscal year Y-1 and all days after 07/01 would be in fiscal year Y, you may create a function to get this year like this:

CREATE FUNCTION [dbo].[GetFiscalYear] (@.tDateTime)RETURNSintASBEGIN-- Declare the return variable hereDECLARE @.ResultintDECLARE @.fmint;DECLARE @.fdint;SELECT @.fm =month(FiscalYearStart), @.fd =day(FiscalYearStart)FROM tblGlobal;IF month(@.t) > @.fmORmonth(@.t) = @.fmANDday(@.t) >= @.fdSET @.Result =year(@.t);ELSESET @.Result =year(@.t)-1;RETURN @.Result;END

Then you may use it like in this example:

select orderid, customerid, orderdate, dbo.GetFiscalYear(orderdate)from Orders

|||

I tried the function, I past it a paymentdate of 7/16/2007 and it returned 2007, but it should be 2008.

If the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 (7/1/2007 to 6/30/2008) so it is plus one here

7/16/2007 = a fiscal year of 2008 because the fiscal year ends on 6/30/2008

If the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 (10/1/2006 to 9/30/2007)

7/16/2007 = a fiscal year of 2007 because the fiscal year ends on 6/30/2007

Does this sound right?

|||

So flip the greater than/less than signs as needed, and add 1 instead of subtract 1 as needed.

It's your turn to do the programming!

How can I "flag" a column on a table

I need to be able to "flag" a column on a table for special treatment and I am looking for a way to do this.

What I want to do is allow an administrator to somehow mark (flag) a column on a table. Then as part of another process, a script would scan all the columns in a database, looking for this flag, and take action based upon the data in this column.

This flag has to be applicable to existing, third-party databases, databases over which I have no control as to column names, or data types.

Ideally, I'd like to have some user-defined property of a column which would allow me to mark columns for use by my program. Does something like this exist.

The best solution I have come up with, so far, is to create a rule, which passes everything (LIKE '%'). By looking for the presence, or absence, of this rule, on a column, I have been able to flag columns.

I'd like to know, however, if there is a cleaner solution.

Thanks.

KenYou could create a seperate table with 2 columns:

tablename
columnname

Which lists the columns that have been flagged and in which table. The admin function can insert a record into this table and then during processing remove the record.

How can change the visibillity of a Chart Data Field?

Hi,
is it possible to toggle the visibillity of a char data item? Because if the
value is zero or null i dont want the column in the chart to be displayed.
Thanks alot
FlorianIf a datapoint value is null, it won't be displayed in the chart.
If you want to explicitly hide datapoint with other values (e.g. y-value =0), you can use an expression for the datapoint value similar to this to
replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
Nothing, Fields!Y.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Florian Kirchlechner" <Florian Kirchlechner@.discussions.microsoft.com>
wrote in message news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
> Hi,
> is it possible to toggle the visibillity of a char data item? Because if
> the
> value is zero or null i dont want the column in the chart to be displayed.
> Thanks alot
> Florian|||Hi Robert,
thx for the reply - it really helped me :-)
But one more thing - can i hide the series label too? I tried it with your
suggestions but it didnt work.
Thanks and greets /Flo
"Robert Bruckner [MSFT]" wrote:
> If a datapoint value is null, it won't be displayed in the chart.
> If you want to explicitly hide datapoint with other values (e.g. y-value => 0), you can use an expression for the datapoint value similar to this to
> replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
> Nothing, Fields!Y.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Florian Kirchlechner" <Florian Kirchlechner@.discussions.microsoft.com>
> wrote in message news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
> > Hi,
> >
> > is it possible to toggle the visibillity of a char data item? Because if
> > the
> > value is zero or null i dont want the column in the chart to be displayed.
> >
> > Thanks alot
> > Florian
>
>|||No, you cannot dynamically hide series labels. Did you look into adding a
filter on the dataset or the chart or the series grouping to filter out all
data points with values you don't want to show in the chart?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Florian Kirchlechner" <FlorianKirchlechner@.discussions.microsoft.com> wrote
in message news:BA66D7C0-D108-4BF0-9474-D5DD2FADF994@.microsoft.com...
> Hi Robert,
> thx for the reply - it really helped me :-)
> But one more thing - can i hide the series label too? I tried it with
> your
> suggestions but it didnt work.
> Thanks and greets /Flo
> "Robert Bruckner [MSFT]" wrote:
>> If a datapoint value is null, it won't be displayed in the chart.
>> If you want to explicitly hide datapoint with other values (e.g. y-value
>> =>> 0), you can use an expression for the datapoint value similar to this to
>> replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
>> Nothing, Fields!Y.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Florian Kirchlechner" <Florian Kirchlechner@.discussions.microsoft.com>
>> wrote in message
>> news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
>> > Hi,
>> >
>> > is it possible to toggle the visibillity of a char data item? Because
>> > if
>> > the
>> > value is zero or null i dont want the column in the chart to be
>> > displayed.
>> >
>> > Thanks alot
>> > Florian
>>|||Im looking for a method to filter the data points without a value and also to
not have their series labels displayed in the legend. Maybe i will overcome
that with manually generating the legend.
Thanks alot
Florian
"Robert Bruckner [MSFT]" wrote:
> No, you cannot dynamically hide series labels. Did you look into adding a
> filter on the dataset or the chart or the series grouping to filter out all
> data points with values you don't want to show in the chart?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Florian Kirchlechner" <FlorianKirchlechner@.discussions.microsoft.com> wrote
> in message news:BA66D7C0-D108-4BF0-9474-D5DD2FADF994@.microsoft.com...
> > Hi Robert,
> > thx for the reply - it really helped me :-)
> > But one more thing - can i hide the series label too? I tried it with
> > your
> > suggestions but it didnt work.
> >
> > Thanks and greets /Flo
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If a datapoint value is null, it won't be displayed in the chart.
> >> If you want to explicitly hide datapoint with other values (e.g. y-value
> >> => >> 0), you can use an expression for the datapoint value similar to this to
> >> replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
> >> Nothing, Fields!Y.Value)
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Florian Kirchlechner" <Florian Kirchlechner@.discussions.microsoft.com>
> >> wrote in message
> >> news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
> >> > Hi,
> >> >
> >> > is it possible to toggle the visibillity of a char data item? Because
> >> > if
> >> > the
> >> > value is zero or null i dont want the column in the chart to be
> >> > displayed.
> >> >
> >> > Thanks alot
> >> > Florian
> >>
> >>
> >>
>
>|||It may be easier to filter the chart series groups, but generating a custom
legend is also possible. This blog article including a sample should get you
started: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Florian Kirchlechner" <FlorianKirchlechner@.discussions.microsoft.com> wrote
in message news:2EC010BC-D1BA-4F57-9F92-CE0CAF37F6A9@.microsoft.com...
> Im looking for a method to filter the data points without a value and also
> to
> not have their series labels displayed in the legend. Maybe i will
> overcome
> that with manually generating the legend.
> Thanks alot
> Florian
>
> "Robert Bruckner [MSFT]" wrote:
>> No, you cannot dynamically hide series labels. Did you look into adding a
>> filter on the dataset or the chart or the series grouping to filter out
>> all
>> data points with values you don't want to show in the chart?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Florian Kirchlechner" <FlorianKirchlechner@.discussions.microsoft.com>
>> wrote
>> in message news:BA66D7C0-D108-4BF0-9474-D5DD2FADF994@.microsoft.com...
>> > Hi Robert,
>> > thx for the reply - it really helped me :-)
>> > But one more thing - can i hide the series label too? I tried it with
>> > your
>> > suggestions but it didnt work.
>> >
>> > Thanks and greets /Flo
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> If a datapoint value is null, it won't be displayed in the chart.
>> >> If you want to explicitly hide datapoint with other values (e.g.
>> >> y-value
>> >> =>> >> 0), you can use an expression for the datapoint value similar to this
>> >> to
>> >> replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
>> >> Nothing, Fields!Y.Value)
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Florian Kirchlechner" <Florian
>> >> Kirchlechner@.discussions.microsoft.com>
>> >> wrote in message
>> >> news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > is it possible to toggle the visibillity of a char data item?
>> >> > Because
>> >> > if
>> >> > the
>> >> > value is zero or null i dont want the column in the chart to be
>> >> > displayed.
>> >> >
>> >> > Thanks alot
>> >> > Florian
>> >>
>> >>
>> >>
>>|||Since you said there is not a way to dynamically hide a label, is there a way
to always hide a series label? I have one line that should not have a label,
but if I set the label to "=Nothing", "=System.DBNULL.Value", or just leave
it blank it puts something automatic like "Series3" -- how can I remove this?
-diana
"Robert Bruckner [MSFT]" wrote:
> No, you cannot dynamically hide series labels. Did you look into adding a
> filter on the dataset or the chart or the series grouping to filter out all
> data points with values you don't want to show in the chart?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Florian Kirchlechner" <FlorianKirchlechner@.discussions.microsoft.com> wrote
> in message news:BA66D7C0-D108-4BF0-9474-D5DD2FADF994@.microsoft.com...
> > Hi Robert,
> > thx for the reply - it really helped me :-)
> > But one more thing - can i hide the series label too? I tried it with
> > your
> > suggestions but it didnt work.
> >
> > Thanks and greets /Flo
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If a datapoint value is null, it won't be displayed in the chart.
> >> If you want to explicitly hide datapoint with other values (e.g. y-value
> >> => >> 0), you can use an expression for the datapoint value similar to this to
> >> replace the value with null (Nothing in VB): =iif(Fields!Y.Value = 0,
> >> Nothing, Fields!Y.Value)
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Florian Kirchlechner" <Florian Kirchlechner@.discussions.microsoft.com>
> >> wrote in message
> >> news:31A10DB1-6A82-4AE8-AF21-A4658087ADB5@.microsoft.com...
> >> > Hi,
> >> >
> >> > is it possible to toggle the visibillity of a char data item? Because
> >> > if
> >> > the
> >> > value is zero or null i dont want the column in the chart to be
> >> > displayed.
> >> >
> >> > Thanks alot
> >> > Florian
> >>
> >>
> >>
>
>