Help..extract Sum Of Columns From Other Table ..help !
Jan 7, 2008
hi
i have 2 tables in DB. followind is the schema :
tblPostingAccount(PostingAccountCode,Description,type,OpeningBalance, Balance)
tblDetails(PostingAccountCode,debit,credit,VoucherNo)
i want to show all posting account codes from tblPostingAccount and their respective sum of debit and credit columns ..
like this
PositngAccountCOde,Debit,Credit,OpeningBalance,Balance
1-0-1-0-1 7000 6799 699999 9000000
1-0-1-0-2 7000 6799 699999 9000000
1-0-1-0-3 7000 6799 699999 9000000
1-0-1-0-4 7000 6799 699999 9000000
1-0-1-0-5 7000 6799 699999 9000000
1-0-1-0-6 Null Null 699999 9000000
the last code has nothing in tbldetails table ..i want a query from which i could show results as above ..
Plz help . Thanks in Advance .
View 4 Replies
ADVERTISEMENT
Apr 23, 2015
I have two columns "Project Name" and "Flag".
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
View 15 Replies
View Related
Jul 20, 2005
From the table i want everything highlighted with a *I wanted an SQl expression to look at values in Column 1 (ID), look atthe corresponding values in the second column (F1) and select the rowwith the highest value, and then if there was more than one row forthat ID with the same value in F1, look at column 3 (F2) and selectthe row with the highest value in this column.SQL> select * from test;ID F1 F2---------- ---------- ----------1 12 4* 1 12 61 11 11 9 122 3 5* 2 9 132 9 93 1 23 1 1* 3 7 5I try the followingSQL> select * from test t12 where f1 = (select max(f1) from test t2 where t2.id = t1.id)3 ;And get the followingID F1 F2---------- ---------- ----------1 12 4* 1 12 6* 2 9 132 9 9* 3 7 5If I add another line with an AND statement after line 2 I either getnothing or rubbish as the output.Ideas?Cheers
View 2 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Feb 23, 2006
Hi,
I'm inserting records into a Table1 with the following fields :
tabID int 4ClientID int 4..etc etc.
In the insertitem template, I want the user to enter a Clientcode into textbox1. Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1.
I've got a mental block and I cannot see how I can do that !
Any ideas most gratefully appreciated.
Thanks
Sunil
View 6 Replies
View Related
Mar 15, 2007
I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key
i want top 3 Name(field) in first table(Form1).
Result
------
xyzx
23wa
abcd
View 7 Replies
View Related
Mar 11, 2008
Hi all
I am trying to teach myself SSIS and need some help, please.
I have SQL table A as my source with column X. I need to add column Z and populate it from SQL table B, column Y where table A column X >= table B column R and <= table B column S.
Thus: Populate A.z with B.y where A.x >= B.r and A.x <=B.s
The interval between B.r and B.s is unique and only one value will be returned.
Which is the best way to do this in SSIS?
Thanks,
NewToETL
View 1 Replies
View Related
May 4, 1999
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
View 10 Replies
View Related
Jan 24, 2007
Hi,
I want to extract all fields from a table and ftp the output file to the mainframe. My problem is the columns in the extracted file do not line up, which is required by the mainframe program. Example column would be this column of last names:
Smith
Jones
Sullivan
How could I format the extracted data to look like this:
Smith
Jones
Sullivan
Thanks,
Jeff
View 8 Replies
View Related
Apr 21, 2014
There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:
time var1 var2 var3 var4
12:00:01PM value ...
12:00:05PM value
12:00:08PM value
12:00:20PM value
12:10:12PM value
...100 rows
How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:
time var1 var2 var3 var4
12:00:05PM value
12:00:08PM value
12:00:20PM value
I consider something like this:
SELECT * FROM mytable WHERE time BETWEEN ...?
View 2 Replies
View Related
Apr 28, 2014
Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q
Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001
Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011
I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011
May be some duplicate entry of same record is available in Table 1.
View 3 Replies
View Related
Jun 19, 2015
I need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,
Non Empty [Date].[Calendar].members on rows
from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
But I get this (MDX thru openquery or SSIS):
View 4 Replies
View Related
Sep 19, 2007
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
View 2 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Jul 22, 2013
I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
View 6 Replies
View Related
Nov 2, 2014
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
View 7 Replies
View Related
Aug 9, 2013
I'm using the following syntax to extract a list of all the table names on a linked server:
EXEC sp_tables_ex
@table_server = MY_SERVER_NAME
It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?
View 3 Replies
View Related
May 26, 2015
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
View 4 Replies
View Related
Jul 20, 2005
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies
View Related
Jun 25, 2014
What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
View 4 Replies
View Related
Apr 4, 2008
I'm in the process of converting legacy DTS packages to SSIS. I need to populate a table that has more fields than the source file. In DTS I did this with an ActiveX script. How do I go about doing this within SSIS.
In the ActiveX script most of the fields were defaulted with either spaces or zeroes.
One of the Destination fields needs to be incremented by 1 for each new record inserted.
Any help would be appreciated.
Thanks,
Jeff
View 6 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Apr 14, 2008
Hello All,
i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne
View 3 Replies
View Related
Dec 23, 2013
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
View 1 Replies
View Related
May 4, 2006
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
View 3 Replies
View Related
Jan 20, 2014
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
View 6 Replies
View Related
Feb 12, 2007
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
View 1 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Mar 20, 2006
I have a table A, with two ID columns. In a report both ID colums shouldbe shown with the actual value stored in a second table, BThe problem is, both IDs need to be looked up in B, but are not in thesame row.How do I do this in an efficient way? A sub select?Thanks,--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
View 5 Replies
View Related
Feb 5, 2007
I have two tables:
Orders, with OrderID as primary key, a code for the client, and a code for the place of delivery/receipant.
Both the client and place of delivery should be linked to the table:
Relations, where each relation has it's own PrimaryID which is a auto-numbered ID. Now I want to substract my orders, with both the clientcode, and the place of delivery code linked to the relations table, so that for both the name and adress is shown.
I can link one of them by:
InnerJoin On Orders.ClientID = Relations.ClientID, but it's not possible to also link to the ReceipantsID. Is there a way to solve this?
View 7 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Jul 19, 1999
What is the easiest way to determine the number of columns in a table (in a particular database)? Also is there any way to get the column id associated with each column (is there such thing)??
NOTE, this needs to be done with only knowing the name of the table and database and nothing else.
All help is greatly appreciated.
Thanks in advance.
ziggy
View 1 Replies
View Related
