Query To Retrieve Records That Sum To A Field In Another Table
May 18, 2012
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).Â
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database. I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields. It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table. In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters. The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%') If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null> My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>. For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
Hi, does anyone know how to retrieve mistmatched records across 2 tables. To clarify, I have table A with 1175 records and Table B with 894 records. The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc but that doesn't seem to work.
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
I have a situation where I need a table if bad items to match to. Forexample, The main table may be as:Table Main:fd_Id INT IDENTITY (1, 1)fd_Type VARCHAR(100)Table Matcher:fd_SubType VARCHAR(20)Table Main might have a records like:1 | "This is some full amount of text"2 | "Here is half amount of text"3 | "Some more with a catch word"Table Matcher:"full""catch"I need to only get the records from the main table that do not haveanything in the match table. This should return only record 2.
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101 S102 | Def of S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39) FROM Tab2 WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SubjectCodeDefinition] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DepartmentReturn] GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition) SELECT 'S100', 'Definition of Code S100' UNION ALL SELECT 'S101', 'Definition of Code S101' UNION ALL SELECT 'S102', 'Definition of Code S102' UNION ALL SELECT 'S103', 'Definition of Code S103' UNION ALL SELECT 'S104', 'Definition of Code S104' UNION ALL SELECT 'S105', 'Definition of Code S105' GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes) SELECT 1,'P100','S100,S101,S104' UNION ALL SELECT 1,'P101','S102,S103' UNION ALL SELECT 1,'P102','S100,S101,S105' UNION ALL SELECT 2,'P100','S100,S101,S104,S105' UNION ALL SELECT 2,'P103','S103,S104,S105' UNION ALL SELECT 3,'P100','S100,S102,S104' GO
SELECT SubjectCode ,Definition FROM SubjectCodeDefinition WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39) FROM DepartmentReturn WHERE DeptID = 1 AND PurposeCode = 'P102')
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
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
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
SELECT table1.col1 ,€™n/a€™ _response FROM table1 INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form
If(Statusarray.count < 1) Set _response = €˜accepted€™ Else Get Statusarray[1] Compare this to Statusarray[0] Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
I need help to build a query that shows me how many columns inside a range on columns are null. Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Is there a way that I can reset the key field to 1 when using DELETE to clear a table? (Note: if there is a separate command that I could use after the DELETE, that is fine too.) Thank you for your help with this, -DJ
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
Hi, Let's say I have 1000 registered users in database table and each of them has numeric ranking value. How can I get the position of each user in comparison to other users ranking value?
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
I am using ADODB.Recordset to retrieve the records from an MS Access database. The table has 11450 records and the recordset shows this number as the recordcount too. But when I try to fetch the records in a While loop using EOF as the check, I am able fetch only 4000 or so records. No error occurs and no exception is thrown. Control just comes out of the function. Why could this be happening? Does Recordset have some limit on the number of records for Jet 4.0 provider?
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.
I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.
In house data:-Table fields contain (address,city,state,zip,zip4,fips) -All data type fields are character -No Primary Key
Local machine table: -Fipscodes (table) contain data was provided by customer that I inserted into my machine and column fields (state,zip,fips) made Zip as Primary Key.
So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.
FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n ON h.FIPS = n.FIPS
WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'
Error occur: Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'State'. Server: Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'FIPS'.
, please help me solve this issue and thank you very much everyone.
Hi, How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links. Table ----- Name Age ----------------- Raja 23 Kumar 26 Suresh 30 Rani 22 Subha 32 Ganesh 25
The result will be Name Age ----------------- Suresh 30 Rani 22 Subha 32
Hello,If I run an action SP from MS Access using ADO:...cmd.executewhere the SP is something like Create...Update tbl1 set fld1 = 'something' where...how can I retrive the count of records affected like from Queryanalyzer?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.
Please can someone advise me on how I can create a statement that will query each individual row within a table.
As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.
An Overview: We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly. As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.
Please find below the script - its very much in its infancy!!
CREATE PROCEDURE sp_reallocate AS select * from tbl_mobile_reallocate order by allocation_change_date
update tbl_mobile_import set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date
update tbl_mobile_reallocate set status = 'Closed' GO
