I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:
a
b
c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Using SQL 2000, how can you combine multiple records into 1?The source data is varchar(255), the destination will be text. I need helpwith the select statement.example tables:CREATE TABLE [NoteHeader] ([NoteID] [int],[CustomerID] [int] ,[Desc1] [varchar] (255),[Date] [datetime] ,)GOCREATE TABLE [NoteDetail] ([NoteId] [int],[SeqNum] [int] NOT NULL ,[Note1] [varchar] (255),[Note2] [varchar] (255),[Note3] [varchar] (255),[Note4] [varchar] (255),[Note5] [varchar] (255))GOSample script joining tables:SELECT *FROM NoteHeader INNER JOINNoteDetail ON NoteHeader.NoteID = NoteDetail.NoteIdSample results:NoteID CustomerID Desc1 DateNote1 Note2.....Note51111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 Notes detail record 1 field2 .....1111 987 Note Header Description 2007-07-15Notes detail record 2 field 1 Notes detail record 2 field 2Desired results:NoteID CustomerID Desc1 DateCombinedNotes1111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 +Notes detail record 1 field2 +Notes detail record 2 field 1 +Notes detail record 2 field 2 +through unlimited number of records up to 5fields eachThe NoteID field is the unique number. 1 record per NoteID in NoteHeader,NoteDetail can have unlimited number of same NoteID (usually not more than10)
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. [wo_No] [prt_Mark] [dwg_Seq] [324037] [d400] [1S2] [324037] [d400] [A1] [324037] [d400] [1]
What I would like is to combine the dwg_Seq into one record where they have the same wo_No and prt_Mark. [324037] [d400] [1S2, A1, 1]
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt, case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid, case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt FROM BI50_BankTransactions_AR_InvcDt_H where cus_no is not null group by cus_no, Inv_no
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. wo_No | prt_Mark | dwg_Seq | 324037 | d400 | 1S2 | 324037 | d400 | A1 | 324037 | d400 | 1 | 219001 | 56 | 2B | 219001 | 56 | 2C | 219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark. 324037 | d400 | 1S2,A1,1 | 219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are. 324037 | d400 | A1 + 2 | 219001 | 56 | 2B + 1 |
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
Result: combine tblShipmentHdr + attached tblShipmentDet records with tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005' AS SELECT SHPH.ProductID, SHPH.ReceivedDate, SHPH.ShipmentNo, SHPD.Vendor, SHPD.Quantity, QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ReceivedDate END, QShipQty = CASE WHEN SHPD.TransCd = 'F' THEN NULL WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ShippingQty END, PROD.ProductName, QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null THEN 0 ELSE MEND.OpeningAccrual END FROM dbo.tblShipmentHdr SHPH LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7) LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record, Table1 has the primary key id -- 1 2 3 4 Table2 has the follwing records id year subject ----------------- 1 2000 English 1 2002 French 2 2004 English 2 2005 English 2 2006 English 3 2007 French I want the result to be like this id 2000 2001 2002 2003 2004 2005 2006 2007 ----------------------------------------------------------- 1 English null French null null null null null 2 null null null null English English English null 3 null null null null null null null English
I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps. at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)??? Thanks in advance.
Hi all, I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ? SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1 FROM dbo.Orders WHERE (ShipVia = 1) GROUP BY EmployeeID ORDER BY EmployeeID
Table 1 Results EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29 SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2 FROM dbo.Orders WHERE (ShipVia = 2) GROUP BY EmployeeID ORDER BY EmployeeID Table 2 results EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19 Table 3 the desired result: EmployeeID CountShipVia1 CountShipVia2
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer AS DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest ( Zip, UpsRegion, UspsRegion ) SELECT CMRC_ZipRegionUps.Zip, CMRC_ZipRegionUps.UpsRegion, CMRC_ZipRegionUsps.UspsRegion FROM CMRC_ZipRegion, CMRC_ZipRegionUsps GO
Is there any way to do this? Or do I have to manually enter all the entries?
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two). insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName" insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)" I have tried: Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment) Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated! Thanks in advance.
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT [tblTickets].[id] AS TicketId, SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal FROM [tblOrders] INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id] INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT [tblCombinations_Tickets].[ticket_id] AS cTicketId, SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal FROM [tblOrders] INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id] INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
Not concatenation, more... err.. I don't know what you'd call it.
SELECT DISTINCT [C01241 Opened].[Col004] AS OpenerEmail, [C01241 External Data].[DMCEMAIL] AS ExternalDataEmail, [C01241 Internal Data].[Col15] AS InternalDataEmail FROM [C01241 Opened] LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004] LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
(Apologies for the table/col names, this is all very temporary)
So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.
At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.
What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine -------------------------------------------------------------------- SELECT CASE when t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1 Else (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) End, t1.Airway_Bill_No, t1.Shipper_Reference, t1.External_Product_Cd, t1.Chargeable_Weight, dt.CountTrackingNumber, dt.SumProductCharge, t1.Consignee_Company_Name, CONVERT(CHAR(8),Ship_DT,112) FROM Shipping t1 Inner Join ( SELECT Cust_DB_Shipment_Key, sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge], count(Airway_Bill_No) as [CountTrackingNumber] FROM Shipping Where Ship_DT = '2008-05-12' and status != 'voided' GROUP BY Cust_DB_Shipment_Key ) dt ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) = (stuff(dt.Cust_DB_Shipment_Key,1,5,'')) Where Ship_DT = '2008-05-12' and status != 'voided'
---------------------------------------------------------------------- SELECT CASE when Cust_DB_Shipment_Key = 'Used:' Then Description_1 Else (stuff(Cust_DB_Shipment_Key,1,5,'')) End, Airway_Bill_No, Shipper_Reference, Service_Name, Chargeable_Weight, Consignee_Company_Name, CONVERT(CHAR(8),Ship_DT,112) FROM Shipping s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code Where Ship_DT = '2008-05-12' and status != 'voided'
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production FROM dbo.job left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid WHERE dbo.Job.CompanyJobId = 3505048 and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA') GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048 SR 434 T-5201SR 434 T-5201 2007-10-11 16:36:45.647 Y 1314.26 (material qty) 1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours fromdbo.job left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production, sum(EmployeeLaborEvent.Hours) as hours FROM dbo.job left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA') GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048 SR 434 T-5201SR 434 T-5201 2007-10-11 16:36:45.647 Y 1314.26(material) 1569.26 (production) NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
