I Need To Return All The Recent ImportId Data
Jun 16, 2006
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.WarehouseDescription
FROM 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.OrbitImportId
WHERE (dbo.ESNTracking.OrbitImportId =
(SELECT MAX(OrbitImportId) AS Expr1
FROM dbo.ESNTracking AS ESNTracking_1))
ORDER BY dbo.AssetType.Description
View 1 Replies
ADVERTISEMENT
Aug 6, 2013
I'm running a query on a set of electricity meters for various sites and wish to determine the most recent metre read for each site, by selected site group.
There are 4 tables :
CONTACTS - Containing all the Site details
LOOKUP - Containing the Site Group details
POINTS - Containing all the meter id details
DATAELECTRICITY - Contains all the meter readings
The system I am using is a bespoke system which I believe is running transact sql. The query below returns all of the meter readings for each site,but I would like :
a) Only return the latest M1-M8 readings for the maximum DataElectricity.Date
b) Only return values for the selected Site Group
PARAMETERS [Site Group] Text ( 255 );
SELECT
Lookup.Lookup_Name AS [Group],
Contacts.Name AS Site,
Points.Number AS MPAN,
[code]....
View 9 Replies
View Related
Jun 19, 2006
This is more ASP SElect .
I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.
Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM 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
ORDER BY dbo.AssetType.Description
View 2 Replies
View Related
Sep 3, 2007
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]
View 11 Replies
View Related
Sep 29, 2015
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
View 5 Replies
View Related
Jul 23, 2005
Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
View 4 Replies
View Related
Oct 7, 2015
How do you Write an expression to Return the date value based on the most recent date only?
Eg. Date 10-7-2015, action - done, notes - all items fixed.
Date 4-5-2016, action - not yet done, notes - buying parts
All these dates are returned based on a search parameter based on a project number. I only want the most current date and associated fields displayed.
View 4 Replies
View Related
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
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
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
Apr 25, 2008
Hello, i have this problem
Table1
tbl1
pcb varchar(30)serial varchar(30)result varchar(30)
tbl2:
pcb varchar(30)date_time varchar(30)result varchar(30) data1 varchar(30)data2 varchar(30),
what i need is query the tbl1 for a range of serials,get the pcb and for those pcb's query the tbl2 for data1,data2
The resultSet should be a join on the two tables, Columns {serial} from tbl1 and {pcb,date_time,data1,data2} from tbl2
Please follow my simple example:
Suppose tbl1 has these 2 records
tbl1 = pcb1,sn1,pass
pcb2,sn2,pass
pcb3,sn3,pass
tbl2= pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6
where date1 is the most recent date and date6 the least recent
Request:what i want is for serial>=sn1 and serial<=sn2,get the pcbs from tbl1(which are pcb1 and pcb2) and based on these, query the tbl2
for the other data but retrieve only most recent records.
The correct ResultSet should be
pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3
and not
pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb1,date5,pass,dataX5,dataY5
pcb2,date3,pass,dataX3,dataY3
pcb2,date6,pass,dataX6,dataY6
What i already did is this:
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
from tbl2
left JOIN tbl1
ON tbl2.Pcb=tbl1.pcb
where tbl1.serial>='1' and tbl1.serial<='53'
and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
group by tbl2.pcb,tbl1.serial;
This works correctly for getting serial from tbl1, date_time and pcb from tbl2.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records (by using this OR philosophy).I mean, it would give all records containing (pcb1,pcb2),much like my example
How can i resolve this issue?
Thank you very much
View 1 Replies
View Related
Apr 25, 2008
Hello, i have this problem
tbl1 :{ pcb varchar(30) ,serial varchar(30)},result varchar(30)
tbl2: { pcb varchar(30) ,date_time varchar(30),result varchar(30),data1 varchar(30),data2 varchar(30),}
what i need is for a range of serials get the pcb (from tbl1) and for those pcb's get as resultSet the table
tbl3:{pcb,serial,data1,data2} containing most recent data on date_time column.
example. tbl1 ={ pcb1,sn1,pass}
pcb2,sn2,pass
pcb3,sn3,pass}
tbl2={pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6}
where date1 is the most recent date
Request:what i want is for serial>=s1 and serial<=s2 get
pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3
What i already did is this:
Code Snippet
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
from tbl2
left JOIN tbl1
ON tbl2.Pcb=tbl1.pcb
where tbl1.serial>='1' and tbl1.serial<='53'
and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
group by tbl2.pcb,tbl1.serial
And this works correctly.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records.I mean it would return from tbl2, all records containing pcb1,pcb2.
How can i resolve this issue?
Thank you very much,
ElectricC
View 1 Replies
View Related
Jul 1, 2015
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13
View 5 Replies
View Related
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
Jan 17, 2008
Hi Everyone i wonder if there anyone know
what is "Render report with most recent data" and execution SnapShot and the different between them
and also it will be great of someone provide me with blog or a link that describe these 2 properties
Thanx
Maylo
View 1 Replies
View Related
Jul 24, 2006
hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
View 1 Replies
View Related
May 2, 2007
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
View 2 Replies
View Related
Jan 5, 2004
Hi,
A partner company unfortunately chose Sybase SQL Anywhere v5 as their database and I have to return data from the tables for a web service that I am writing - YUCK !
The ODBC driver is riddled with errors and has no support. Third party .NET providers do not seem to support v5, so I have resorted to playing with DTS in Enterprise Manager.
After fumbling around for a while, I managed to get an "Execute SQL Task" to return data and saved the package in both SQL and VB.
Now, before I waste any more time heading in this direction, can anybody advise if DTS is supported by MSDE as the full blown SQL Server will not be an option ?
If it is supported - is this a good way to go, or is there a better solution ?
Thanks in advance.
Steve.
View 2 Replies
View Related
Feb 24, 2005
I am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.
Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50)
Parem.Direction = ParameterDirection.Output
View 3 Replies
View Related
Mar 12, 2007
Hi,
I have the following SSAS MDX query, currently taking a From & To date. How would I alter the MDX below so that just the prior 3 months data was returned for a chart of balances. I do not wish to retain the date parameters
SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS, NON EMPTY { ([Time].[Simple Date].[Simple Date].ALLMEMBERS * [Products].[Product ID].[Product ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) : STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Type].&[Term] } ) ON COLUMNS FROM ( SELECT ( { [Book Balance Type].[Balance Type].&[Credit Balance] } ) ON COLUMNS FROM [DailyBalances]))) WHERE ( [Book Balance Type].[Balance Type].&[Credit Balance], [Products].[Product Type].&[Term] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
View 7 Replies
View Related
Jan 19, 2008
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks
SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
View 7 Replies
View Related
Mar 19, 2006
How I can check at code level if the select command in the SqlDatasource didn't return any rows?
Thank you all..
View 1 Replies
View Related
Jun 16, 2006
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.WarehouseDescription
FROM 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.OrbitImportId
WHERE (dbo.ESNTracking.OrbitImportId =
(SELECT MAX(OrbitImportId) AS Expr1
FROM dbo.ESNTracking AS ESNTracking_1))
ORDER BY dbo.AssetType.Description
View 1 Replies
View Related
Nov 15, 2012
I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID
tblEmployee
EmployeeID FirstName LastName
1 John Doe
tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
1 1 1/1/2012 8
2 1 6/1/2012 5
3 1 9/1/2012 7
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
View 2 Replies
View Related
Nov 20, 2012
I have a table with 2 columns ItemID and ReturnDesc. A sample of the data is:
ItemId ReturnDesc
1244 Did not want
1244 Wrong color
3426 Return came with ticket 426
3571 Ticket 584 was not included
The only information I want to have returned is the number following the text 'ticket'. How do I do this?
View 3 Replies
View Related
Jul 9, 2007
hi,
i am new to SQL. i am trying to retrieve a set of data(i.e. set of rows which satisfies my condition given in the select statement). i want to output this recordset to my calling program i.e C#.net. how could i do this. i was adviced to use temp tables. but i am not sure how to do it. can any one say that using the temp table is the correct way. if so can u give me some sample code.
my proble is
Table name: ProductDetails Table
It has following columns
ProductID Name UnitPrice Category Stock
when i give a category value from front end it should bring me the rows which comes under the given category. there will be many rows under this category. how could i send this set of rows back to my calling program.
regards,
Johny
View 1 Replies
View Related
May 22, 2007
Hi there,I have tables with such structuretransaction_YYMM(idx,date,company_id,value)where YYMM stands for 2digits year and monthI want to define query (maybe view, procedure):select * from [???] where date>='2007-01-01' and date<='2007-04-30'which will grab data fromtransaction_0701transaction_0702transaction_0703transaction_0704and return all as onebest regardsRafal
View 3 Replies
View Related
Apr 9, 2015
Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.
As in the following should return the data:
'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'
And the following should not return the data:
'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'
The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.
So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.
View 4 Replies
View Related
Jul 23, 2005
I have SQL 2000 and need to retrieve fairly large amout of data (~50.000 characters) in XML format and then insert it into the field ofthe text type.As 'FOR XML' can't be used with either local variables, INSERT INTO orSELECT INTO this makes "XML support" quite useless in many aspects.Can anyone please help me in solving this.Thanks a lot for your help and time.Pavel
View 1 Replies
View Related
Jul 23, 2005
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.
View 2 Replies
View Related
Jul 23, 2005
VB.NET 2003 / SQLS2KThe Stored Procedure returns records within Query Analyzer.But when the Stored Procedure is called by ADO.NET ~ it produced thefollowing error message.---------------------------Exception Message: Failed to enable constraints. One or more rowscontain values violating non-null, unique, or foreign-key constraints.------------------------------------------------------Exception Source: System.Data---------------------------If I click OK past the error messages I will get data filling thedatagrid. However not as I would like to see it.Even though it returns the proper data rows and includes all thecolumns I asked for, it also returns plenty of columns I didn't ask for(all the columns of the main table) and all those columns are filledwith "null"In addition each row header contains a red exclaimation mark whch whenhovered over reads;"Column 'cmEditedBy' does not allow DBNull.Values."An interesting thing about this column 'cmEditedBy' is that there isnoting wrong with it and all rows for that column contain data.I believe this error is a mistake! But it probably indicates some otherproblem. How should I track its cause?M O R E ...Below is the code in the data layer, the stored procedure, and the datareturned within query analyzer.\'DataAdapterFriend daView041CmptCyln As New SqlDataAdapter'SqlCommandPrivate daView041CmptCyln_CmdSel As New SqlCommand'Add the commanddaView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel'SelectWith daView041CmptCyln_CmdSel.CommandType = CommandType.StoredProcedure.CommandText = "usp_View_041Cmpt_ByJobCyln".Connection = sqlConnWith daView041CmptCyln_CmdSel.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, _4, ParameterDirection.ReturnValue, False, CType(0,Byte), _CType(0, Byte), "", DataRowVersion.Current, Nothing))'Criteria.Add("@fkJob", SqlDbType.Text).Value = _"48c64a55-874d-40d0-addc-7245f5d9c118"'.Add("@fkJob", SqlDbType.Text).Value = f050View.jobIDEnd WithEnd With//\ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln(@fkJob char(36))AS SET NOCOUNT ON;SELECTJobNumber,DeviceName,ComponentName,Description,Quan,Bore,Stroke,Rod,Seconds,CylPSI,PosA,PosB,PosC,PosD,PosE,HomeIsRet,RetIsRetrac,POChecks,Regulated,FlowControl,PortSize,LoadMassFROM tbl040cmptINNER JOIN tbl030Devi ON fkDevice = pkDeviceIdINNER JOIN tbl020Proc ON fkProcess = pkProcessIdINNER JOIN tbl010Job ON fkJob = pkjobIdINNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeIdINNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeIdINNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeIdWHERE(fkJob = @fkJob)--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'AND fkComponentType = 2GO//(note - columns are wrapped)\F1111Clip DriverCylinderClip Driver_2 - Top -Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_222.1002.0001.0001.234NULL11000110011/8NPTNULLF1111ClipDriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_122.1002.0001.0001.000NULL01000110011/8NPTNULLF1111DegateCylinderDegate 1 -Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULLF1111Clip DriverCylinderClip Driver 1 -Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL//
View 1 Replies
View Related
Jul 29, 2007
Hi,
I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?
Thanks
View 1 Replies
View Related
Apr 13, 2007
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
View 3 Replies
View Related
