Retrieve 1st And Last Record Of Each Customer

Oct 15, 2007

I have to retrieve first and last record of each customer according to the Date. Each customer has 10 - 15 records in the table and there are 3000 customers.
how can I retrieve this data.

regards

View 7 Replies


ADVERTISEMENT

SQl Query: How Can I Get Only One Record For Each Customer?

Oct 30, 2006



i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.

Userid, product id, createddate

1034 2000788 2006-09-01 14:50:19.880
1034 383002 7 2005-09-07 20:50:19.880

1034 4493903 2006-09-01 20:00:19.880

I am therefore making a query for getting the data.How can i get only one record for each customer?

Sincerely

wan.



View 3 Replies View Related

Select Most Recent Customer Record From Table Only?

Nov 6, 2007

If I have a table structure similar to the following, how might I query it to obtain the Transaction ID, Transaction Date, and Customer Name for the most recent transaction per customer only:


TransactionTable:
TransactionID TransactionDate TransactionType CustomerName
1 10/1/07 1 Bob
2 8/30/07 2 Janet
3 9/17/07 1 Mike
4 9/25/07 1 Bob


The following query will return all records in the table other than Janets...not what I want:
SELECT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC


The following query will return all records in the table other than Janets again, because DISTINCT will use the combo of TransactionID, TransactionDate, and Customer name for uniqueness...not what I want:
SELECT DISTINCT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC

The results set I'm looking for would be the following, where only the most recent entry per customer with TransactionType 1 is returned (i.e. one record for Bob):


TransactionTable:
TransactionID TransactionDate CustomerName
1 10/1/07 Bob
3 9/17/07 Mike


I believe I need an appropriate subquery to yield the results I desire, but can't sort out what it is? I do not want to execute multiple queries but subqueries are fine. Unfortunately there's probably an easy answer that my brain is not currently generating. Any help would be appreciated.


Note, this is not a real table, but a sample to illustrate the concept for the query I need.

Thanks.

View 6 Replies View Related

SQL Server 2012 :: Query Design - Find Most Recent Datetime Record Each Day For A Customer

Apr 2, 2015

So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:

SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)

[Code] ....

View 4 Replies View Related

How Can I Retrieve Nth Record Only?

Jul 6, 2001

Could you help me out?

I am interested in retrieving certain record among outputs.

For example, if I use the following sql,

select * from info order by name asc
====================================

then I can retrieve 25 rows from info table.

What I want to do is that I want to retrieve 15th record only among possible 25 records.

How can I solve this problem?

Thanks in advance.

View 2 Replies View Related

How To Retrieve One Record

May 23, 2006

I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.

Thanks.

View 3 Replies View Related

How To Retrieve MAX Record

Jul 9, 2013

I have retrieved the data using CTE . But still need the retrieve the latest row number record from my result.

;with cte as
(
Select ROW_NUMBER() over ( PARTITION by [T7S1_PRODUCT_CDE_original],[T7S1_BR_NO_original] order by [sql_updated] ) rn , [sql_updated]
,[T7S1_TYP_CDE]
,[T7S1_PRODUCT_CDE_original]
,[T7S1_BR_NO_original] from [interface_i084].[dbo].[tb_i084_ds_CeresTLStageFileDocDetail](nolock)
)
Select * from cte

My Query result:

rnsql_updatedT7S1_TYP_CDET7S1_PRODUCT_CDE_originalT7S1_BR_NO_original
12012-06-26 00:17:32.007703A0200030OOO 00066
22012-06-27 12:30:21.803703A0200030OOO 00066
32012-07-13 01:15:36.073703A0200030OOO 00066
12012-06-27 12:45:30.653703A0200030OOO 00083
12012-06-25 07:45:33.907703A0200030OOO 00090
22012-08-16 12:45:22.227703A0200030OOO 00090

Now Expecting:

rnsql_updatedT7S1_TYP_CDET7S1_PRODUCT_CDE_originalT7S1_BR_NO_original
32012-07-13 01:15:36.073703A0200030OOO 00066
12012-06-27 12:45:30.653703A0200030OOO 00083
22012-08-16 12:45:22.227703A0200030OOO 00090

I want to retrieve the MAX rn record..

View 1 Replies View Related

Retrieve The Record.

Nov 9, 2006

I'm trying to compare the fields if they are equal, retrieve it.
Because null <> null.
i cant retrieve the record. Is there anyway to retrive the records by comparing all the fields?

declare @table table(
ad_num int null,
ad_str1 varchar(50) null,
ad_num_suffix_a varchar (10) null,
ad_num_suffix_b varchar (10) null,
ad_unit varchar(10) null,

ad_num_test int null,
ad_str1_test varchar(50) null,
ad_num_suffix_a_test varchar (10) null,
ad_num_suffix_b_test varchar (10) null,
ad_unit_test varchar(10) null,
passed bit)

insert @table (ad_str1, ad_str1_test)
select 'apple road orad RD' , 'apple road orad RD'

select ad_num , ad_num_test,
ad_str1 , ad_str1_test,
ad_num_suffix_a , ad_num_suffix_a_test ,
ad_num_suffix_b , ad_num_suffix_b_test ,
ad_unit , ad_unit_test,
passed from @table

select * from @table
where ad_num = ad_num_test
and ad_str1 = ad_str1_test ad_num_suffix_a = ad_num_suffix_a_test
and ad_num_suffix_b = ad_num_suffix_b_test
and ad_unit = ad_unit_test

View 2 Replies View Related

How To Retrieve Last Updated Record

Dec 11, 2006

I have some set of records in my table.
The same set of records will be updated often. Now I have a column as "lastupdated"
While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed.
Means, the recently updated records should be displayed in datagrid.
Pls give me the sql code / i am also in need of a Stored procedure for this.
 I am working in SQL 2005

View 2 Replies View Related

Retrieve The ID Of The Record Just Added

Dec 12, 2003

I would like to retreive the identity field value of a record that was just added to the table.

In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.

This may be overkill, but here is my code to process the form:

Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @RegionID, " & _
"DistrictID = @DistrictID, " & _
"DateReported = @DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@RegionID, " & _
"@DistrictID, " & _
"@DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select

Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DateReported", SqlDbType.NVarChar))
...CODE...

sqlComm.Parameters.Item("@RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...

Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub

The response.redirect at the end of the sub is where I would like to put the identity field value.

View 6 Replies View Related

Retrieve Id After Adding A New Record

May 13, 2005

How do I retrieve the id of the record after I INSERT it?

View 4 Replies View Related

How Can I Retrieve The Id Of A New Record Before Update

Oct 14, 2001

Can anyone help with an effective way in retriving the id of the new record before input of any data into a form. We have a form where a few of the controls recordsource requires the new record id before they will display correctly. I have tried various ways to trigger the form afterupdate event in the hope that the id will be returned but get the error message "The data will be added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying recordsource"

Thanks in advance

View 1 Replies View Related

How To Retrieve Last Record In A Table

Aug 19, 2013

I have a bulk of employee records, i want to retrieve 1st record and last record in employee table how to retrieve the record

View 14 Replies View Related

Retrieve The Last Record Of A Table

Jul 10, 2007

Hi!
I needto find the last record that has been inserted on a table.
If I perform a SELCT * FROM mytable , will I receive the data based on the inserted order (natural order)? In that case i'll simply need to check the last record of the results I got in my application to find the last inserted one?
(no I am not looking for an identity number ^^)

thank you

View 6 Replies View Related

How Do I Retrieve A Record By Primary Key?

Sep 29, 2006

Visual Basic 2005 Express:

I want to retrieve an SQL DataBase table record whose primary key is 4.

How do I read in that record and how do I pick up data from it?

View 6 Replies View Related

Retrieve Record From Database Problem

Sep 21, 2006

i m using sql server 2000 with asp.net with c#

i hv 4 customer records in the customer table starting from C1001 to C1004, i wanna ask is that when a new record is add to the table, the record will be placed at the bottom of the table. For example,
CustomerID






Customer ID

C1001

C1002

C1003

C1004

C1005


When i add a new record which is C1005, is it the record will be placed as shown in the table?


if so, that's mean can straight away use the datarow to retrieve the largest number which is C1005, right??? Thx 

View 1 Replies View Related

Retrieve Last Inserted Or Updated Record

Nov 22, 2007

Hi
I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.
On the event handler I am usin select ID,Name from my [table];
this will return all record from database.
I just want to get the record which is inserted or updated.
Can u help me in that.
Take care
Bye

View 4 Replies View Related

How To Retrieve Identity Of Just Inserted Record???

Jan 30, 2008

Ok I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated.
 
Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 
 
 
Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString()
 
 
personalContactDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
personalContactDataSource.InsertCommand = "PersonalContactInsert"
 
 personalContactDataSource.InsertParameters.Add("FirstName", FirstName.Text)
personalContactDataSource.InsertParameters.Add("LastName", LastName.Text)personalContactDataSource.InsertParameters.Add("KeyPerson", KeyPerson.Checked)
personalContactDataSource.InsertParameters.Add("DayPhone", DayPhone.Text)personalContactDataSource.InsertParameters.Add("EveningPhone", EveningPhone.Text)
personalContactDataSource.InsertParameters.Add("Fax", Fax.Text)personalContactDataSource.InsertParameters.Add("Email", Email.Text)
personalContactDataSource.InsertParameters.Add("HomeAddress", HomeAddress.Text)personalContactDataSource.InsertParameters.Add("City", City.Text)
personalContactDataSource.InsertParameters.Add("State", State.Text)personalContactDataSource.InsertParameters.Add("Zip", Zip.Text)
personalContactDataSource.InsertParameters.Add("ReqEffectDate", ReqEffectDate.Text)personalContactDataSource.InsertParameters.Add("MRID", MRID.Text)
personalContactDataSource.InsertParameters.Add("CurrentPremium", CurrentPremium.Text)personalContactDataSource.InsertParameters.Add("CurrentCarrier", CurrentCarrier.Text)
personalContactDataSource.InsertParameters.Add("CurrentDeductible", CurrentDeductible.Text)personalContactDataSource.InsertParameters.Add("CurrentCoins", CurrentCoins.Text)personalContactDataSource.InsertParameters.Add("ReasonForQuote", ReasonForQuote.Text)
 
 
End Sub
 
 
And here is my Stored ProcALTER PROCEDURE dbo.PersonalContactInsert

@FirstName varchar(30),@LastName varchar(30),
@DayPhone varchar(14),@EveningPhone varchar(14),
@Fax varchar(14),@Email varchar(60),
@HomeAddress varchar(80),@City varchar(30),
@State char(2),@Zip char(5),
@KeyPerson bit,@ReqEffectDate smalldatetime,
@CurrentCarrier varchar(30),@CurrentPremium smallmoney,
@CurrentDeductible smallmoney,@CurrentCoins smallmoney,
@ReasonForQuote varchar(150),@MRID int,
@ClientNumber int OUT
 
AS
 
INSERT INTO PersonalContact(FirstName, LastName, DayPhone, EveningPhone, Fax, Email, HomeAddress, City, State, Zip, KeyPerson, ReqEffectDate, CurrentCarrier, CurrentPremium, CurrentDeductible, CurrentCoins, ReasonForQuote, MRID, DateTimeStamp)
VALUES(@FirstName,@LastName,@DayPhone,@EveningPhone,@Fax,@Email,@HomeAddress,@City,@State,@Zip,@KeyPerson,@ReqEffectDate,@CurrentCarrier,@CurrentPremium,@CurrentDeductible,@CurrentCoins,@ReasonForQuote,@MRID, GetDate())
SET @ClientNumber = SCOPE_IDENTITY()
RETURN
 

View 8 Replies View Related

Hard Task To Retrieve A Value From A Record?!

Feb 12, 2008

Hi!I'm desperating here!!! Two questions:1º  Is line 13 realy selecting all the records with the username samurai (in this case)?!2º How do I fill the Boolean SeExiste var with a value from the record?1 Dim UserIDParameters As New Parameter
2
3 UserIDParameters.Name = "ProdUserID"
4
5 UserIDParameters.DefaultValue = "samurai"
6
7 Dim LoginSource As New SqlDataSource()
8
9 LoginSource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString()
10
11 LoginSource.SelectCommandType = SqlDataSourceCommandType.Text
12
13 LoginSource.SelectCommand = "SELECT FROM aspnet_Users (FirstTime) VALUES (@UserIDParameters) "
14
15 Dim SeExiste As Boolean
16
17 SeExiste = LoginSource.SelectParameters("FirstTime").DefaultValue
 I'm a newbye and despite this simple thing that in normal ASP is very easy to do!!! Please help me!Thanks in advance!  

View 4 Replies View Related

Retrieve The GUID For Inserted Record

May 2, 2006

I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user.
 
SqlConnection sql_connection = new SqlConnection("Server=xxx.xxx.xx.xxx;uid=xxxxxxxx;password=xxxxxxx;database=xxxxxxx;");SqlCommand sql_command = new SqlCommand("INSERT INTO members (member_sex, member_cpr, member_nationality, member_block, member_gov, member_daaera, member_email, member_mobile, member_created_ip) Values (@member_sex, @member_cpr, @member_nationality, @member_block, @member_gov, @member_daaera, @member_email, @member_mobile, @member_created_ip)", sql_connection);
sql_command.Parameters.Add(new SqlParameter("@member_sex", Session["member_sex"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_cpr", Session["member_cpr"]));sql_command.Parameters.Add(new SqlParameter("@member_nationality", Session["member_nationality"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_block", Session["member_block"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_gov", "GOV"));sql_command.Parameters.Add(new SqlParameter("@member_daaera", 6));sql_command.Parameters.Add(new SqlParameter("@member_email", Session["member_email"].ToString().ToLower()));sql_command.Parameters.Add(new SqlParameter("@member_mobile", Session["member_mobile"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_created_ip", Request.UserHostAddress.ToString()));
sql_connection.Open();sql_command.ExecuteNonQuery();sql_connection.Close();
 

View 1 Replies View Related

Retrieve Last Record For Each Month Between Two Dates

Oct 10, 2013

I need to retrieve the last record for each month between two given dates from a unique table that contains on record per day.

View 3 Replies View Related

Retrieve Identity Key Of Record Just Inserted?

Aug 21, 2014

Best way to retrieve the identity key of the record just inserted?This question is for discussion purposes; the business process that spurred the question is currently working.Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:

Code:
BEGIN
BEGIN TRANSACTION
INSERT INTO tblTools
([Desc],CreateDate,Model,CreatedBy,Notes)

[code]....

In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting. In SQL Server, I know options include:

Code:
IDENT_CURRENT('tblX')
SCOPE_IDENTITY
@@IDENTITY
among other methods.

Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).

View 2 Replies View Related

Retrieve When All Values In Group Have Same Record

Feb 25, 2015

Here is some sample data:

CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)

[code]...

I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?

View 2 Replies View Related

How To Retrieve Deleted Record In Table?

Feb 20, 2008



Hi,

i have deleted 5 to 10 records in a table. is there any way to retrieve the deleted records?

please help me in this regards.

Thanks in Advance.

M.ArulMani

View 9 Replies View Related

Retrieve Good Records From A Bad Record Table

May 17, 2006

I have a situation where I need a table if bad items to match to. Forexample, The main table may be as:Table Main:fd_Id INT IDENTITY (1, 1)fd_Type VARCHAR(100)Table Matcher:fd_SubType VARCHAR(20)Table Main might have a records like:1 | "This is some full amount of text"2 | "Here is half amount of text"3 | "Some more with a catch word"Table Matcher:"full""catch"I need to only get the records from the main table that do not haveanything in the match table. This should return only record 2.

View 1 Replies View Related

How To: Retrieve A Limited Number Of Records From A Record Set

Apr 24, 2008

I€™m working on a database project that will ultimately contain millions of records for each lot. In addition, each lot will have up to 96 corresponding serial number records.

I would like to add a SQL parameter that would tell the database engine to only return X number of records.

For Example:
If table TBL_LOTS contains one million records I would like to limit the return set to 100 for example.

What would I need to add to the SQL command to below to restrict the data set to the first 100 records in the set of one million?

SELECT [LOT NUMBER]
FROM TBL_LOTS
WHERE [STATMENTS]

View 3 Replies View Related

SQL Server Everywhere - Retrieve Identity Column After Insert Record

Jun 23, 2006

Hello

Using Visual Studio 2005 Prof and SQL Server everywhere.

How do get the identity column value after insert record.

With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())



How do this is sql everywhere??



regards

View 1 Replies View Related

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

Help Getting Top 1 Row For Each Customer

Oct 26, 2007



Hi ALl,
I am using SQl server 2005,
I have the following data

customer order_id order_code complete
1328004 3462 18 1
1328004 3463 18 1
1554477 3689 18 1
1554477 4123 18 0

Here I need results like this,

customer order_id order_code complete
1328004 3462 18 1
1554477 3689 18 1

the first row for each customer and order_code, so far I haven't been able to come up with the correct query.

select top 1 a.customer,a.order_id,a.order_code,a.complete
from order a
order by a.customer,a.order_id

My query only returns the top 1 row .Please point me in the right direction.

Thanks in advance

View 4 Replies View Related

Customer Identity

Apr 13, 2008

Hi, is there a way to modify the way an identity column counts? for example, I want the counter to reset every month, I store the number of the month in another colum, so I just need the identity to take the month in to account, so the first day of each month it would start from 1 again. 

View 4 Replies View Related

Customer Table

Feb 13, 2007

I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)

so to get the invoice_customer address for the customer account - 13301

SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'

i want to add the above InvoiceAddress to the query below:

select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'

How would I put the 2 togtheer...

Thanks in advance!!!

View 2 Replies View Related

Pro / Con Of Different Databases Per Customer

Jul 18, 2007

Hello everyone -

Is there a pro / con reason why a corporation would create a new database for each customer??

I can understand the obvious reason - to keep the companies apart,
but other than that single reason are there any other ones?

thanks
tony

View 7 Replies View Related

Last 3 Orders Per Customer

May 10, 2007

Good day.
Am new with SQL server and i use SQL Server 2005

i have 3 tables (1 = customers, 2 = orders, 3 = keys) both these tables are linked by the key table

I wanna be able to "view" (as am only allowed to view) the last three orders for every customer.

I tried using the top predicate/ clause with a group by & count but unfortunately am getting no where.

Please kindly help

View 14 Replies View Related







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