How to Truncate

Hello again,
I also want to truncate some data which is a floating point number. But
it is calculated as a result of two other columns.
for example lets say
column1: 2,4
column2: 4,1483
the result is: 9,95592
but I want to truncate the first 2 digits after the decimal point.
like 9,95
also what is the rounding formula and how to use?
Thanks in advance.You're really talking about display formatting -which is better done in the
client application.
If you must have SQL Server change the format of this computed value, try:
SELECT cast( (Column1 * Column2 ) AS decimal(10.2) )
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637900.501096.298380@.m73g2000cwd.googlegroups.com...
> Hello again,
> I also want to truncate some data which is a floating point number. But
> it is calculated as a result of two other columns.
> for example lets say
> column1: 2,4
> column2: 4,1483
> the result is: 9,95592
> but I want to truncate the first 2 digits after the decimal point.
> like 9,95
> also what is the rounding formula and how to use?
>
> Thanks in advance.
>

How to Truncate

Hello again,
I also want to truncate some data which is a floating point number. But
it is calculated as a result of two other columns.
for example lets say
column1: 2,4
column2: 4,1483
the result is: 9,95592
but I want to truncate the first 2 digits after the decimal point.
like 9,95
also what is the rounding formula and how to use?
Thanks in advance.You're really talking about display formatting -which is better done in the
client application.
If you must have SQL Server change the format of this computed value, try:
SELECT cast( (Column1 * Column2 ) AS decimal(10.2) )
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637900.501096.298380@.m73g2000cwd.googlegroups.com...
> Hello again,
> I also want to truncate some data which is a floating point number. But
> it is calculated as a result of two other columns.
> for example lets say
> column1: 2,4
> column2: 4,1483
> the result is: 9,95592
> but I want to truncate the first 2 digits after the decimal point.
> like 9,95
> also what is the rounding formula and how to use?
>
> Thanks in advance.
>

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:

Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

how to sum the HOURS in a group

Hi,

How to sum up the total of all the HOURS except 'ZZ' which should be summed up seperately.
hope u got me.
Example: (output should be as below).
nadeem 5
shyub 5
ayub 5
ZZirshad 5

Reported Production Hours: 15 (nadeem + shyub + ayub)
Reported NonProduction Hours: 5 (ZZirshad)

This report which i have asked now already exists..now i have to modify the report with the changes which i have explained to you above. In the existing report, the record selection formula that exists is,
{HR01.HR01015} like {?Department} and
{EPS_Timesheet_Validation.FNTransactDate} = {?DateRange} and
Switch ({?Non_Productive_Time}='Exclude Non Productive Time',Not ({EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*'),
{?Non_Productive_Time}='Include Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like '*',
{?Non_Productive_Time}='Only Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*')

Please help me.
Thanks.,edit the select, and remove the

Switch ({?Non_Productive_Time}='Exclude Non Productive Time',Not ({EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*'),
{?Non_Productive_Time}='Include Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like '*',
{?Non_Productive_Time}='Only Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*')

this should return all records, you can then group off the param
{?Non_Productive_Time} and sum it up that way, or use a running total

How to sum a column in a SQL query

Can someone tell me how to sum values in a column from a resultant Query. For example, consider this query which in part
calls a view named "Rpt_OverallIndMarket"

select * from Rpt_OverallIndMarket order by MarketId,Year,Quarter

The resultant query from the previous query will contain a column named TotalSquareFeet. How do I sum all of the TotalSquareFeet values in
that column?

Do you just need the sum or you need the data + sum? If you need both perhaps your front end is best to calculate the sum. Most controls like datagrid etc have features to calculate a sum. If thats not possible you might need to use a GROUP BY in the query but that might affect the results.

|||

Dont know exactly if this is what you want but this is what I understood:

SELECT Sum(ALL(columnname)) FROM table

|||

select sum(TotalSquareFeet) from Rpt_OverallIndMarket returns 1 row with 1 column, the sum of total square feet
----------
select sum(TotalSquareFeet), MarketId, Year,Quarter from Rpt_OverallIndMarket group by MarketId,Year,Quarter
returns 1 row per MarketId, Year, Quarter with the sum across that MarketId, Year and Quarter (IOW, the subtotals).
----------
select 'Total ' as SubTotal, sum(TotalSquareFeet), max(MarketeId), max(Year) ,max(Quarter) from Rpt_OverallIndMarket
union all
select 'Detail', TotalSquareFeet, MarketId, Year,Quarter from Rpt_OverallIndMarket
order by 1, 2, 3,4

returns detail and total lines in 1 answer set, ordered by the first 4 columns. Those max() functions are just place holders since a union requires the same number and types of fields in all parts of the union

Detail 10 a 2000 3
Detail 15 a 2000 2
Detail 8 b 2001 1
Total 33 b 2001 3 <-- The 'b 2001 3' are just place holders, you can ignore them

My point in giving this last example is that you can use subtotaling and unions to do a lot of the subtotaling work for you -- all you need to do is include a literal column (eg, "Detail", "Total") telling your program what the row represents.

|||

Did you get the answer you wanted yet?

The two choices in the posts above are basically use theSUM function in SQL with a GROUP BY clause, or to do it yourself in code.

If you're not familiar with grouping, I'd recommend you read:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

how to store and access reservation data (was "small project")

I have a project need to store the information of reservation. For example, somebody wants to reserve a laptop. She will sign in the date from calender to use. I would like to let the administrator to access the database and update the database in the intranet. Also the user who want to reserve a resource can access the intranet to fill a form, after approving it, she can borrow the resource. Of course, this is small database. And we also have a report on this.Do you have any idea how to do it? I appreciate.This in a database? What's wrong with excel/plain text file?
A bit of asp/jsp and a simple database? Guess it depends on what's been used for other applications.