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
Say I have 3 .bak files named:jamesB.bak, jamesG.bak, jamesW.bakIs there a clever way to find out which is the most recent of thesebackup files?? Using sql query analyzer preferably...
View 4 Replies View RelatedHi,
Here's my situation. Every day I will be downloading extracts to a folder. The extracts are named:
20070529.Extract1.csv
20070528.Extract1.csv
20070527.Extract1.csv
20070529.Extract2.csv
20070528.Extract2.csv
20070527.Extract2.csv
So, on any given day, I will want to find the most recent versions of Extract1 and Extract2, for example:
20070529.Extract1.csv & 20070529.Extract2.csv
How would I go about doing this?
Thanks much
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
What might be the reason for this error:
A propos above error, traying to open [URL] ....
I am getting this error:
User 'domainuserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
How to proceed with this ?
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]....
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
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
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]
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.
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
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.
What statements can be used to grab the most recent customer record?
View 3 Replies View Relatedhello,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 RelatedIf 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.
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
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()))
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]....
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.
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.
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]....
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
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
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
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
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 RelatedThere 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 RelatedI'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))
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
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!
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
I've got a table like so
date | number___________|_____________
2007-01-01 | 432007-01-02 | 652007-01-03 | 232007-01-04 | 652007-01-05 | 232007-01-06 | 11
2007-01-07 | 52
2007-01-08 | 83
2007-01-09 | 44
and I want to get the most recent date in the past which I must go back where the sum of the numbers of the number column if greater than a certain number.
EG. From the above data, the date which I must go back to get a sum of 195 is 2007-01-05
as 44+83+52+11+23 is the least amount that is greater than 195.
Can anyone help, is this even possible? I'm flummoxed!
Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns 2550 rows of data.
In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns 2500. I need help retirenving the entire liste with the most recent OrbitimportId.
Help!
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetAttribute.AssetDescription, dbo.Asset.Barcode, dbo.Asset.SKU, dbo.ESNTracking.ReportTime, dbo.ESNTracking.CurrLocStreet, dbo.ESNTracking.CurrLocCity, dbo.ESNTracking.CurrLocState, dbo.ESNTracking.CurrLocZip, dbo.ESNTracking.CurrLocCounty, dbo.ESN.EsnNumber, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.MapUrl, dbo.InventoryOrigin.WarehouseDescriptionFROM dbo.AssetType INNER JOIN dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId LEFT OUTER JOIN dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportIdWHERE (dbo.ESNTracking.OrbitImportId = (SELECT MAX(OrbitImportId) AS Expr1 FROM dbo.ESNTracking AS ESNTracking_1))ORDER BY dbo.AssetType.Description