Selecting The Most Recently Edited Item AND Selecting A Certain Type If Another Doesn't Exist
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like: [AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int] [Type] [nvarchar](10) [Address] [varchar](50) [City] [varchar](50) [State] [char](2) [Zip] [varchar](5) [AddDate] [datetime] [EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.
I have 2 tables. One table with schedules and one with attended time.
What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?
Example scheduled records student1 2/4/2004 7 hours scheduled student2 2/4/2004 8 hours scheduled student1 2/3/2004 8 hours scheduled student1 2/2/2004 6 hours scheduled <---Need this one student2 2/2/2004 8 hours scheduled student2 1/30/2004 4 hours scheduled <---Need this one ...thousands more...
It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.
I did some tsql a year or so ago, it wasnt much but it allows me to do most things i need to do in my job.
I have created a new database for bookings, each booking has all the relevant details and also the date that it will occur. im trying to create a script that will look for the 10 soonest dates that there is no booking for. at first i thought it would be easy, but now once i have got round to creating it i cant seem to think of a way to make it work!
Hello all, I have an odd requirement. I have a column with a systemgenerated username that is a 6 character, alphanumeric, field. Theseusernames are randomly generated by code. I need to create a storedprocedure that will return all combinations that are not alreadybeingused. Maybe the result of still trying to wake-up from a longweekend.But, I cannot think of an easy way to do this.Any help would be greatly appreciated.Best regards,rbr
Hello. I need to create a page with asp 3 and sql server 2000 that lists all the records in a table that match a date criterion, but when doing so I get an error "Error Converting datetime from character string"... I've tried this versions of the query:
Code:
SELECT (...) CONVERT(DATETIME,PAYMENTS_RECEIVED.PYR_DATE,103), (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE BETWEEN '20/03/2006' AND '21/03/2006')
and...
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME,'20/03/2006',103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME,'21/03/2006',103))
I also tried adding " 00:00:00 a.m." to the dates to be converted in the second piece of code. If I recall correctly, "103" is the convertion code meaning "dd/mm/yyyy". That is, I expect people to type in a date like "27/03/2006" and get the table records from that date.
Weirdest thing of all is that, when using the query builder of a sqlsource control in Visual Studio Web Developer Express 2005 this following query works just fine as I expect:
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME, '20/03/2006', 103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME, ' 21 / 03 / 2006 ', 103))
Both the server and the DB are the same in all cases, but in the first two I run the query from a (working of other queries) vbscript asp 3 page.
I know that similar posts exist already (I've searched for this before posting), but I can't fix it, at least not with the usual answer on this one (that seems to be using the convert function as in the first piece of code). Thanks in advance!!!
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
I am trying to select all entries from a database apart from the top/latest entry, via a stored procedure. Below is the code that i have but i am doing it wrong somehow.
Any ideas?
Thanks
CREATE PROCEDURE spNEWSARCHIVE AS
SELECT * FROM tblNEWS WHERE NewsID NOT TOP 1 ORDER BY NewsID DESC GO
Hi,I have the following set and would like to select all rows that havemin value of column 4 for a given column 1 and 2 group, irrespective ofcolumn 3, as below:C1 C2 C3 C4---------------------A B x 5A B y 10A BB XX 55A BB YY 11AA CC z 1AA CC zz 11Need---A B x 5A BB YY 11AA CC z 1using sql server 2000 (which does not provide rank or partition by)Thanks in advance,Tamas
Hello,I have a gridview bound to a sqldatasource control. I have an emptydatatemplate setup. In the selecting event, I make sure that an actual select has been performed (and not on null data); however, I have cancelselectonnullparameters set to true to stop it, because initially it will be null.However, the selecting event runs, even when I cancel it, the emptydatatemplate shows up. How do I prevent that from occuring?
Dear All, I need to select records between two datetimes. My Database Structure and sample values are as below Fromdate ToDate 2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000 Query i used select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00' Please help Thanks
Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
hello all, i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx. private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
How can I select data between Mth row and Nth row from a specific search condition? for example , I want to get the 5th to 10th employee who is born in March and ordered by their name.
I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?
Here is a code snippet of what is going on: Do While varCount < varRecordCount conSqlConnect.Open() cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@idnumber and date<@date", conSqlConnect) cmdSelect.Parameters.Add( "@accountnumber", "10000" ) cmdSelect.Parameters.Add( "@date", dtnow ) dtrdatareader = cmdSelect.ExecuteReader() While dtrdatareader.Read() If IsDbNull(dtrdatareader("value")) = false Then varValue = dtrdatareader("value") End If End While dtrdatareader.Close() conSqlConnect.Close()
'#####The information above is copied to another table here '#####The record where the information was received is deleted.
I have a tbale with maybe 30 columns and I'm selecting all where a record number matches a parameter I've passed. That works fine, however is there any way to do some conversions to fields when selecting without having to list out each column? For instance this is what it looks like now:
SELECT * FROM Losses WHERE @AppRecord = AppRecord ORDER BY Record ASC
But I want to convert say one column, is there a way to keep similar syntax instead of listing out each column?
SELECT *, CONVERT(varchar(15), [Losses]) as [Losses] FROM Losses WHERE @AppRecord = AppRecord ORDER BY Record ASC
And the column I'm converting is of type 'money'. I'm converting it to get rid of the extra zeroes at the end. If you know a better way to do it I'd be interested in knowing.
CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_GetAllIssues]@moduleId intASSELECT PreciseData_IssueTracker_Issue.id AS 'issueId',PreciseData_IssueTracker_Status.name AS 'statusName',PreciseData_IssueTracker_Issue.subject AS 'subject',PreciseData_IssueTracker_Type.name AS 'typeName',Users.Username AS 'assignedUserName',Users.Username AS 'raiserUserName',PreciseData_IssueTracker_Issue.raiseDate AS 'raiseDate'FROMPreciseData_IssueTracker_Issue INNER JOIN PreciseData_IssueTracker_Status ON PreciseData_IssueTracker_Issue.statusId=PreciseData_IssueTracker_Status.idINNER JOIN PreciseData_IssueTracker_Type ON PreciseData_IssueTracker_Issue.typeId=PreciseData_IssueTracker_Type.idINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.assignedUserIdINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.raiserUserIdWHERE PreciseData_IssueTracker_Issue.moduleId=@moduleIdORDER BY PreciseData_IssueTracker_Issue.raiseDate DESCGOHow do i make it work?
I have a content management system that I am building. My CMS has a pages table which contains 60 fields, some are varchars, some are ntext some are dates etc. They have field names which match their purpose like varchar1, ntext3 etc. These fields are used up depending on which template is applied to the page.
The templatefields table holds holds the information about each field in a template. It's control type (rich text control, date control etc) and the field in the pages table that the data will be stored in (varchar1,ntext3 etc)
What I want to be able to do is loop through the templatefeilds table for a particular template and then using the field name for the pages table, go to the pages table and get the value to populate the control.
So to clarify, I would like to produce a stored procedure which outputs the page field name (varchar1) from the templatefields table and varchar1's value in the pages table. Maybe in a temporary table.
Hi -- What is the proper way to select results with a date in the where statement when the datatype of the column is datetime? select * from table where date_field = '3-23-2006' does not get any results.
We are developing an Access application that will be used to run adhoc reports against a SQL Server table. Selection criteria is entered on a form, a passthru query is built on the fly, and the results are formatted for output via an Access report.
There are 16 different fields on the query form. Two are date fields (from / to), the rest are text. The user is required to supply a date range for the report. They may specify any combination of the remaining query fields.
Examples: From/To dates, part number, work order number From/To dates, location, operation, sales order number, operator
What is the best scheme to use for the indexes? One index for each potential query field? Or, a single index that includes ALL query fields? Or, some other combination?
I should also mention that there will be no realtime updates to the table. On a weekly basis, we will import updates from a mainframe extract (dropping the indexes prior to the import, rebuilding them afterwards).
I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
Can anyone help me in finding out the database name? For example, @@SERVERNAME gives you the name of the server on which you SQL Server is running. In the same way, is there any global variable which gives me the name of the database in the context in which I am running my queries or stored procs?
I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help...
Hello . Sometimes when i try to select data from a table (from a selected database) using SQL Query Analyzer (for exemple) i receive an error message : "The Object XX does not exist" . But when i prefix it by the Owner it work .
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length. Any ideas people?
I wanted to schedule a DTS package to run on the last day of the month at 11:30 PM. I noticed that when you use EM to do this I scheduled it to run monthly on the 31st. Hate to ask a dumb question, it will run tomorrow on 10/31, what happens in November on the 30th, is SQL smart enough to do it on that date?
This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity cityID (p/k) stateID (f/k to tblState.stateID) cityName (varchar 35)
tblStudent studID (p/k) cityID (f/k to tblCity.cityID) hometownID (f/k to tblCity.cityID) studFName (varchar 25) studLName (varchar 35) ...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be Quote: 12150, 127, 1050, 'John', 'Smith', ... How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID, s.studLName + ', ' + s.studFName AS studNameName, c.cityName, c.cityName AS swapconNum FROM tblStudent AS s INNER JOIN tblCity AS c ON (s.cityID = c.cityID) WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
