Join Two Datasets From A Similar Column
Apr 23, 2008
Hi, I was wondering wich is teh easiest way to solve this:
I have one data set that comes from a sql query with many columns.
I have another data set that comes from a txt file that I am reading.
Both data set have one column in common (for example ID).
In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.
So, which is the easiest way to solve this?
Thanks!
View 6 Replies
ADVERTISEMENT
Apr 10, 2008
Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
View 5 Replies
View Related
Feb 23, 2015
Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
employeeID
R_12345678
S_5555555
T_777777
U_7777
Our new data structure simply removed the letter & underscore.
employeeID
12345678
5555555
777777
7777
Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?
View 9 Replies
View Related
May 12, 2006
Hi,
In one of my packages, I have join data from 2 data sets. The problem is 'Join' requires both dataset to be sorted and I can't sort one dataset without 'sort' transformation. The real problem is since I have to sort 65million records, the sort operation sucks always. The other option is using 'Lookup', but since I have to do 'Range Lookup', it is taking hours and hours to process less # of records.
My question is, is there any other way, I can do join of 2 datasets without sorting?
View 9 Replies
View Related
Aug 5, 2015
Struggling to come up with an overall sum for each individual user when using the below code
SELECT
Practice.ibvStaffCategorisation.StaffId
,Practice.ibvStaffTotalsCL2Y.Period
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
[Code] ....
As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below
=Lookup(Fields!StaffId.Value, Fields!StaffId.Value, Fields!Sum_ChargeableAmount.Value, "ABSTimeRollingY")
I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.
Is there a way to do this within the dataset or via the query in the table?
View 2 Replies
View Related
Jun 17, 2015
I want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:
I want to display a ssrs table like:
Both the datasets are coming from different sources. So I cannot integrate them at sql query level.
View 4 Replies
View Related
Nov 21, 2007
Hi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
View 1 Replies
View Related
Sep 20, 2007
My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!
View 3 Replies
View Related
Sep 16, 2015
I am sharing one sql query and o/p:
select distinct case
when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
when LastExecutionResult = '17023' then 'User cancelled installation'
when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
View 2 Replies
View Related
Jun 23, 2015
I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .
I would like to create a tabular report datasets on the report like shown below with grouping on col1
like
col1 some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.
View 2 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Sep 27, 2015
My report has two data sets that hold inventory from two different departments.
ds_DeptA and ds_DeptB
I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine. Typically means DeptB does not have the record yet. I need to count these empty (null) feilds and populate it in a Text box outside of the table.
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset.
table: ds_DeptA
fields:
ID
Name
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")
View 3 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Sep 8, 2014
I have two tables.
One called Boats with fields
IDSerialNo AreaCompany
There is another table called Equipment with fields
ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I dont want the other fields appearing more than once in the query.
View 4 Replies
View Related
Apr 3, 2007
I have 2 tables, my vehicle data table and my config table. I need a
query to join them by a datarow and a data column. Heres my tables...config table--------------------id name type--------------------1 make varchar2 model varchar3 color varcharveh table--------------------------id make model color--------------------------1 chevy s10 white2 ford ranger silver2 chevy blazer brownrecordset needed for veh.id=1---------------------------id name type value---------------------------1 make varchar chevy2 model varchar s103 color varchar white Thanks for any helpRyan
View 1 Replies
View Related
Aug 16, 1999
I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1
The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16"
like this
CREATE VIEW subaccounts
(Subacct_no, balance)
AS SELECT
(s.Account_No + "." + r.Subacct_Ext, S.Balance)
FROM Acct_Rcd r, Subacct_Key s
WHERE
(s.attr1 = r.attr1 OR s.attr1 IS NULL)
AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)
View 3 Replies
View Related
Apr 6, 2007
Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.
Code:
select
id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
FROM se_links INNER JOIN
se_index AS se_index_1 ON
se_links.target_index_id = se_index_1.id AND
se_links.anchor LIKE :query))
View 1 Replies
View Related
Oct 2, 2013
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC
View 5 Replies
View Related
Nov 26, 2013
How to accomplish a join when the join condition has to be parsed from another column? I need a list of all the exchange part numbers with exchange part pricing, but need the description from the original part number. Example:
Part Number Description Cost
123-456-1 A ROUND THINGIE 5
150-387-1 EXCH - 123-456-1 2
Needed record is
Part number ExchangePart Description Cost
123-456-1 150-387-1 A ROUND THINGIE 2
Something like
SELECT PART# as exchangepart,
case
when substring(descr, 1,4) = 'EXC-' then trim(substring(DESCR, 5, 25))
when substring(descr, 1,3) = 'EXC-' then trim(substring(DESCR, 4, 26))
when substring(descr, 1,6) = 'EXC - ' then trim(substring(DESCR, 7, 23))
when substring(descr, 1,5) = 'EXCH-' then trim(substring(DESCR,6,24))
[Code] .....
View 2 Replies
View Related
Jul 23, 2005
Group,Passing inline values to a udf is straightforward. However, how or is itpossible to pass a column from the select of one table into a udf thatreturns a table variable in a join to the original table. The goal is toexplode the number of rows in the result set out based on the result of theudf. Although the example I am providing here is simplified, we are tryingto parse out multiple values out of a text column and using a select into sothe result will be one row for each of row in the original table compoundedby the number of occurrences in the text field.(I know bad table design but that's out of my control)Thanks,Raycreate table TableOne(Col1 int,Col2 char(5))goinsert TableOne values (1, 'One')insert TableOne values (2, 'Two')insert TableOne values (3, 'Three')goCreate Function dbo.udfTableTwo(@Id int)RETURNS @tbl TABLE (TID int, TChar char(1))ASBEGINDeclare @test intset @test = @IdWhile @test >= 0BeginINSERT @tbl VALUES (@Id, Char(65+@test))set @test = @test - 1EndRETURNEND--worksselect a.*, b.* from TableOne a join dbo.udfTableTwo(2) bon a.col1=b.TID--Fails with Line 1: Incorrect syntax near '.'.select a.*, b.* from TableOne a join dbo.udfTableTwo(a.col1) bon a.col1=b.TIDdrop table TableOnegodrop function dbo.udfTableTwogo
View 6 Replies
View Related
Mar 17, 2006
Thanks in advance for any help.I'm trying to attempt the following with one table/query.I have a table called TABLE_1It has the following fields:SSNTITLELOCATION322EXDALLAS568REPFT_WORTH536EXDALLAS485SLSYOUNGSTOWN854BROFEEDER258EXEVANSVILLE478TEMPTROY861SLSDALLASI want toselect SSN, TITLE, LOCATIONfrom table_1where ssn = ('322', '536', '258', '478', '861')and title = 'EX'and location = 'DALLAS'The problem, however, it that I want all of the SSN records on my list.My table should look like this:SSNTITLELOCATION322EXDALLAS536EXDALLAS258nullnull478nullnull861nullnullI can easily do this on two tables with a left outer join. I do notknow how to accomplish same (theory) with one table.Any help on how to do this would be appreciated. Thanks
View 8 Replies
View Related
Apr 23, 2008
Hello all,
I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !
eg.
Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6
I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6
I need to use SELECT * as there are many many columns and wish to save time :)
Cheers,
James
View 3 Replies
View Related
Feb 1, 2014
I'm trying to join two columns together to form a new column
My code is basically in the form of can't post the actual since it would be cheating--school assignment
SELECT Column1Name,Column2Name, Column3Name,Column4Name,
Column1Name+Column2Name AS NewColumn1
Column3Name+Column4Name AS NewColumn1
FROM OriginalTable
View 1 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
Mar 23, 2007
Hi,very new to SQL queries, and strugling with join conceptI had to do a join based on a single field:select*fromtableA, tableBwheretableA.value = tableB.value(+);this works finebut how can i do the same thing while comparing multiple columns ... iwas thinking something like this: (obviously doesn't work)select*fromtableA, tableBwhere[tableA.value1, tableA.value2] = [tableB.value1,tableB.value2](+);Is there some sort of "tuple" comparison I can do?Thank you.
View 2 Replies
View Related
Mar 28, 2006
Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
declare @inv table (item varchar(50), supplierid int)
declare @supplier table (supplierid int, weight int)
set nocount on
insert into @inv values ('item1', 1)
insert into @inv values ('item1', 2)
insert into @inv values ('item2', 2)
insert into @inv values ('item2', 3)
insert into @supplier values(1, 30)
insert into @supplier values(2, 20)
insert into @supplier values(3, 10)
-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3
select item, ps2.supplierid from @supplier ps2 join
(select item, min(ps.weight)'weight'
from @inv inv join @supplier ps on inv.supplierid=ps.supplierid
group by item) iw on ps2.weight=iw.weight
Is there a better alternative to this?
Thanks in advance,
Mike
View 3 Replies
View Related
May 4, 2007
Hi,
I have three tables.
The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.
I need to do a join to a second table based on the MasterID...
However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.
Make sense?
Anyone got any pointers?
Thanks in advance
James
View 8 Replies
View Related
Oct 16, 2015
I'm trying to do the following:
update a tables value(a single column), based on a query of another database, using a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
[Code] ...
print @trackingNoThe error it's returning is:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data
View 8 Replies
View Related
Oct 12, 2013
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum
View 1 Replies
View Related
Oct 29, 2014
Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.
table is 'adrmst'
Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206
table is 'shipment'
Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD
View 4 Replies
View Related
Feb 25, 2008
Hello,
I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.
So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
column.SortKeyPosition = 1
Next
End Sub
I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.
Thanks!
View 5 Replies
View Related
Sep 1, 2006
Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...
View 3 Replies
View Related
Oct 27, 2007
Hi I'd like to select two columns out of one column from another table. Example from source table:
Code Block
ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132
I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:
Code Block
ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132
View 20 Replies
View Related
