Most Recent Records

Mar 11, 2008

Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.


Select Distinct
--PCSNumber,
PCSINumber1,
max(PCSINumber2) as mostrecentPCS,
LastName,
FirstName,
State,
--InsuranceCarrier,
CarrierName,
MAX(CoverageEffectiveDate)as MostRecentEff,
MAX(TerminationDate) as MostRecentTerm,
AmtPerClaim,
Aggreg

FROM VW_Medical_Malpractice_Project

Where
State = 'PA'


Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg

Order By PCSINumber1


Example


000043010003MUSSER WILLIAM American Professional Agency2002-06-01

000043010002MUSSERWILLIAM Columbia Casualty2000-02-20

000043010001MUSSERWILLIAMMCIC Vermont Inc.1998-07-01

000043010004MUSSERWILLIAMProMutual Insurance Co.2005-05-16

000043010005MUSSERWILLIAMTri-Century Insurance Company2007-01-01


So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.

View 5 Replies


ADVERTISEMENT

Get The Recent Records

Feb 6, 2007

i have a datetime field in the post tables.
I would like to get the records within the latest 7 days.
Are there any functions for doing something like this?
my current query is something like
select * from post where creation_time ....
 Thank you

View 5 Replies View Related

Get Records Where Most Recent X = Some Value

Mar 31, 2008

If I have tables like Customer (Id, First, Last, Address, etc.), CustomerEvent (EventCode, EventDate, CustomerId), EventTypes(Code, Description) how would I get all of the CustomerIds where the latest CustomerEvent record is a cancel and it happened within the last 60 days?


If I just do a select max(EventDate) and put EventTypes.Code='cancel' in the where clause it picks up records where the customer has done other things since the cancel.

View 6 Replies View Related

Removing Old Records While Retaining Recent Ones

Apr 12, 2006

Hey guys, I have a table full of data that has duplicate records except for two date columns (date1 and date2). What I would like to do is remove the duplicates while retaining the most recent record, how can I do this?

So record 1 looks like this:


Code:

John | Smith | 08/08/2000 | 10/10/2000



Record 2 looks like this:


Code:

John | Smith | 08/10/2005 | 10/10/2005



I'd like to remove the first instance and keep the second (most recent one).

Ideas?

Thanks!

View 5 Replies View Related

Displaying Records With Recent Date

Jul 12, 2006

I want to display some horse racing results I have on a page but I only want to show the results for the last 7 days.

I have fields for date, time of race, horses name and result.

How can I just get records within the last 7 days to display?

I'm new to this SQL malarky so please be gentle!

View 4 Replies View Related

Selecting Only Records With Most Recent Date

Dec 15, 2007

Using MS SQL 2005, how can I SELECT only the records whos date field is equal to the most recent date held in that field?

For example:

date item colour
---- ---- ------
12/15/2007 shirt red
12/13/2007 shirt black
12/15/2007 shirt blue

result(s) expected:

12/15/2007 shirt red
12/15/2007 shirt blue

View 11 Replies View Related

T-SQL (SS2K8) :: Finding 5 Most Recent Records For Each Customer With Abnormal Order Amounts

Nov 5, 2014

The database consists of the following tables:

create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)

Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.

Possible result: [URL] ......

View 7 Replies View Related

Most Recent Date With Most Recent Table ID

Jul 15, 2014

I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic

SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records
FROM table1
GROUP BY tCustID

View 2 Replies View Related

Power Pivot :: Difference In Cost Between Most Recent Date And Second Most Recent Date

Apr 15, 2015

Have a table that list item#, date the standard cost went into effect and the standard cost.  How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.

Item#      EffectiveDate    StdCost

1152        01/01/2009      5.50
1152        09/29/2014      5.81
1152        04/04/2011      5.56

[code]....

View 2 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Getting Most Recent Record

Sep 4, 2007

I am trying to think of the easiest solution to do this.

What it is, is that I am trying to be alerted, if an account has not had its daily reports ran for x days (for example, 3 days).

I have two tables.

One is a table, with a list of AccountIDs that are to have the report done.

Another is a Log Table, which a record is inserted whenever a report has run. So, if a report has not run, it will not have a record in that table.

Here is the table setup with sample data. I am only going into basics, as the tables are used for a couple other things which are not needed for this.


Code:


Table: Reports_AccountID
ReportID | AccountID
--------------------------
5 | 1234
5 | 987
4 | 1234
3 | 1234
5 | 5677

Log: Reports_Log
LogID | ReportID | AccountID | ReportRunDate
----------------------------------------
9876 | 5 | 1234 | 9/4/2007 10:35:43 AM
9875 | 5 | 987 | 9/4/2007 10:35:43 AM
9874 | 4 | 1234 | 9/4/2007 9:05:43 AM
9873 | 3 | 1234 | 9/4/2007 9:30:17 AM
9872 | 5 | 1234 | 9/3/2007 10:35:43 AM
9871 | 5 | 987 | 9/3/2007 10:35:43 AM
9870 | 4 | 1234 | 9/3/2007 9:05:43 AM
9869 | 3 | 1234 | 9/3/2007 9:30:17 AM
9868 | 5 | 1234 | 9/2/2007 10:35:43 AM
9867 | 5 | 987 | 9/2/2007 10:35:43 AM
9866 | 5 | 5677 | 9/2/2007 10:35:43 AM
9865 | 4 | 1234 | 9/2/2007 9:05:43 AM
9864 | 3 | 1234 | 9/2/2007 9:30:17 AM



so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)

I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?

or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)

just trying to think of the best way to do this. any tips/advice would be appreciated

View 6 Replies View Related

Recent Dates Only

Dec 26, 2007

I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please


ALTER PROCEDURE [dbo].[SrchIssueDateLstNm_RecntIssueDte]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_LastName nvarchar(10) = null

AS
SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date],
[dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date]
FROM[dbo].[30 Day exclusion] INNER JOIN
[dbo].[30 Day exclusion].[id] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]

WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate)
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_LastName is null or [Last Name] = @Enter_LastName)
ORDER BY [Last Name]

View 1 Replies View Related

Getting Most Recent Order

Jan 15, 2007

I have a Customers table and an Orders table. The Customers table has fields... CustomerId, and CustomerName. The Orders table has Fields CustomerId, OrderDate, and ProductId.

I wish to execute a query that will return a single Customer and Order record for a given customer ID that reflects the most recent order. So, a typical join like...


SELECT c.customerId, c.customerName, o.orderdate,o.productId

FROM Customers C LEFT OUTER JOIN Orders o ON (o.customerId=c.customerId)

WHERE c.customerId=SOMEVALUE

would return all the orders for a given customer but I only want the most recent order. Can anyone help?

Thanks.

View 9 Replies View Related

Most Recent Record

Nov 13, 2007

Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.

here is the code


SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total
FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend,
filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number()
OVER (Partition BY regardingobjectid
ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND
filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND
filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d,
- CAST(@NeglectedDays AS INT), GetUTCDate())) AS d
WHERE recid = 1
GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname
ORDER BY d .actualend



Compnetsyslc

View 4 Replies View Related

Need Most Recent Customer

Mar 12, 2008

What statements can be used to grab the most recent customer record?

View 3 Replies View Related

Most Recent Output

Jul 20, 2005

hello,I have a table which contains the audit time field. This audit time fieldrecords the current date and time when a record is inserted.I would like to output the most recent top 500 rows.thank you.

View 3 Replies View Related

How To Get The Most Recent File?

Mar 19, 2007

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

View 8 Replies View Related

Most Recent Status Date

Apr 17, 2008

Sorry if I am posting in the wrong place here.  I am not a programmer by profession but just muddle through building some reports for my job.  The report I am working on at the moment is the most complicated one I have worked on so far.  I am working with MS Query (SQL) and trying to limit the rows returned with the most recent date.  I have searched online for the answer to this but just cannot seem to get it to work. 
There can be more than one Production Name per Manuscript Number.  Each Production Name has a Production Task Assignment Start Date.  I want to display one row per record with the latest (most recent) Production Task Assignment Start Date.  I have been trying to use the MAX functionality (which I have seen listed online) but just cannot get it work.  Can anyone help?  It would be much appreciated!
 The code looks like this:
SELECT SCHEDULE_GROUPS.`Schedule Group Description` AS 'AE', ROLEAUTH_DOC_PEOPLE_ADDR.`Article Type`, ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number` AS 'MS #', DOCUMENT.`Production Notes` AS 'Short title', ROLEAUTH_DOC_PEOPLE_ADDR.`First Author Last Name` AS 'First Author', PRODUCTION_TASKS.`Production Task Name` AS 'Name', "Production Task Assignment Start Date"-"ROLEAUTH_DOC_PEOPLE_ADDR.Final Decision Date" AS 'Days Since', ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Last Name of the Editor who made First Decision`, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`, ROLEPROD.`Production Task Assignment Start Date`
FROM DOCUMENT.tab DOCUMENT, PRODUCTION_TASKS.tab PRODUCTION_TASKS, ROLEAUTH_DOC_PEOPLE_ADDR.tab ROLEAUTH_DOC_PEOPLE_ADDR, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.tab ROLEEDIT_DOC_PEOPLE_ADDR_AUTH, ROLEPROD.tab ROLEPROD, SCHEDULE_GROUPS.tab SCHEDULE_GROUPS, SCHEDULE_GROUPS_TOC.tab SCHEDULE_GROUPS_TOC
WHERE PRODUCTION_TASKS.`Unique Idenitifer` = ROLEPROD.`Production Task ID` AND ROLEPROD.`Document ID` = ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = SCHEDULE_GROUPS_TOC.`Document ID` AND SCHEDULE_GROUPS_TOC.`Schedule Group ID` = SCHEDULE_GROUPS.`Unique Identifier` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = DOCUMENT.`Unique Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Unique Document ID` AND ((SCHEDULE_GROUPS.`Schedule Group Description` Not Like '%2008' And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Accepted (With SC for assigning)') And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Comments')) AND (ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`='Accept'))ORDER BY ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number`
 Once the records are retrived I am returning the data to Excel. 
Thanks for your time.
Natalie
 

View 6 Replies View Related

Select Most Recent Record

Dec 21, 2006

Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.

Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00

All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.

I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01

We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o

This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))

View 1 Replies View Related

Select Only Most Recent Date?

Mar 8, 2012

I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)

I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)

Code:
SELECT CPA.ScanName,
CPA.pspplMSSeverity,
CPA.smachIPAddress,

[Code]....

View 3 Replies View Related

Select 4 Most Recent Results

Mar 29, 2013

I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:

SiteDate Data
A01/01/2013 ...
B02/01/2013 ...
C03/01/2013 ...
A04/01/2013 ...
B05/01/2013 ...
C06/01/2013 ...
And so on...

In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.

View 3 Replies View Related

Select Most Recent Record??

Apr 27, 2004

Hi,

I want to construct a query that would give the most recent record. Table data looks like -

F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/6/2004 9:31:54

All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent.
If data is like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/7/2004 19:31:54

Select the 2nd record.

How do I construct the SQL query?


Let me know.

View 1 Replies View Related

Query Getting Most Recent Record

Jun 16, 2015

Here's the scenario. Consider the following sample data.

CREATE TABLE #MyTest
(
CustomerNumber INT,
Division CHAR,
SalesRepType INT,
SalesRepNumber INT,
EnterDate DATE

[code]....

Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.

CustomerNumberDivisionSalesRepTypeSalesRepNumberAssignedDate
10000A11002/1/2015
10000A23001/28/2015
10000B14002/1/2015
10000B26002/2/2015

I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and

SalesRepType 1.
WITH
cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)
AS
(
SELECT

[code]....

View 4 Replies View Related

Selecting The 2 Most Recent Dates

Mar 31, 2008

Hi guys,

I am having a problem in outputting the last two dates that a bill has been sent out.

Background:
A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.

The database that I am using stores all of the dates that a bill would have been sent out.

Issue:
A customer recently requested to see the dates of last 2 bills along with their value.

I know that you can view a customer last date by using the following statement.

Select max(issuedate)LastBill
from statements

but i don't know how i could output the last 2 bills that were issued

Any ideas....??

Velvet.

I know that in order to select the

View 4 Replies View Related

How To Get Recent Record In Duplicates

Feb 12, 2014

by executing this qry i will get below result ,in that TId is duplicates that is from that i want recent record with same result.i want last one if TId comes duplicate record. any one ans pl

select sa.GrandTotal, sa.TId, sa.Id,sa.Date, pr.PName,
pr.PCode from Sales sa
Left Outer Join Product pr on sa.Pid = pr.Id where sa.GrandTotal is not null

GrandTotalTIdIdDate PNamePCode
200 122014-01-30 18:46:55.000RK100
560 252014-01-30 18:47:49.000RK100
420 262014-01-30 19:55:11.000RK100

View 1 Replies View Related

Where...Can It Give You The Most Recent Entries?

Jun 12, 2007

SELECT post.C_ID, post.Comment, post.Title, post.Date, authors.U_Name
FROM authors INNER JOIN
post ON authors.A_ID = post.A_ID
ORDER BY C_ID DESC

That's the query I'm using, and I was hoping there is a way to use the WHERE clause to have only the 5 highest #'s from my primary key, which is A_ID IDENTITY(1,1).

What would be the best way for me to do this? I also have date as a datetime, but then I could be getting more or less than 5 results in my query.

Thanks

View 3 Replies View Related

Selecting Most Recent Date

Jan 8, 2008

I need to select the most recent price for an item from this table where certain criteria are met:

flexing_stock_transactions
--------------------------

item date price status week firm_or_commission
---- -------- ----- ----- ---- ------------------
CH 10/05/07 200 2 35 F
AL 10/05/07 195 1 35 C
CH 10/05/07 209 1 35 F
CH 11/05/07 210 1 35 C

I am currently using:

SELECT
price, date

FROM flexing_stock_transactions

WHERE (
[date] = (SELECT MAX([date]) FROM flexing_stock_transactions)
AND item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
)

zero rows are returned wheras I was expecting:

209, 10/05/07

I'm using MS Server 2005

View 13 Replies View Related

Selecting Most Recent Record

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

The Most Recent Date And Time

Jul 20, 2005

There are several day_timestamp for each index_id.Anyone can help me to write a sql to generate the most recentday_timestamp of index_ids which has not accessed into the system in90 days from today's date.So, I need to get the most recent date and time for each index_id in90 days from today's date.Sample data:Index_idday_timestamp2 2001-04-11 21-29-312 2002-05-21 21-29-312 2003-06-11 21-29-3122004-11-21 21-29-312 2004-09-21 21-29-315 2000-04-21 21-29-315 2003-05-21 21-29-315 2003-06-21 21-29-3152004-09-11 21-29-318 2000-08-11 21-29-3182004-04-01 21-29-3182004-09-21 21-29-3182004-09-23 21-29-31102001-04-11 21-29-31102002-04-21 21-29-31102003-08-11 21-29-31102004-10-21 21-29-31102004-09-21 21-29-31The output will be as below:2 2004-11-21 21-29-3152004-09-11 21-29-3182004-09-23 21-29-31102004-10-21 21-29-31

View 6 Replies View Related

Most Recent Data Query

Jun 28, 2007

I'm trying to retrieve the most recent data using the query below. The issue is there are multiple records which prevent distinct from working. If I could somehow get the most recent data (based on the eventdate field), that would work.

How would I go about this? I've tried order by and group by as well as max but that didn't seem to work.
Any ideas would be greatly appreciated!

SELECT DISTINCT
TOP 100 PERCENT SITES.Shape, SITES.OBJECTID, vw_sitemap.INTAKE_DATE, SITES.Expr6,
vw_sitemap.intake_num, vw_sitemap.ProjectName, vw_sitemap.ProjectType, vw_sitemap.ApplicantName,
vw_sitemap.PlannerInitials, vw_sitemap.file_num, vw_sitemap.status, vw_sitemap.EventDate
FROM SITES INNER JOIN
vw_sitemap ON SITEPTS.Expr6 = vw_sitemap.intake_num
WHERE (vw_sitemap.INTAKE_DATE > CONVERT(DATETIME, '2004-12-31 00:00:00', 102))

View 7 Replies View Related

How Do I Get The Most Recent Data From A Table?

Aug 27, 2007



I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the
volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.

Geoffrey Callaghan

View 5 Replies View Related

How Tot Select The Most Recent Date

Jan 9, 2007

I have 2 tables:
1) Item table (No_, Description)
2) Sales Price HAG table (Item No_, Sales Price HAG, Starting Date)

I want to have a report within from every item the most recent sales price. But, it is possible that a unique item has more than 1 sales price in the 'Sales Price table' (if it has more than one Starting Date). I want to report the sales price with the most recent date.

I've made this statement:
SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price]
FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
WHERE ([DatabaseName$Sales Price HAG].[Starting Date] IN
(SELECT MAX([Starting Date]) AS EXPR1
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]))



After running I still get from some items more than 1 price...... What went wrong?

Thx for help!

View 10 Replies View Related

Recent Docment List

Mar 18, 2008



Hi Everyone,

I was hoping someone could give me a clue as to how to do something like the following:

Select Distinct TOP 5 CaseId From Dna..CaseLog
Where Username = 'SomeUser' Order By LogDate Desc


This gives me an error:


ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


As you can probably see, I am trying to get a list of the last five cases that a particular user accessed, similar to a recent document list, in say Word.

The CaseLog table, however, keeps a record of all the updates to this case. So if I open a particular case twice, it will log that I opened it twice, with the same CaseId but with LogDate slighly different (ie by seconds).

Is there anyway I can get what I am looking for without having to have another log to keep track of what user has access what case?

Thanks very much for any help


Shai

View 4 Replies View Related







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