Query To Pull Lastest Date Record

Apr 16, 2008

How do I pull only the latest shipdate record from a multiple record key?

I tried the following, but it doesn't pull only the lastest date record:

Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate

Thanks in advance.

View 7 Replies


ADVERTISEMENT

Query To Select Lastest Record

May 30, 2012

If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.

LeaseIDOpID Year MonthMCFFileDate
208801407982006 107660200807
208801407982006 107680200801
208801407982006 107784200704

View 5 Replies View Related

Select Query To Pull The Last/latest Event By Date

Oct 30, 2007

I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx

I want the records of the persons latest WHENBAD date. It should return one recordset for each person.

Thanks in advanced.

View 4 Replies View Related

Transact SQL :: Pull All Records From One Table And Just A Single Record From Another

Aug 7, 2015

I'm trying to pull all records from one table and just a single record from another.  I have this join, (see below).  It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return.  The end result should be the blog record and a single corresponding image record.  But always a blog record.

SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],

[code]...

View 6 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

How To Pull From Two Date Ranges

Dec 17, 2006

Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
 I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount

View 6 Replies View Related

Transact SQL :: Pull A Value Closest To Another Date

Apr 21, 2015

I have two tables and I am trying to pull a value closest to another date.  There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?

TableA
PA_IDEval_Date
12015-01-08 00:00:00.000
22015-01-25 00:00:00.000

TableB
IDPA_IDDate Stat
112015-01-15 00:00:00.000 14
222015-01-28 00:00:00.000 8
312015-01-16 00:00:00.000 2
422015-01-029 00:00:00.000 3
512015-01-09 00:00:00.000 11
612015-03-14 00:00:00.000 14

Expected Results
PA_IDDate Stat
12015-01-09 00:00:00.000 11
22015-01-28 00:00:00.000 8

View 8 Replies View Related

How Can I Get Lastest Value

Mar 14, 2008



I have a table for Units and Dollars based on 2 date keys ; snapshot date_key and delivery date_key (which are date key from date dimension)

This table do not update daily and if I want to have the information based on the lastest snapshot date_key how should I write a query for this case?

What I did was:





Code Snippet

SELECT snapshot_date_key,delivery_date_key, units, dollars
FROM dbo.tbl_fact
WHERE snapshot_date_key IN(SELECT DATE_KEY FROM tbl_DIM_Date
WHERE (FULL_DATE = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))))





But this query is only available when snapshot date key exists in the table based on today.
If table is not updated today then I want to get the lastest snapshot_date_key from the fact table.
Max function with sanpshot_date_key does not work due to the delivery_date_key.

And kinds of advice would be greatly appreciated.
Thanks.

View 5 Replies View Related

Add Date To Record In SQL Server Each Time Record Is Added

Mar 1, 2006

Hi
 
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

View 6 Replies View Related

Date Formatting - Pull Information From Data String

Jul 9, 2013

'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'

Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:

cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)

...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -

So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?

View 3 Replies View Related

Script To Pull Records ONLY If Date Repeats More Than One Within Same SEQ Number

Jan 30, 2014

I have the following table:

SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
124 FEB-28-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013

I need a script to pull the above records ONLY if the date repeats more than one within the same SEQ number. The proper script would output as follows:

SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013

View 2 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

How To Get Lastest Two Records For Each Cutstomer_number

Jun 5, 2006

Hi ,

My Requirment goes like this. In a table say X , i have two column called Customer_number and date_received. What i need to find is, lastest two enteries for the customer number. That is top 2 records for each customer_number.

the query out put is some thing like this,

Cust Number Date_received

1234 06/06/2005 10AM

1234 06/06/2005 08AM

1235 06/06/2005 10AM

1235 06/05/2005 10AM

1236 06/05/2005 11PM

1236 06/04/2005 06AM



How can i achieve the above functionality.

I hope i was clear in my explanation.



Regards,

Chetan

View 6 Replies View Related

Return The Lastest [Identity] Field Value

Feb 17, 2004

Hi!

I have a question that might sound obvious to some of you but (obviously) not to me.

I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).

I want to return the value of the TableID of the newly created record. Sound simple ?

Thanks.

View 4 Replies View Related

RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'

Apr 25, 2007

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:






Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}





Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:




Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456



The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.



So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?



Please help.



Thank you.

Fipil.



View 10 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

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

How To Create A Query To Pull That Specific Row And All 50 Columns

Feb 7, 2012

I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.

View 3 Replies View Related

T-SQL (SS2K8) :: Select Query To Pull Required Data From 3 Tables

Mar 19, 2014

I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.

Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .

Example:

Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,

[Code] ....

Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .

My Final Output of select query should be like below.

EmpID Role
1 Role1
2 Role2
3 Role1

View 5 Replies View Related

Query To Pull Data From Multiple Columns (Three Email Fields)

Mar 12, 2015

I need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.

View 6 Replies View Related

Reporting Services :: Query To Pull SSRS Parameter Properties (Hidden / Visible / Internal)

May 19, 2015

We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?

I checked executionlog2 but can not find the parameter property.

View 7 Replies View Related

Get End Date From Next Record

Mar 6, 2008

I have the following table and have included some sample data. Basically, the records are grouped by opid and it shows ownership changes for the opids. It only shows the owner change date which is the date that the owner was assigned to the opid. What I want to figure out is the date that the owner ceased to be assigned to the opid as well. If it is still the current owner then the end date should just be the current date.




Code Snippet
CREATE TABLE #op_owner_history(
[opshid] [int] IDENTITY(1,1) NOT NULL,
[opid] [int] NOT NULL,
[role] [varchar](30) NOT NULL,
[rep] [char](20) NOT NULL,
[percentage] [int] NOT NULL,
[ownerchg_date] [datetime] NOT NULL,
[ownerchg_rep] [char](20) NOT NULL,
CONSTRAINT [PK_#op_oppshare_history] PRIMARY KEY CLUSTERED
(
[opshid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #op_owner_history ON
GO
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4653, 23795, 'Sales','KOHLENBERG', 100, '9/26/2006 3:57:22 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8576, 23795, 'Sales', 'Haber', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (9363, 23795, 'Sales', 'KOHLENBERG', 100, '1/4/2007 12:40:14 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (2919, 24623, 'Sales', 'COLEMAN', 100, '9/1/2006 12:02:03 PM', 'Dunkel')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (15475, 24623, 'Sales', 'COLEMAN', 100, '5/8/2007 8:10:51 AM', 'Woodruff')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8580, 24623, 'Sales', 'Woodruff', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4310, 5548, 'Sales', 'KOHLENBERG', 100, '9/19/2006 1:53:47 PM', 'KOHLENBERG')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (10649, 5548, 'Sales', 'HABER', 100, '2/7/2007 3:50:40 PM', 'Kohlenberg')




Based on this sample data, here is what I want to see in the results: (My apologies, I couldn't get it formated very well to make it more readable.)

opshid opid role rep percentage ownerchg_date End Date ownerchg_rep
4310 5548 Sales KOHLENBERG 100 9/19/2006 1:53:47 PM 2/7/2007 3:50:40 PM KOHLENBERG
10649 5548 Sales HABER 100 2/7/2007 3:50:40 PM GETDATE() Kohlenberg
4653 23795 Sales KOHLENBERG 100 9/26/2006 3:57:22 PM 1/3/2007 10:40:00 AM Kohlenberg
8576 23795 Sales Haber 100 1/3/2007 10:40:00 AM 1/4/2007 12:40:14 PM ADMIN
9363 23795 Sales KOHLENBERG 100 1/4/2007 12:40:14 PM GETDATE() Kohlenberg
2919 24623 Sales COLEMAN 100 9/1/2006 12:02:03 PM 1/3/2007 10:40:00 AM Dunkel
8580 24623 Sales Woodruff 100 1/3/2007 10:40:00 AM 5/8/2007 8:10:51 AM ADMIN
15475 24623 Sales COLEMAN 100 5/8/2007 8:10:51 AM GETDATE() Woodruff

View 4 Replies View Related

Get Record With Latest Date

Jun 4, 2007

 if there are 2 records with different date  how to write query for ----> get record with latest date 

View 4 Replies View Related

Select Date Record

Feb 18, 2006

    how to select record from the table where the data between a  range. example  between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks

View 2 Replies View Related

Using Date From Prior Record

Apr 8, 2004

Hello gurus, I have a table of data containing stop and start times of equipment, such as this:

StartTime....................EndTime
12/01/01 15:44...........12/01/01 18:44
12/02/01 3:44............12/02/01 14:44
12/02/01 15:22...........12/02/01 15:33

etc.

With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?

View 14 Replies View Related

How To Get Latest Date Record

Apr 10, 2014

I want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved

table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 1 Replies View Related

Record Update Date

Nov 2, 2005

How to update the Last Update Date of a modified record? I want to put this in table trigger .

Or any setting can be used?

Please help~~~~

View 20 Replies View Related

Only Need One Record Per Newest Date.

Feb 7, 2008

Hey all,

I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.

Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.

I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?

Here is my query:

SELECT
rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool

FROM
dbo.Result_View AS r WITH (NOLOCK) INNER JOIN
dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN
dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN
dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id

WHERE
(rpv.Name NOT LIKE 'Archive')
AND
(r.CreateTime > '2/1/2008')
AND
(r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))

ORDER BY MachineName


I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.

Thanks in advance,

Allen

View 3 Replies View Related

Transact SQL :: Get One Record By ID Which Has Max Date

May 4, 2015

I have records like this

id      address1          date
1      xyz                 01/01/2013
1       abd                01/01/2014
2       dfg                 01/03/2015

Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.

So here result should be

1       abd                01/01/2014
2       dfg                 01/03/2015

View 12 Replies View Related

Search Record Base On Date

Oct 27, 2000

Hi,

The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.

Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String

myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy")

Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"

Set rstDate = New ADODB.Recordset

With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With

If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date") = Format(myFileDate, "dd-mm-yyyy")
rstDate.Update
End If
rstDate.Close
End Sub

Regards,
May

View 1 Replies View Related

Automatically Change Record By Date

Jul 1, 2005

How can I automatically change records by a specific date specified inside the record. An example would be the way ebay sales work. How does ebay have the status of an item change to closed, at the time inside the item record of the database.

I know i could use triggers or something to check the current date against the enddate everytime the record is accessed, but is there a more efficient method?

View 2 Replies View Related

Get Inbeetween Record In Date Range

Apr 7, 2006

I HAVE THIS SQL STATEMENT WHICH GET THE MIN(AMOUNT) and MAX(AMOUNT) and DISPLAY IT IN A SINGLE ROW.
HOW CAN I GET THE RECORD BEFORE THE MAX (AMOUNT) INBETWEEN DATES IN CASE THERE ARE 4 RECORDS WITH SAME EMPLOYEENO IN THE DATE RANGE SPECIFICIED ? THIS IS SORTED BY EMPLOYEENO AND STARTDATE DESC


SELECT EmployeeNo, StartDate AS StartDate, MIN(Amount) AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2001-06-01' AND startdate <= '2007-06-02') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND startdate >= '2001-06-01' AND startdate <= '2007-06-02'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate DESC

THANK YOU SO MUCH ...

View 2 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Unable To Get Latest Date Record?

Apr 10, 2014

i want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------

idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved