Ok I have a database that looks as follows: Orders, Services, OrderUnits, Notes Orders: id Services: id, orderid Units: id, Orderid, unitnumber Notes: id, relatedid, text, date Orders have 1 to many order units Orders have 1 to many services Services have 1 to many notes I am trying to relate only the notes that relate to the unit ...
Hi, I have to write a query which should do the following task select SID from v$session where username = 'some user' and if there is any SID by that particular username then kill that SID using the command below : alter system kill session 'sid'; What I have wrtten currently is the following : alter system kill session where...
I have a table with this structure. UserID | UserName | AnswerToQuestion1 | AnswerToQuestion2 | AnswerToQuestion3 1 | John | 1 | 0 | 1 2 | Mary | 1 | 1 | 0 I can't figure out what SQL query I would use to get a result set like this: UserID | User...
I want to only get the unit number out of this string: '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>' The note is always exactly the same but the unit number changes. How do I extract just the unit number? Thanks! ...
I have the following query SELECT tagindex, AVG(val) from floatTable WHERE tagindex IN(828,856,883,910) AND DateAndTime > DATEADD(HH,-1,GETDATE()) AND DateAndTime < DATEADD(HH,-2,GETDATE()) group by tagindex It returns the following: 828 1 856 1 883 1 910 1 How can I return a single result where it is the combined a...
I want to remove some rogue HTML from a DB field that is supposed to contain a simple filename. Example of ok field: myfile.pdf Example of not ok field: myfile2.pdf<input type="hidden" id="gwProxy" />... Does anyone know a query I can run that can remove the HTML part but leave the filename? i.e. remove everything from the first < ...
If data is in the following format: SID TID Tdatetime QID QTotal ---------------------------------------- 100 1 01/12/97 9:00AM 66 110 100 1 01/12/97 9:00AM 66 110 100 1 01/12/97 10:00AM 67 110 100 2 01/19/97 9:00AM 66 . 100 2 01/19/97 9:00AM 66 110 100 2 01/19/97 10:00A...
Hello all, I need to know how to join two tables together with their timestamps. The timestamps differ consistently by 1.8 seconds every time and there is a data entry every half hour. any ideas? ...
We're customizing a CMS that has a few hundred tables in it. We are considering moving our custom tables to a separate database on the same server. Should we expect much of a slow down in SQL Server? There will be many multi-join stored procedures joining data across the CMS database and our custom database. ...
Is it possible to use a select in the middle of joining... I am trying to do the following: FROM tblorders o INNER JOIN tblunits u on o.id = u.orderid INNER JOIN ((SELECT ,Min(n.date) as [MinDate] from tblNotes n Where n.test = 'test') te INNER JOIN tblnotes n on te.id = n.id ...
ReporterTbl is one to many relationship >>> AttachmentTbl in ReporterTbl i have Id (101) and i can have AttachmentTbl more than one attachments related with ReporterTbl.Id SELECT ISNULL(ReporterTbl.Id, 0) AS Id, CONVERT(char(10), ReporterTbl.StartDate, 101) AS StartDate, ISNULL(ReporterTbl.PriorityId, 0) AS PriorityId, ISNULL(d...
I have to create an association between 2 products, which has unique product_ids and insert them into an already constructed table. The association is created based on unique part number these product ids have. For instance: Product_id = 7578711 Part Number = 0101-2478 Product Id = 7957948 Part Number = 0101-2478 Product Id = 1055814...
If I have a table such as 1 bob 1 ray 1 bob 1 ray 2 joe 2 joe And I want to select distinct based on the two columns so that I would get 1 bob 1 ray 2 joe How can I word my query? Is the only way to concatenate the columns and wrap them around a distinct function operator? ...
Hi all, i have an audit table in SQL server 2008 database which keeps track of all changes made in tables. It does a great job, except it can't catch username of the currently logged user, because my connection string uses one username for all connections. This app is written in ASP.NET MVC, this is why i have username stored in connec...
I'm hoping there's a simple way to do this without using a sub-query: Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key". So if the Table contained the rows: KEY SUBKEY VALUE 1 1 100 1 2 200 1 3 300 For Key = 1, I need the val...
I have an Indexed View with two columns, a primary key char and a field for full-text indexing varchar(300). I need to search from a MS Great Plains database, so I created the view to populate a field with concatenated values from my primary table IV00101. CREATE VIEW SearchablePartContent WITH SCHEMABINDING AS SELECT ITEMNMBR, rtrim(I...
I'm making a Q&A site, similar to this site and Yahoo answers. I have 3 tables - smf_members, qa_questions and qa_answers. In this query, I want to select some fields from qa_questions, a few fields from smf_members and the number of records in ga_answers for the question_id. This is so I can have some basic info on the question, some b...
I'm using the shortcut, via properties. When i only need to generate one identifier, i set "(Is) identity" to "Yes", but i can't seem to do it for two rows. Please Help. ...
What is the proper way to exclude results from a MySQL UNION? I'm looking for the equivalent to: (query1) UNION (query2) UNION (query3) EXCEPT (query4) ...
How does SSMS calculate the query cost (relative to the batch) value that is displayed when you select to include the actual execution plan? Mainly, I have a few saved execution plan XML files that I want to compare with each other. It must be calculating that number somehow from the data in the XML files, but I am not seeing how this ...