MS SQL Server :: Update A Rows In One Table For Each Row In Other Table

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

Transact SQL :: Update Table From Another Table For Multiple Rows

Jun 10, 2015

Matrix table has ID column and data below.

ID        Flag     TestDate         Value Comment
111 2 12/15/2014     7.5    null
222     2    Null 10          received

Matrix_Current table could have 1 or multiple rows as below.

ID       Flag             TestDate          Value Comment
111         2                  01/26/2015        7.9
111         2                  02/23/2015        7.9
111         2                  04/07/2015        6.8
222       1           null      8         test comment 1
222        3           null     9       test comment 2

When I run below update

UPDATE AM
SET  M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID

Matrix table has value below:

ID        Flag     TestDate         Value Comment
111 2 01/26/2015     7.9
222     1    Null 8        test comment 1

I want to update Matrix table from all row from Matrix_Current, final table would like below:

ID        Flag     TestDate        Value Comment
111         2 04/07/2015     6.8
222     3    Null 9       test comment 2

View 3 Replies View Related

Update Rows From Another Table

Aug 24, 2007

Hi

I would like update the rows of table 1 with the values in table2 see below based on a matching ID. I don�t want to update the ID field as this would be a violation of the primary key I assume as ID is a primary key, just the FirstName and SecondName fields need updating.

Table1
(ID, FirstName, Surname)
Table 2
(ID, FirstName, Surname)

Any ideas?
Cheers

View 6 Replies View Related

How To Update Rows In A Table

Nov 26, 2007

We have a table and it is having records with for example “Chase“, now we don’t want those “ “ and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?

Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here

declare @tbl varchar(200)

declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'

open cur

fetch next from cur into @tbl

while @@FETCH_STATUS = 0

begin

print @tbl

--exec sp_spaceused @tbl

fetch next from cur into @tbl

end

close cur

deallocate cur

Can you please send me the logic?
It will be more appreciable.

-Thanks N Regards,
Kanthi.

View 1 Replies View Related

How To Update Rows In A Table

Nov 26, 2007

We have a table and it is having records with for example ��Chase��, now we don��t want those �� and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?

Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here

declare @tbl varchar(200)
declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'
open cur
fetch next from cur into @tbl
while @@FETCH_STATUS = 0
begin
print @tbl
--exec sp_spaceused @tbl
fetch next from cur into @tbl
end
close cur
deallocate cur

View 1 Replies View Related

Update Multiple Rows In One Table?

Nov 11, 2014

I want to update multiple rows in one table

DDL of 3 tables

CREATE TABLE [dbo].[appl](
[app_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL)
CREATE TABLE [dbo].[appl_party](
[prty_id] [numeric](9, 0) NOT NULL,
[app_id] [numeric](9, 0) NOT NULL)
CREATE TABLE [dbo].[party](
[prty_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[lockbyid] [char](8)

I want to update muliple rows in table "party" for column "lockbyid"

below is the update query with which i can only update one row but i need to update multiple rows in party table

update party set LOCKBYID ='abcd' where prty_id in
(select distinct prty_id from sappl_party where app_id in (Select appl.app_id
FROM appl INNER JOIN appl_party ON appl.app_id = appl_party.app_id where appl_party.prty_id=1234))
and LOCKBY_USR_ID is null

View 1 Replies View Related

Need To Update Table To Delete Rows

Oct 2, 2007

what is wrong with this statement?

Update services
Delete
where ticketnum = '0'

error message: incorrect syntax near keyword delete

View 6 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?

View 3 Replies View Related

Transact SQL :: Iterate Through Table Rows And Do Conditional Update

Aug 11, 2015

I have a table with the following fields:

ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))

I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?

View 4 Replies View Related

Power Pivot :: Update Specific Rows In Table

May 21, 2015

I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?

View 2 Replies View Related

Transact SQL :: Query To Update A Table With More Than 150 Million Rows Of Data?

Sep 17, 2015

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,

[Code] ....

The update requirement is as follows:

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
SET rowcount 10000
update r
set Comp=t.Comp

[Code] ....

The update took more than 48h and didn't terminate , how to accelerate it ?

View 6 Replies View Related

Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table

Jul 16, 2015

We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.

Below is the Table Structure.

Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0

Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.

Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.

View 6 Replies View Related

Problem With Update When Updating All Rows Of A Table Through Dataset And Saving Back To Database

Feb 24, 2006

Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType = CommandType.Text;
snUpdate.CommandText = "Update TestTable set shipdate = @shipdate";
snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds, "Table1");
conn.Close();

-Thanks

View 4 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

Count All Table Rows Then Insert Into Test Table Using SSIS Packages

Jul 15, 2013

I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .

test99: tableName countRows
t1 20
t2 30
t3 25

View 2 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE

Dec 7, 2007

I have the following data

MASTER
id
name

DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 Replies View Related

SQL Call To Count The Total Rows In Table B For Each User In Table A

Jan 17, 2006

I have 2 tables:

TableA:
Name
UserA
UserB
UserC

Table B:
Name               Data
UserA              xxx
UserB              asdasd
UserB              ewrsad
UserC              dsafasc
UserA              sdf
UserB              dfvr4

I want to count the total entries in Table B for every user in Table A. The output would be:

Name               Count
UserA              2
UserB              3
UserC              1

I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?

View 5 Replies View Related

Copying All Rows From One Table Into Another Existing Table And Overwriting Data

Feb 15, 2005

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanks

View 1 Replies View Related

Trigger On Table LeaveRegister And Inserting Rows In Audit Table

Oct 22, 2012

I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.

View 6 Replies View Related

How To Archiv Table To Another Table With Unique Number For All Rows Once + Date

Apr 28, 2008

need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )

this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03

for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15

.........................
.....................................................................667 15/04/2008 17:15

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15

.........................
.....................................................................668 15/04/2008 08:15

TNX

View 3 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:

SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x

This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:

SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'

'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

SQL Server 2012 :: Create Table From Rows Of A Different Table

Mar 6, 2015

View 9 Replies View Related

Table Scans - More Rows Brought Back Than In Table

Jul 20, 2005

I have a query which is quite complex. It's based on a set of data ina complex view which takes the data from several tables.In this complex query, if I allow the various parts of the query towork on the results of the view (MISView), it can take 15 minutes torun (eek !), however, if I create a temporary table with the data fromthe view and then use that for the remainder of the query, it runs inapprox 20 seconds.Now, I have examined the execution plan (my new favourite toy) andthere is a difference (as expected). However when looking at the partof the query that takes up most of the time, it shows that it bringsback 109,645,866 records from a table (Credit) that contains 13,002records. This table is one that is referenced in the view (MISView)which contains 13,653 records and does get some of it's data from thetable which is scanned (Credit).For the record, we don't have any tables with over 100,000 records in,so 109 million rows is going some for us. The part of the query thatruns slow does reference another copy of itself but this is necessaryfor the equation that is being run.Now I'm OK with why it's doing the table scan, but why does it bringback substantially more data than is in the table ? Is it somemultiple of the number of records that it's trying to work out. Iassume it tries to run a seperate plan for the view as part of it'sprocess.Ideally, I'm still going to go down the route of the temporary table,but I would like to understand more about what it does first as Idon't like leaving things unanswered.Any help would be appreciated.

View 6 Replies View Related

How To Insert Rows To Table B Based On Values In Table A.

Mar 7, 2008

I need Insert rows in the OrderDetails Table based on values in the Orders Table

In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType

For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD

Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.

I need a SQL Script to accomplish this. thanks

View 6 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008

I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table

Jan 19, 2008

Code Block

Hi,

I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:

Table Name: metrics_ladder

id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.

Table Name: metric_details

id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2

Table Name: metric_type_details

id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated

The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:

id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>

Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.

Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,

metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally

metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).

As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:

security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.

which is then unpivoted.

The SQL that achieves this is more or less as follows:

*********
START SQL
*********

declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)

-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;

with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id

*********
END SQL
*********

The result then needs to be unpivoted and stored in metrics_ladder.

And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.

Many thanks (if you've read this far!)

M.

View 6 Replies View Related

Selecting SUM From One Table As Field In Results Of Rows From Another Table.

Nov 20, 2007

Hi,
These aren't my tables, but lets dumb it down for my sake!

I have 2 tables:

CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount

There are several records in TransactionInfo for each record in CompanyInfo.

I want to:

SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)

How can I do this? Can I?

View 4 Replies View Related

Copy Rows To The Same Table And Its Related Data In The Other Table

Nov 23, 2007

Hi All,
I have 2 tables People & PeopleCosts.

PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber

The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..

Kindly help. thanks in advance.
Myl

View 3 Replies View Related

I Imported A SQL Table Into SQL DataBase, But I Can Not Update This Table Even With SQL Server Management Studio

Jan 8, 2008

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
When I change any data on mentioned table above, Red exclamation sign appears left of the record .
How can I correct this problem?
Thanks.

View 1 Replies View Related