Pulling Data Back From 2 Tables - Join On Top 1 Record
Nov 7, 2013
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID
I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue
--===== Create the test table with
CREATE TABLE #workorder ( Id varchar(20), wo varchar(10), wodate datetime, amount float ) GO CREATE TABLE #revenue
Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row. Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin', leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType', SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption', leg_depart_time_local AS 'DeptTime', leg_arrive_time_local AS 'ArrTime', dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays', TruckEditor.EffectiveDays as 'NewEffectiveDays' FROM lhif_prod JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd AND Type_Code = 'T' AND leg_orig = @LocID ORDER BY RteNum, Dest, DeptTime Here is what comes back from this query:ABE00 ABEA ABER CTV5 H 1855 1915 MTWT--- NULLABE01 ABEA ABER CTV5 H 1941 2001 MTWT--- NULLABE02 ABEA ABER CTV5 H 2045 2105 MTWTF-- NULLABE03 ABEA ABER CTV5 H 2059 2119 MTWTF-- NULLABE04 ABEA ABER CTV2.5 H 2245 2305 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- MT-TF--ABE12 ABEA ABER WALKIN H 2109 2124 MTWTF-- NULLEF038 ABEA EWRHB 53BULK H 0100 0245 -TWTFS- NULLEF085 ABEA EWRHA CTV5 H 1955 2140 MTWT--- NULLEF106 ABEA EWRHB CTV5 H 1901 2046 -----S- NULLEF140 ABEA ABER CTV5 H 0550 0610 M------ NULLEF166 ABEA EWRRA CTV5 H 2230 0010 MTWT--- NULLEF366 ABEA EWRRA CTV5 H 2230 0010 ----F-- NULLEF543 ABEA EWRRA CTV5 H 2200 2345 MTWTF-- NULL The 2 rows in bold are the issue right now. There should only be 1 row (the 2nd one where the last column is not null). I'm not sure why it returns both columns when I'm doing a join on there to add that last column. Can anyone help me out with this? I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide. Thanks.
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?
I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.
What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.
Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.
Any help would be greatly appreciated!! Thanks again!
Records in tblAPPRAISALS Table:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 A | 1/10/2003 | 5 A | 1/10/2002 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2004 | X B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
My current SQL:
SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating] FROM tblAPPRAISALS WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X" Order by 1, 2 DESC
Results from this Query:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5
Desired Results:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
Code Snippet select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb from tbl2 left JOIN tbl1 ON tbl2.Pcb=tbl1.pcb where tbl1.serial>='1' and tbl1.serial<='53' and tbl2."Result" like 'pass' and tbl1."result" like 'pass' group by tbl2.pcb,tbl1.serial
And this works correctly.But unfortunately i also need data1 and data2 columns from tbl2. If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records.I mean it would return from tbl2, all records containing pcb1,pcb2.
How can i resolve this issue? Thank you very much,
Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPriceContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPriceCustomer: CustomerNumber, CustomerDepartment, Contract1 – Contract4ContractNum ‘99’ is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have. Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function. SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description, Contracts.Quantity AS MinContractQty, COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice) AS Price, Inventory.WhlCatalogPrice AS ListPriceFROM Inventory LEFT OUTER JOIN Contracts ON Inventory.ItemNumber = Contracts.ItemNumber AND Inventory.Company = Contracts.Company AND '99' = Contracts.ContractNumberWHERE (Inventory.ItemNumber = N'444') AND (Inventory.Company = N'035')
BrokersCode BrokerName 100 John 101 muller 102 ABC
Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking. i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original. (No of records in booking table should be same after join as before, the thing is only to show brokers names) .
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data.
I think the solution requires a subquery in the FROM portion of the statement, but I am not sure how to structure it. Here are the Fields:
Products.UPC, Data.UPC, Data.Store_Num, Stores.StoreNum, Data.StartDate, Dates.DateKey, and finally the reason we are here Data.Qty
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code: TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME 1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI 2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS in the code for the join,
Code: left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.
Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.
However, no matter what I did I always got 99 records when I joined.
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID inner join ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as: select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID , all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this? I tried to create a function to get the latest sentence for a chargeID like the following: select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it? thanks,
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote
is there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14 Unclosed quotation mark after the character string ''.
Select Name From Table Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
I'm trying to pull data from about 30 progress databases using DTS andscheduling the jobs to run monthly. I'd like to pull data betweenspecific dates, but for some reason, I can't figure out how to filterthe data on the progress side.I want to run a query that will pull all data fromprior-month/8/current-yearandcurrent-month/15/current-yearI'll also have to account for when it's january, make it december ofthe year before.Any ideas?thanks,M@
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Hi friends, I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO. I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Can any one tell me how to transfer bulk data from SQL Server6.5 to SQL Server2000.I have tried bcp out and bcp in to SQL Server2000 but it is taking very long time.Is there any way that I can pull the data into SQL Server2000 from SQL Server6.5.Can I use DTS feature of SQL Server2000 to pull the data from SQL Server6.5..
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
E.G.
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT ETC...........
I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description col3 and col4 - go together col3 = amount col4 description col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram
This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross. Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K. Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.
Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?
Hey guys, I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items. I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId) I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable. Any help or suggestions would be great. Thanks, Matt
I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file. Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?
