Transact SQL :: Use Query Results As Select Criteria For Another Query
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"." Here is my Query, sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go reconfigure SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , min(crtFinalTime) AS MinTime FROM tb....... INNER JOIN etc.. GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.
If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate") If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")
Changing the code to:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , MIN(crtFinalTime) AS MinTime , MIN(cdsStartDate) AS RaceDate , MIN(cdsDistanceNumber) AS DistanceNumber
FROM tb....... GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.
An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.
Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)
Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.
As an example a few times:
DistanceFirst nameLast name Time Date Distance nr. 500 Yuya Oikawa 34.49 201311155 500 Yuya Oikawa 34.49 201311153 500 Yuya Oikawa 34.49 201311172
Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure
declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin)
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB Â Â Â Â 123 colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Criteria Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4 WHERE sd='20060717' AND serial='0455' GROUP BY SUBSTRING(st,1,4),d_price,l_cum
After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option. After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:
Options | Query results | SQL Server | Default Destination for Results
set to "Results to File" and a path entered, but it doesn't work. Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?
I’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.  My code less the personal information   EXEC msdb.dbo.sp_send_dbmail    @profile_name = '',      @recipients = '',       @subject = 'Database Integrity - Import Errors',
I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:
I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:
SELECT A.AccountID, c.CustomerNo, c.Surname, c.Name, c.TaxNum FROM Accounts A left join AccountCustomer ac on ac.AccountID = A.AccountID left join Customers c on c.CustomerNo = ac.CustomerNo order by A.AccountID;
As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:
I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". Â The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". Â I will post the definitions from another effort below.
UID , Name, amount, start date                      End Date     1     A        10        2015-05-01 00:00:00            2015-05-01 23:59:59     2     A        10        2015-05-02 00:00:00            2015-05-02 23:59:59     3     A        10        2015-05-03 00:00:00            2015-05-03 23:59:59     4     A        10        2015-05-04 00:00:00            2015-05-04 23:59:59      5     B        10        2015-05-05 00:00:00            2015-05-05 23:59:59
my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader(); can we have something likeHashtable = cmd.ExecuteReader();
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
Hello, In the query analyser, I execute a select ' restore database ' + name + ' from disk = " D:sql7dumpackup ' + name + '' + Name + ' _du_21.bak " with move ' ' + name + ' _Données " to " e:sqlbasesasesdata ' + name ' _données.mdf ", move ' ' + name + ' _Journal " to " d:sqllogs ' + name + ' _journal.ldf ", replace From sysdatabases
And some lines are truncated in results ?. how to avoid that.
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST"> <select name="ManagerID" id="manager" style="width:400px" class="form_textbox"> <? $db_connect= mysql_connect($db_host, $db_username, $db_password); mysql_select_db($db_name, $db_connect) || die(mysql_error()); $sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet 'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater). Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml
Partial Public Class outputxml <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by the 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:
SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla FROM tableX.... WHERE call_status=open.....
I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.
At the moment the SELECT query above gives me the following results:
Call Ref sla
10001 In SLA 10002 Breach 10002 In SLA 10003 In SLA 10004 In SLA 10005 Breach 10005 In SLA ... ...
What it should look like is this....
Call Ref sla
10001 In SLA 10002 Breach 10003 In SLA 10004 In SLA 10005 Breach ... ...
Please let me know if anyone has the answers or any clues to this! thanks.
I have a query that returns the data about test cases. Â Each test case can have multiple bugs associated to it. Â I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription  BugID BugStatus 1         TestCase1            1    Closed 2         TestCase1            2    Open 3         TestCase2            11   Closed 4         TestCase2            12   Closed 5         TestCase2            13   Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages. for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on... is there any way to achieve it using an SQL query? the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway. any suggestions? Thanks.
I have  created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
 ALTER PROCEDURE [dbo].[RPT_Cost_copy] SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM INTO T1 FROM (SELECT a.meu, a.mep2, SUM(a.mest) as excst         FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''   Â
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable. INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in -- descending order
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
I have created a sample Table with inserting below data,
ID   Subject   CreatedDate 1    Test     2015-09-20 22:59:07.373 2    Test     2015-09-21 09:16:58.290 3    Test     2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query  for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?Â
I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.
eg. of summary. Recipe Name Total Weight Hedge Shears - Lasher/kudu 500 Grass Slasher 200 eg.
But it needs to summarize when selected between start and end date.
