Selecting Only Earliest Record - Query Help
May 25, 2005
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. :)
View 1 Replies
ADVERTISEMENT
Jun 12, 2008
I'm having a mental block on a select statement.I have a table with the following columns:KitId int
LotNo varchar(10)
DateReceived smalldatetimeIt is possible to have many rows with the same LotNo but a differing DateReceived I need to write a select statement that returns the KitId for a given LotNo with the earliest DateReceivedso if I had rows:KitId =1, LotNo = 123, DateReceived = 11th May 2008KitId =2, LotNo = 123, DateReceived = 28th May 2008KitId =3, LotNo = 125, DateReceived = 28th May 2008KitId =4, LotNo = 127, DateReceived = 28th May 2008KitId =5, LotNo = 123, DateReceived = 12th June 2008I would want to retrieve KitId=1 if I provided LotNo 123 as a parameter Whilst it should always be the case that the LotNo with the earliest date will have the lowest KitId I cannot guarantee that will be the case so going for the lowest KitId isn't an optionCan one of you SQL gurus provide me with the statement I need?ThanksNeil
View 2 Replies
View Related
Mar 17, 2012
im trying to get the earliest record. data is below
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332/ 20090106<---- / 20081219/ 20090106
2332/ 20090323/ 20081219/ 20090323
2332/ 20090413/ 20081219/ 20090413
2332/ 20090507/ 20081219/ 20090507
because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332 20090106 20081219 20090106
View 5 Replies
View Related
Feb 9, 2007
Hello all,Quick sql syntax question:I have this table:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[REQUESTS]GOCREATE TABLE [dbo].[REQUESTS] ([ROW_ID] [uniqueidentifier] NULL ,[REQUEST_DATE] [datetime] NULL ,[STATUS] [tinyint] NULL) ON [PRIMARY]GOwith these values:insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 1)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)I need to select the single record with a STATUS = 0 with the earliestREQUEST_DATEI am using this query:SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BYREQUEST_DATEnot sure if this is the way to go...pointer appreciatedthanks
View 3 Replies
View Related
Nov 30, 2011
I have to find the earliest date for any existing calendar year in a table...
For instance.....
ClientID Year
1 1/1/2000
1 2/1/2000
2 3/1/2000
2 4/1/2001
2 5/1/2001
The results I need are....
ClientID Year
1 1/1/2000
2 3/1/2000
2 4/1/2001
It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM
(
SELECT ClientID, YEAR(MyDate) as Year1
FROM MyTable
) AS DT
GROUP BY ClientID, Year1
ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working...
View 1 Replies
View Related
Sep 4, 2007
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.
View 3 Replies
View Related
Jun 25, 2004
I'm trying to add a next and previous button to a popup window I have on a photo gallery -
Having trouble with the sql
I know I would have 2 select statements that would each return 1 result set.
I know how to get the top ( and bottom by ordering descending and doing Select Top 1)
But how would I get the next one up!
I would hate returning the entire image section and perform the calcuation in the business tier :(
View 4 Replies
View Related
Mar 1, 2012
I have one table like this:
Code:
CREATE TABLE DlIndexTable
(SessionStartTime DATETIME NOT NULL PRIMARY KEY, SchemaID INTEGER NOT NULL,
DLBaseRate REAL NOT NULL)
and one like so:
Code:
CREATE TABLE DlTextDataTable (
SessionStartTime DATETIME NOT NULL REFERENCES DlIndexTable,
ChTimestamp FLOAT NOT NULL, Channel01data VARCHAR (255),
Channel02data VARCHAR (255), ... , Channel16data VARCHAR (255),
CONSTRAINT TxtDatPriKey PRIMARY KEY (SessionStartTime, ChTimestamp))
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.
View 10 Replies
View Related
Mar 18, 2015
I’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.
The database contains a table, like so:
ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name
Initially, the user will create a “V0.1” document. So the data would look something like
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”
Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.
For example, a “2.1” document would be stored as:
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”
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.
View 4 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
Mar 26, 2008
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
View 15 Replies
View Related
Apr 24, 2001
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.
View 2 Replies
View Related
May 19, 2004
Hi all,
I Have the following situation:
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?
Thanx all
View 8 Replies
View Related
May 21, 2001
Hi
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:
1231999-06-14 12:17:11.000
1231999-06-14 12:17:31.310
1231999-06-14 12:17:31.000
1231999-06-14 12:22:56.000
1231999-06-14 12:22:58.000
8901999-06-15 10:00:18.000
8901999-06-15 10:03:30.340
8901999-06-15 10:03:30.000
8901999-06-15 10:03:40.000
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
Thanks in advance
Bazza
View 1 Replies
View Related
Oct 10, 2006
I have the following query I am using to identify duplicate records in one of my database tables:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
HAVING ( COUNT(memberID) > 1 )
Executing the above proc returns 4079 records...
Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...
I tried running the following procedure:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )
But zero results are returned ...
The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.
Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.
Thanks,
Jandrews
View 3 Replies
View Related
May 14, 2014
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.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
[code]....
View 6 Replies
View Related
Apr 20, 2007
Hi,
Is it possible to select a record by just the date part of the data type TIMESTAMP?
date_time
2007-04-20 16:27:01
For example, a query to select all records added today.
Thanks very much,
dai.hop
View 2 Replies
View Related
Oct 10, 2005
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 :)
View 4 Replies
View Related
Mar 23, 2006
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
View 4 Replies
View Related
Aug 27, 2014
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.
View 2 Replies
View Related
Jan 31, 2014
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:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
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.
View 1 Replies
View Related
Oct 29, 2006
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 ?
View 2 Replies
View Related
Jan 30, 2008
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
AND a.OrderID = b.OrderID
View 8 Replies
View Related
Feb 10, 2014
I have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
View 4 Replies
View Related
May 26, 2015
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
View 9 Replies
View Related
Jan 24, 2015
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
ID,phone_ number,first_ name,last_name,address1, address2, address3,city,state,postal_code,HOME_OWNR,HH_INCOME,CREDIT_RATING,AGE,MATCH,source_id,
title,comments,dnc_flag,provider,vehicle,coverage,alt_phone,email,marital status,dob
View 8 Replies
View Related
Apr 7, 2008
Hi,
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.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
May 7, 2008
Hi I am having trouble and I don't know why.
I have this query which I pasted below. I need to find the earlist effective date (pcsp_eff), but I need to show all of the fields below in my report like a flat file. I know ususaly when you use Max/Min you have to have a group by, would I group by everything that is in my select statement?
SELECT Distinct
dbo.pcs.pcs_id1,
dbo.pcs.pcs_lname AS [Last Name],
dbo.pcs.pcs_fname AS [First Name],
dbo.pcsp.pcsp_eff AS [Effective Date for Provider],
Min(pcsp_eff) as "earliestEffectivedate",
dbo.pcst.pcst_trm1 AS [Tracking Thru Date],
dbo.pcst.pcst_dat3 AS [Re-cred Letter Sent Date],
dbo.pcst.pcst_dat7 AS [Re-cred Complete Date],
PRO_STATE as "State",
PRO_COUNTY as "County"--, PCSP_NET
FROM dbo.pcs INNER JOIN
dbo.pcst ON dbo.pcs.pcs_id1 = dbo.pcst.pcst_id1 INNER JOIN
pcsp ON pcs.pcs_id1 = pcsp.pcsp_id1 left Join
dbo.pro ON pcs.pcs_id1 = pro.pro_id1
WHERE
(CONVERT(CHAR(10), dbo.pcst.pcst_dat3, 110) <>'03-23-1977') and
(CONVERT(CHAR(10), dbo.pcst.pcst_dat7, 110) = '03-23-1977') and
(pcsp.pcsp_prd = 'DGH') AND
--(pcsp.pcsp_id2 = '0001') and
PCS_CTL = 'I' and
pcsp_NET <> 'DACFP' and
pcs_id1 = '00004307'
Group by pcs_id1
View 2 Replies
View Related
Nov 26, 2013
I want to find out the earliest [First_Post_Date] for any parentdid
My query (See below)
Produces the following results
SELECT
ParentID
,[First_Post_Date]
,[FDMSAccountNo]
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878595212886'
Order by ParentID desc
[code]....
View 1 Replies
View Related
Jul 20, 2005
Hi,I have a table (SQL Server 2000) with several date columns in it, all ofwhich are individually NULLable, but in any one row, not all the dates canbe NULL.I want a query which ORDERs BY the earliest date it finds in each row. I'mguessing I have to do this in two steps:STEP 1Using a UDF, find the earliest date and stick it in a new calculatedcolumn "earliest date"STEP 2ORDER BY this UDF-created columnIf this is the right way to go about this, is there a simple SQL way ofdetermining which is the lowest of several dates? (ie of doing STEP 1).Or am I looking at this the wrong way, and missing an easy *one-step* way ofgetting what I want?TIA,JON
View 5 Replies
View Related
Mar 1, 2007
Hi,
How to get the latest date from ColumnDateTime?
and the earlier as well ?
View 5 Replies
View Related
Dec 10, 2014
I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null
I tried..
DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime
[Code] .....
View 4 Replies
View Related
