This is my first post so I hope I'm doing it to the right forum. I need to compare a LastUpdated time to Start and End Time fields to get the "Due" Time for a given order. Can someone give me the correct SQL? (the example below should result in 6:00:00 AM "Due" time.) All are DATETIME fields. Thank you.
LastUpdated StartTime EndTime DueTime
5/29/2007 12:04:32 AM 2:00:00 AM 11:30:00 AM 3:30:00 PM
11:30:00 AM 5:00:00 PM 9:00:00 PM 5:00:00 PM 2:00:00 AM 6:00:00 AM
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND from fcs.dbo.WorkSet as ws left outer join fcs.dbo.WorkAssignment as wa on ws.WorkSetID = wa.WorkSetID left outer join fcs.dbo.MeterSessionInput as msi on wa.worksetkey = msi.worksetkey
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.
I want to get some combined data from both tables, so right now I am joining them at the SessionStartTime column, which is a primary key in the first and a foreign key in the second table, something like this:
Code: SELECT DlIndexTable.SessionStartTime, DlTextDataTable.Channel01data FROM DlIndexTable LEFT JOIN DlTextDataTable ON DlIndexTable.SessionStartTime = DlTextDataTable.SessionStartTime WHERE DlIndexTable.SessionStartTime BETWEEN '2006-10-13 16:40:08.790' AND '2012-03-01 17:54:30.930' ORDER BY DlIndexTable.SessionStartTime, DlTextDataTable.ChTimestamp
The trouble is that this query, exactly as requested, gives me all the entries from the second table matching the first, while I really would like to pick just one row (preferably, the first chronologically - by ChTimestamp) so that the first column (SessionStartTime) has distinct entries in the resulting table. What would be the simplest way of doing that? Performance is not a big priority over simplicity since the first table could have only a few hundred rows (maybe a couple of thousand), while the second will be real tiny.
The earlier versions will still exist on the database, but the latest version will be 2.1.
There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.
I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.
So, if the database contained the following records:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,0,1,My Document 1,1,1,0,2,My Document 1,1,1,0,3,My Document 1,1,1,1,0,My Document 1,1,1,2,0,My Document 1,1,1,2,1,My Document 1,1,2,0,1,My Second Document 1,1,2,0,2,My Second Document 1,1,2,0,3,My Second Document
My query should return:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,2,1,My Document 1,1,2,0,3,My Document
… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.
MSSQL2000I have a table that contains customer transactionsCustomerIDTransactionTransactionDate....I need to select the most recent record that matches a specific CustomerID.I am fairly new to SQL, could someone provide a sample select statement.TIATim Morrison-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
I have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO
Hello. I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number. So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc... If any one have any ideas please respond. Thanks in advance.
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL, titre VARCHAR(50) NOT NULL, annee INTEGER NOT NULL, idMES INTEGER, genre VARCHAR(20) NOT NULL, resume TEXT, codePays VARCHAR(4), CONSTRAINT PKFilm PRIMARY KEY (idFilm), FOREIGN KEY (idMES) REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable; How can I do it in MS SQL or Transact-sql?
I'm no SQL whizz yet but I'm learning hard, and need to get some information from our DB rather urgently so have resorted to this fantastic forum, only I can't find what I'm looking for.
Basically I'm selecting a whole load of entries that have a (admission)date field after 2001, but I only want to return the Earliest (admission) for each (patients number).
Here is the script I have created to select all the data, but how can I limit the results to just the earliest (admission date) for each (patient).
SELECT Admission_Year, Admission_Month, Age_On_Admission, [Length of stay(continuing)], [Patient's Number], [Cons epis seq no], Sex, [Main Primary Pas Diag], [Date of Death], [Epi duration], [OP Code1], [Admission date], [Date of Death] - [Admission date] AS [days before death],[Intended Management] FROM dbo.Admissions WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
I would really appreciate it if anyone can help with this, I'm sorry I can't really contribute to this forum as an SQL expert as .net is really my forte and I usually spend my time contributing to the asp.net forums. :)
I'm sure this is an easy problem but my brain is fried today...however how do I do the following:
I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:
OK, how do I get the top 1 of each key so that I get a subset of records looking like the following: 1231999-06-14 12:17:11.000 8901999-06-15 10:00:18.000
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)
Good Morning,I have a person table with personID. I have a personRate table withpersonID, rateID, and effectiveDate.I need to select fields from personRate, but I want the fields from theproper record.I need the one child record that has the most current date of the largestrateID.For example a person may have many rate records. I need the record that hasthe most current date of the largest rateID they have. Does that makesense?I am making a view that has data from both tables. I need to display themost current rate info.Any ideas? TIA ~ CK
I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table if object_id('tempdb..#tmpdelnotes') is not null drop table #tmpdelnotes
create table #tmpdelnotes( DelNote int identity (1,1) , DelDate date not null, Item int not null, Customer int not null)
[code]...
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours], wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
Hi,I have a stored procedure that has to extract the child records forparticular parent records.The issue is that in some cases I do not want to extract all the childrecords only a certain number of them.Firstly I identify all the parent records that have the requird numberof child records and insert them into the result table.insert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select t1.BatchNumber,t1.EntryRecordID,t1.LN,t1.AdditionalQualCritPassedfrom(select BatchNumber,RecordType,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Temp) as t1inner join(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) = max(TotalNumbers)) as t2on t1.BatchNumber = t2.BatchNumberand t1.RecordType = t2.RecordTypeand t1.EntryRecordID = t2.EntryRecordID)then insert the remaining records into a temp table where the number ofrecords required does not equal the total number of child records, andthenloop through each record manipulating the ROWNUMBER to only selectthe number of child records needed.insert into @t_QualificationMismatchedAllocs([BatchNumber],[RecordType],[EntryRecordID],[AssignedNumbers],[TotalNumbers])(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) <max(TotalNumbers))SELECT @QualificationMismatched_RowCnt = 1SELECT @MaxQualificationMismatched = (select count(*) from@t_QualificationMismatchedAllocs)while @QualificationMismatched_RowCnt <= @MaxQualificationMismatchedbegin--## Get Prize Draw to extract numbers forselect @RecordType = RecordType,@EntryRecordID = EntryRecordID,@AssignedNumbers = AssignedNumbers,@TotalNumbers = TotalNumbersfrom @t_QualificationMismatchedAllocswhere QualMismatchedAllocsRowNum = @QualificationMismatched_RowCntSET ROWCOUNT @TotalNumbersinsert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select BatchNumber,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Tempwhere RecordType = @RecordTypeand EntryRecordID = @EntryRecordID)SET @QualificationMismatched_RowCnt =QualificationMismatched_RowCnt + 1SET ROWCOUNT 0endIs there a better methodology for doing this .....Is the use of a table variable here incorrect ?Should I be using a temporary table or indexed table if there are alarge number of parent records where the child records required doesnot match the total number of child records ?
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields: OrderID (FK, not unique) InstallationDate (Datetime) CreateDtTm (Datetime)
There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.
For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:
SELECT a.OrderID, InstallationDate
FROM ScheduleDateLog a
INNER JOIN
(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord
FROM ScheduleDateLog GROUP BY OrderID) as b
ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.
Example:
Phone Number Name Home Owner Credit Insurance 727-555-1234 Dave Thomas Yes B 727-555-1234 Dave Thomas Gieco
I would like the end result to be one record:
Phone Number Name Home Owner Credit Insurance 727-555-1234 Dave Thomas Yes B Gieco
Since these were imported into SQL they all have a unique ID, here are the current labels
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 have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
