'One Table' Record To Separate 'two Or Three Tables' Using Cursor?
Jan 28, 2005
I would like to 'one table' record to separate 'two or three tables'
. I just know use the DTS , try to import and export again and agian.
So trouble.
Could you give me some suggestions for me? For example ,
'Cursor' write in new table . But I try to SQL Server Books Online
which is not suitable for me solving problems. One table separate two
or three tables. Can you wirte the detail example for me?
Thx a lot.
View 1 Replies
ADVERTISEMENT
Oct 31, 2007
Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P.
View 8 Replies
View Related
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Sep 7, 2005
Hi,
We are building an application for online system for people to place ADs for selling various used items like Car, Electronics, Houses, Books etc.
If someone selling a car then he can fill out headline, year, make, model, mileage, transmission, condition, color, price, description, contact etc.
Similarly if someone selling a digital camera he will fillout headline, memory, zoom, megapixel, maker, model, color, batter, description etc.
Option 1: I can have a main table to hold the common attributes of all different types of ADs (headline, images, contact, price, color, condition, description)
+ 1 table to store string values of all ADs (car: maker, model, square feet (if house), memory, megapixel (camera) etc)
+ 1 table to store the droplist select values(car: transmission, door, seat etc; house: year_built)
pros: single table for all ADs. unique IDs for all ADs, easy to extend as new attributes can be dropped easily.
cons: lot of physical reads of 2nd and 3rd table from join. 10 times physical reads compared to option 2 when reading 5000 records.
Option 2: have different set of table for each AD type. Car will have its own main table + 1 table to store multiselect list box values.
Similarly housing will have its own set of tables
pros: 10% less physical read than option 1.
cons: hard to add new attributes. We have to modify the main table by adding one column.
Query will go to different table based on the category.
Do you have any suggestions on which way to go?Thanks
View 2 Replies
View Related
Feb 2, 2002
Hi, I'm newbie in SQL, could somebody tell me how do I walk through a table one record at a time without using a cursor please.
Greatly appreciated.
Ann
View 4 Replies
View Related
Feb 15, 2012
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies
View Related
May 16, 2008
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
View 7 Replies
View Related
Sep 5, 2007
Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7
The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?
Many Thanks,
probetatester@yahoo.com
View 7 Replies
View Related
Jul 23, 2005
Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.
View 3 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
Mar 3, 2001
I am new to T-SQL and triggers
Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert
AS
Update customer SET cust_id =( SELECT
Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id))))
+ Convert (varchar(10),i.id)
+ Substring(i.lastname,1,3)
+ Substring(i.firstname,1,1)
from employee C INNER JOIN inserted i on i.id=c.id)
from employee C INNER JOIN inserted i on i.id=c.id
Saad
View 2 Replies
View Related
Apr 29, 2014
I have two tables
Books
-BookID
-CustomerID
Magazines
-MagazineID
-CustomerID
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
CustomerID, BookCount, MagazineCount
View 3 Replies
View Related
Nov 4, 2007
Hi,
I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks
----
DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50);
DECLARE @MyVariable CURSOR;
SET @MyVariable = CURSOR FOR
SELECT isnull(Filters.WorkflowIDs, ''),
isnull(FilterElements.CreatedByUser, '')
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID
WHERE FiltersItemID = @FilterID;
OPEN @MyVariable;FETCH NEXT FROM @MyVariable
INTO @WorkflowIDs, @CreatedByUser;
View 3 Replies
View Related
Apr 20, 2004
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END
CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO
View 1 Replies
View Related
Jan 5, 2001
We have some tables that we have spread across two databases. The segregation isnt essential, but the entities involved were disparate enough that we thought it made sense. However, our client app regularly & frequently requires information that can only be answered by queries to tables in both databases. It has been suggested that segregating the tables as we have introduces a performance hit. At this stage, it would be relatively easy to re-combine the tables into one DB.
View 1 Replies
View Related
Apr 7, 2008
I've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks.
What does this mean exactly and how would you configure this?
Is this a common practice in high-performance real-world environments (or should it be)?
View 3 Replies
View Related
Jul 30, 2014
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
View 9 Replies
View Related
Aug 20, 2014
I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.
My end result should look like the below example.
Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0
So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.
Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.
--create the temp table
Create table #SalesLine
(
Novarchar (50) not null
, LocationCodevarchar (50) not null
, QtyCommitint not null
)
create table #ItemLedgerEntry
[code]....
I am close to the desired results but can't find a way.
View 9 Replies
View Related
Jul 20, 2005
I have a report that's created each day as a flat textfile.Because I came from the Access world, I created a macro that importsit with a schema that gives meaningful names to the various columns,and then uses a query to massage some of the data for me (deletes thefirst blank row and does a couple of calculations)Then I use DTS to import the Access query as a table.the textfile has a column called "File_num", and among several others,a column called "Serial_num". (the file numbers represent shipments,and sometimes there are more than one serial number in the shipment,etc., so there is a separate line for every serial number)Naturally, I would like to split this info into two tables..one thatdoes not contain the serial numbers and has a primary key on the"File_num" column, and another table that would contain just the"File_num" and "Serial_num" columns. That way I could relate themlater...but most importantly, it will give me a table where I can usethe "File_num" as my primary key.What would be the best way to import these two tables from one sourcetextfile? The other thing that gives me problems is that the textfile has no column names, and the first row is always blank.I'm very new to SQL and DTS and would appreciate any direction.Thanks,Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
View 1 Replies
View Related
Jul 20, 2005
Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS
View 5 Replies
View Related
Apr 17, 2008
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
-El Salsero
View 5 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Apr 29, 2015
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
View 6 Replies
View Related
Sep 25, 2007
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
View 1 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
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
Feb 10, 2007
How can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.
thanks.
For example:
a: data
b: data
c: data
a: data
b: data
c: data
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 17, 2014
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies
View Related
Jul 27, 2007
I'm using SSRS SP2. I have a table with 1 group defined. I have a group footer that includes sub totals for each group. I have a table footer that includes my grand totals, but this footer will NOT print on a separate page even though I have 'Page Break At End' checked for the group. My groups correctly start on a new page, but when I get to my Grand Total footer line, it prints just after the last group sub-total line.
Any ideas what I might be doing wrong?
Thanks in advance,
Dawn J
View 4 Replies
View Related
Aug 18, 2015
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies
View Related
Apr 23, 2008
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
View 4 Replies
View Related
