DTS Select Using Record Position

Jul 20, 2005

Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is being
maintained by an outsourced company and therefore I can't change the
table structure or even ask them to. Anyway, this table currently has
about 104,000 records. I am building a package to check it and pull
out the most recent records and put them where they go in my SQL
Server 2000 tables. The only way I can think of to get the most
recent records is to use a global variable in the package to remember
the record count and then get those records from that record position
on. Problem is, I have no idea how I would go about selecting records
from a record position. Does anybody have any ideas or should I be
using a different approach? There are no time stamps to work from. I
was told that the AS400 records, including updates, are appended to
the table, which is why I thought this approach made sense. I would
truly appreciate any help.

View 1 Replies


ADVERTISEMENT

Autoincrement Record Position In A View

Nov 13, 2003

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let’s say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@hotmail.com

Thank you

Christos Stavrinou

View 2 Replies View Related

Getting Record Position In Sql Server 2000

Mar 25, 2008

hii am using SQL server 2000. I want to know the record position whileretrieving records. its like row_number() in sql server 2005Is it possible?

View 5 Replies View Related

Transact SQL :: Add Position Of Record Based On Certain Date Of Transaction

Aug 4, 2015

I built the following query to add the position of a record based on a certain date of the transaction of a customer:

select
CustomerID
, Product
, purchasedate

[code]....

View 6 Replies View Related

How To Get Table Record's Position In Comparison To Other Records Based On Numeric Field?

Apr 2, 2007

Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?

View 6 Replies View Related

Transact SQL :: Select Column By Ordinal Position

Apr 16, 2012

Is there any way to select columns by ordinal position rather than by name?

Something like
Select t.[1] as col1, t.[2] as col2
FROM table t

The question comes because of a very specific situation where I've got 2 databases that have exactly the same schema at a column/datatype level but the column names are different.   There are a few other ways to skin the cat but I'm interested if it can be done this way - without a join to syscolumns.

View 12 Replies View Related

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View 1 Replies View Related

Select One Record Only

Nov 14, 2001

Hi

Here is my example: I have the following table

ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith

I want to select only one record of each ID, the output should be

ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543

It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.

Thanks

View 4 Replies View Related

Trying To Select Last Record...

Nov 8, 2004

I have a sql query that looks for values in a few different databases....

is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?

View 2 Replies View Related

Select One Record

Sep 18, 2013

I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?

View 3 Replies View Related

Using TOP To Select The 2nd Highest Record

Jun 19, 2007

Hai frendz,

I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-

"select top 1 EID, Salary from Employee ORDER BY Salary DESC"

Now I need to write a query which selects the second highest salary.
So how to achieve this?..

thanx

View 6 Replies View Related

How To Lock Record On Select

May 29, 2008

hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.

View 1 Replies View Related

Select Only If There's A Same Record In The Table

Apr 26, 2005

i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.

View 1 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

How To Select First Record With Out Using Rowcount

Mar 20, 2002

Hi,
I need to select first record from a table.
This can be comfortably achieved by usibg set rowcount 1.
I need to do this with out using rowcount.

This is urgent.
Thanks
Krishna

View 2 Replies View Related

Select Next And Previous Record

Aug 28, 2001

Hi!

I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records)

Is this possible?

View 3 Replies View Related

How Can I Select Nth Record In A Table.

Nov 7, 2001

hi friends,

I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.

name, totmarks
-----------------
kumar 145
ravi 300
jude 189
geeetha 320
rajesh 251
guru 190
ramesh 99
----------------------

i am having records like this.pl help me. thank u

regards
raj

View 1 Replies View Related

How To Select Record Only In One Table?

Feb 14, 2006

Hello, everyone:

I have two tables like:

Table A
IDCodeANumA
1D1289
2C4300
3C9409

Table B
IDCOdeBNumB
1D3266
2C4300
3C7101

How to find out the records that is in Table A not in Table B, and in Table B not in Table A? That means C4/300 should not be selected.

Thanks

ZYT

View 3 Replies View Related

How To Select One Record Many Times?

Sep 14, 2006

The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.

Any suggestion will be appreciated.

ZYT

View 4 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 TOP 2 From Each Details Record

Apr 24, 2007

Hi everyone,

I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.


Example :
=======
Details table is as follows
HeaderID DetailsID
1 1
1 2
1 3
2 4
3 5
3 6
3 7
3 8


output should be:
TransDate SupplierName HeaderID DetailsID
1/1/2000 abc 1 1
1/1/2000 dsds 1 2
2/3/2003 fgd 2 4
2/4/2005 sdsd 3 5
1/1/2006 fgfdg 3 6


I am using the following query

SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
Header H, Details D
WHERE H.HeaderID = D.DetailsID
AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)


As I am dealing with very huge data it is taking very long time to execute.

Is there any better way to accomplish the task?

Thanks.

Riyaz

View 14 Replies View Related

How To Select Earliest Record

Mar 17, 2012

im trying to get the earliest record. data is below

note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332/ 20090106<---- / 20081219/ 20090106
2332/ 20090323/ 20081219/ 20090323
2332/ 20090413/ 20081219/ 20090413
2332/ 20090507/ 20081219/ 20090507

because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result

note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332 20090106 20081219 20090106

View 5 Replies View Related

Select Last Record By Group?

May 18, 2012

query to show last record/Partner or PartnerName.

select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc

View 4 Replies View Related

Select Max Record With A Condition

Sep 17, 2012

I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other

IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45

So I need a query to return

IDTimestamp
409/17/2012 11:05:45

as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.

View 6 Replies View Related

SQL Select Last Record Per Group

Jan 24, 2004

I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3

TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1

I need a view or SP to return the following:

ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1

It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx

View 2 Replies View Related

Select Nth Largest Record - How To?

Feb 3, 2004

How do i select first name of the lets say 6th highest salary of an employee?

is it use Select TOP statement?

p/s: I just want the 6th largest fname not the top 6. so it will only return one value.

Thank you in advance for your reply.

View 1 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

Select And Display Only One Record

Sep 18, 2013

I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.

How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?

View 3 Replies View Related

Select One Record From Each Group

Jan 21, 2014

I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?

View 1 Replies View Related

How To Select Last Record In Table

Oct 15, 2007

Hey guys, is there an easy way to select the very last record in a table?

View 5 Replies View Related

How To Select Specific Record.

Oct 24, 2007

Group_Code *****Station_nbr*****Beg_Eff_Date*****End_eff_date

00000002 D01G00733 1/1/2007 8/31/2007
00000002N D01G00733 4/1/2007 8/31/2007
00000002W D01G00733 1/1/2007 12/31/2007


For the report I just want to be able to pick up the first and the last line.

Using dbo.Station_group Table

Thank you for the help

View 14 Replies View Related

Select Bottom Record

Feb 19, 2008

Hi Guys, I am trying to select the bottom record in a tabel based on a primary key and having no luck. Can some please help. This is how I have tried.


"SELECT Bottom Week,DOR from Observation_Record Where ID = '" + ID + "'"Thanks Trafmore

View 4 Replies View Related

How To Select Only The First Record From A Group

Sep 18, 2006

I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.

Thanks,

BobD

View 2 Replies View Related







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