How To Insert Result Of Calculation In New Column

Jan 17, 2014

I have a clustered index in a table. this column is of datatype date. how can i retrieve the following?:

select [date], valueColumn from myTable
where [date] = '2000-01-03' and
('2000-01-03'+1) and
('2000-01-03'+2)

My Goal ist to retrieve 3 values of valueColumn of 3 subsequent days, calculate the average of this 3 values and insert this average in a third colum called [average3days].

View 12 Replies


ADVERTISEMENT

Transact SQL :: Arithmetic Calculation Between Two Rows And Result In Alias Column?

Jul 12, 2015

I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100 

Formula for calculation

SGR = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
MR =( margin (2015)-margin( 2016) / margin(2015)
MR = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE

[code]....

last record : as there is no record  for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100

View 18 Replies View Related

Cumulative Result Calculation

Jul 5, 2014

Is there any way to calculate Cumulative result.

Manpower|Count|Cum_Count|Formula
Apr|70|70|Sum(APR)
May|40|110|Sum(Apr+May)
Jun|110|220|Sum(Apr+May+Jun)

View 2 Replies View Related

Need To Know Why Two Calculation Methods Result In Different Rounding Effect

Feb 23, 2004

Hi All,

I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).

My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.

The first snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate
GO

and it's result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004781.11774.17769.53527896
112/20/2004757.97750.36742.93605740
122/20/2004509.92501.72494.854180516
132/20/2004988.23980.65973.58632337
142/20/20041283.261269.571259.37416145


And the second snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(CloseIndex * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(CloseIndex * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate

which returns a different result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004784.52774.17772.89527896
112/20/2004755.64750.36740.64605740
122/20/2004512.43501.72497.294180516
132/20/2004989.77980.65975.1632337
142/20/20041285.991269.571262.05416145


Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.

I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself

*scratching head*

any thoughts? Thanks,
Paul

View 14 Replies View Related

Converting Result Of Aggregate Function Calculation To A Double (C#)

Jan 5, 2008

I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good).
 If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable?  Possibly, is the expression after an AS something
that I can manipulate into a double variable?  My end goal is to insert the result of the calculation into a database.
What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps:
  <asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource">
<Columns>
<asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" />
<asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" />
<asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" />
<asp:BoundField DataField="density" HeaderText="density" SortExpression="density" />

</Columns>
</asp:GridView>

<asp:SqlDataSource ID="lbsgalDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT
a.Formulation,
SUM (a.lbs) AS lbs,
SUM ((a.lbs)/(b.density)) AS gal,
( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density
FROM Formulations a INNER JOIN Materials b
ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation">

<selectparameters>
<asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" />
</selectparameters>

</asp:SqlDataSource> 
 

View 2 Replies View Related

How To Calculation Between 3 Column?

Mar 19, 2008

Hi
I am using below code in sql procedure and the data types are below.
b.price -nvarchar 255
b.quantity - nvarchar 255
a.NumPacks - numeric

cast(b.price as decimal(19,2)) / cast(a.NumPacks * b.quantity as decimal(19,2)) AS UNITPRICE

When I run the above code I should get the result as below
1400.99
Instead I am getting as below
1400.990000000000000000

Please help. Advance Thanks.

View 5 Replies View Related

Table-Valued Function Result Vs. Calculation Table

Jun 6, 2006

 

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View 3 Replies View Related

Percentage Calculation Of Column

Aug 27, 2013

I have a column Bill_amount from bill_details table , i want to find 100%,30% of that column.

View 2 Replies View Related

Use Column Alias In Another Calculation

Jul 20, 2005

Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2

View 6 Replies View Related

Derived Column Calculation

Mar 14, 2006


Example: (47 / 204709) * 12000 = 2.754
Both values (47 & 204709) are of data type Int.
SQL Sever - Derived Column Calculation returns 2.74

The destination data type is Float

I have converted the data types to Float, Decimal & Numeric and still got the same answer.

Any suggestions

View 4 Replies View Related

Using Newly Created Column For Doing Further Calculation

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1

First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.

View 2 Replies View Related

How To Test Column Value To Affect Calculation Of Another

Jan 30, 2008

Hi,
I have a column in table which tells me whether Tax should be calculated on a price. This is stored in a column called taxIncluded. I have a 'price before tax' column e.g. grossPrice. I want to calculate the price after tax e.g. netPrice if the taxIncluded column is set to 1. How do I form my sql statement to test whether the taxIncluded is set to 1 & therefore add the tax at say 20%.

e.g.
id name grossPrice taxIncluded netPrice <--- calculated within SQL
-- ------------- ------------------- ---------------- -------------
1 Product1 10.00 1 12.00
2 Product2 20.00 0 20.00

View 7 Replies View Related

Adding A Category Column Based On A Calculation

Mar 19, 2008

Hi all,

I have a large dataset (currently 131,000 rows) that looks similar to the following:

ID NewPer NewAmt OldPer OldAmt
334 1/07/08 200 22/01/08 200
2396 1/07/08 4000 10/12/07 3600
7650 1/07/08 1100 07/07/06 1200
.
.
.

and I need to create a session temp table (eg ##output) that translates the calculation (NewAmt - OldAmt) into categories such as

"decrease -201 to -500"
"decrease -1 to -200"
"no change"
"increase 1 to 200"
"increase 201 to 500"

so that my final output would look like this:

ID NewPer NewAmt OldPer OldAmt Change ChangeCategory
334 1/07/08 200 22/01/08 200 0 no change
2396 1/07/08 4000 10/12/07 3600 400 increase 201 to 500
7650 1/07/08 1100 07/07/06 1200 -100 decrease -1 to -200
.
.
.
I understand how to add the "Change" column to my temp output table, but am struggling with the ChangeCategory column - can someone point me in the right direction?

Thanks in advance
Jamie

View 3 Replies View Related

SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?

Feb 26, 2015

Is there a way to display a column alias as part of the result set column labels?

View 9 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

SQL Server 2012 :: New Column Calculation Based On Running Difference

Aug 15, 2014

New column calculation

CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,

[Code] .....

---OUTPUT
--=======

INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)

[Code] ....

View 2 Replies View Related

Error: Column Name X Appears More Than Once In The Result Column List.

Oct 3, 2006

Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below.  Any clues on how to fix this?  Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }

View 3 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

Insert Result In Another Table

Jan 3, 2006

Hi all

I have a problem to which I'm not finding a solution for, and I thought maybe some of you could give me a few hints on how to approach it.

I need to make available a SP.
For each row returned by the SP, I need to make an insert on a 2nd table, using one of the parameters used on the SP, the PK of the original table and some more info. As in to identify rows, with a specific execution of a particular SP.

Any ideias?

Thanks in advance,
Jorge Carvalho

View 2 Replies View Related

Concatinating Two Field And Insert The Result

Aug 20, 2005

Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind
select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
 

View 1 Replies View Related

How Do I Pass A Result Set Into An SQL Task For An Insert?

Feb 19, 2008



Hello,

I am new to SSIS so I'm sure this is easy to do but I can't seem to figure it out. I created a SQL task that creates a result set. I would like to loop through each result of the result set and insert it into another table.

I'm not sure how to map the result set as input to the second SQL task that will do the insert. I can put the variable in the parameter mapping but I don't know what the SQL should look like to insert this into my table. Each row of the result set has five columns. Normally I have something that looks like INSERT...Values(?,?,?,?,?) but because my input parameter is just one result set object this doesn't work.

Help?

View 5 Replies View Related

Bulk Insert With Another Outstanding Result Set Should Be Run With XACT_ABORT On

Apr 1, 2008

Before today when I run the package, everything is fine. Today when we use similiar database with small schema change, my package can not be run successfully. Always got '

Bulk Insert with another outstanding result set should be run with XACT_ABORT on

' error.

Any help will be appreciated. Thanks

View 8 Replies View Related

Bulk Insert With Another Outstanding Result Set Should Be Run With XACT_ABORT On

Oct 24, 2006

All,

I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.

When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.

Thanks

View 2 Replies View Related

Get Insert Statement Result In Creating A Trigger

Feb 1, 2008

Hi,

When a record is to be inserted, how can I check whether the insert is succeeded or not?


CREATE TRIGGER MY_TRIGGER ON MyTable AFTER INSERT
AS
DECLARE @id INT
DECLARE @name NVARCHAR(50)
SELECT @id=id,@name=name FROM INSERTED
BEGIN

INSERT INTO MySubTable(id,name) VALUES(@id,@name)
-- if failed, rollback
END
GO
GO

View 3 Replies View Related

Need Help On Getting Result From Row To Column

Jan 17, 2008

Hi
I need help to build the below data to my requirement
i have table having the following data's








NoofLive
Action
Sold
Ratio
EffDt

10-49
43
0
0
1/1/2008

50-99          
62
0
0
1/1/2008

100-199        
73
0
0
1/1/2008

200-299        
17
1
0.059
1/1/2008

300-499        
25
0
0
1/1/2008

500-999        
21
0
0
1/1/2008

1000+          
45
0
0
1/1/2008

100-199        
11
1
0.091
2/1/2008

1000+          
1
0
0
2/1/2008

100-199        
17
0
0
3/1/2008

500-999        
2
0
0
3/1/2008 
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
















Jan-08
Feb-08
Mar-08

Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio

10 - 49
43
0
0
0
0
0
0
0
0

50 - 99
62
0
0
0
0
0
0
0
0

100 - 199
73
0
0
11
0
0
0
0
0

200 - 299
17
1
0.059
0
0
0
17
0
0

300 - 499
25
0
0
0
0
0
0
0
0

500 - 999
21
0
0
0
0
0
0
0
0

1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
 
 
 
 

View 1 Replies View Related

Insert Stored Procedure Result Into Temporary Table ?

Mar 21, 2006

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:


-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProc

View 5 Replies View Related

Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 3 Replies View Related

T-SQL (SS2K8) :: Use Stored Procedure Result To Insert Into Table

Mar 25, 2015

My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:

=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====

Or may be I can use Table-valued function insted of myProc?

View 4 Replies View Related

Linked Server Unable To Insert Result Into A Table

Jul 12, 2007

I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.



When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.



I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.



Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.



DECLARE @retval AS INT

DECLARE @value AS INT



SET @value = 4



CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)



INSERT INTO #TempTable

EXEC @RetVal = Server.Database.dbo.testproc @value



SELECT * FROM #TempTable

DROP TABLE #TempTable

View 6 Replies View Related

Add Apostrophe To Column In Result Set

Feb 5, 2004

I have a brain teaser for you all.

The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).

The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@strSQL)

Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.

I have been unable to obtain the solution and I am wondering if it is even possible?

TIA

View 3 Replies View Related

Column Name As The Result Of A Query?

Jan 11, 2005

Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that

LOL

I just tried another way as i was going to post

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordan

View 1 Replies View Related

Aggregate (SUM) A Column And Then Use The Result?

May 7, 2007

Hi,



I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.



Thanks

View 9 Replies View Related

SQL 2012 :: Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved