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.
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.
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.
I have this table below with customers having loans. Nou I want to write a query which retrieves all customers with exactly the same set of Loans.
Rules:
If I give C1 then I should get C2 (same set of loans) If I give C2 then I should get C1 (same set of loans) If I give C3 then I should get C4 (same set of loans) If I give C4 then I should get C3 (same set of loans)
If I give C5 then I should NOT get any row because there is no other Customer with the same set of loans. If I give C6 then I should NOT get any row because there is no other Customer with the same set of loans. If I give C7 then I should NOT get any row because there is no other Customer with the same set of loans.
I tried this query below but it doesn't give me always the same right results.
SELECT t1.LOAN, t1.CUSTOMER FROM LoanCustomer t1 WHERE EXISTS ( SELECT t2.LOAN, t2.CUSTOMER FROM LoanCustomer t2 WHERE CUSTOMER= 'C1' and t1.CUSTOMER != t2.CUSTOMER and t1.LOAN = t2.LOAN )
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT Film 5,000 15,000 20,000 Aero Elct 3,000 950 3,950 Desg Edu 150 150
Here€™s a simplified version of the DDL: CREATE TABLE invchead ( invoicenum int NULL , invoicedate datetime NULL , invoiceamt decimal(16, 2) NULL , custnum int NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct c.Name, c.GroupCode, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB, ...... (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS FILTER( ([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]), [Measures].[Sales] >= 10000 AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
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.
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'
I am aware of the problems with using identity. I would like a bitmore information on implementation. Concurency is a concern becausethis is a net application.Do I do this at the application level, in a trigger, or using anothermethod?I want to make sure that the method I choose is robust and maintainsthe integrity of the data.[color=blue]>From what I know, in this case not much, it would seem that the[/color]trigger is the best bet.If I were to do a table that stores the unique number counter, I'mthinking TableName and NextNumber fields could be created and thenused for all tables that need this requirement. Then my triggerwould look up the next number based on its table name, save thenumber as the ID and then increment the value by 1 or whatever stepI choose. Is this thread safe?Thanks,Greg
How to Get previous record thru sql query For the example my table: 1 usera item1 1.00 01/02/072 usera item1 2.00 02/02/073 userc item2 3.00 03/02/07 --how to use the query to make them join became like this (get/join with the next record) 1 usera item1 1.00 01/02/07 item1 2.00 02/02/073 userc item2 3.00 03/02/07 null null null >.<need help ... thanks alot
Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following dataRow Timestamp1 01/01/20072 15/03/20073 17/04/20074 05/05/20075 10/05/20076 11/06/20077 12/07/2007 My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?
I have a table called Customers which holds the customers details including CustID primary key a table called DVDs which holds all the dvd details including DVDID primary key and a table called WishList which holds the CustID and DVDID I want to take the CustID given my the user and display all the dvds in the wishlist with that CustID the code I have now is int CustID = Convert.ToInt16(Session["CustID"]); comm = new SqlCommand("SELECT DVDs.DVDID, dvds.Title, Director, Actor, SUBSTRING(Description,0,200) AS Description, Image FROM DVDs, Customers, WishList WHERE WishList.CustID = @CustID AND WishList.DVDID = DVDs.DVDID", conn); comm.Parameters.Add("@CustID", SqlDbType.Int).Value = CustID; but when i run the query it displays each dvd 5 times not sure why can any one help cheers
I'm a bit new to MS-SQL so i thought maybe you could help me.
This is my prob. I have an Access application that i have upsized to a MS-SQL server. I have a query based on 3 tables: Customers, Companys and Payreminders. When i run this query, i can't add new records or make any changes. If i only run the query with the tables: companys and payreminders i can add new records and edit them. If i run a query that's based on companys and customers, i can edit and add new records, but when i run the query based on companys and customers i can't add or edit records.
companys, payreminders: Editable payreminders, companys: Editable Companys, customers: Non editable Companys, payreminders, customers: Non editable
Something must go wrong when i use the companys and customers table. Can anybody help or give a suggestion what to do.
happy friday... my table has 200,000 + records and I want to see the particular record which I think it is causing the problem. How could i query 195,203rd record? thank you, yanoroo
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.
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
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ? I'm using sql Server 2000 Thank's
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:
id, studentid, date, canceled.
I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid
I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.
what would be grate is if there where a way of returning a the coresponding id like in:
select studentid, max(date), related(id) as ids from tablea where canceled=0 group by studentid
then I could do:
Select * from tablea inner join (select studentid, max(date), related(id) as ids from tablea a where canceled=0 group by studentid ) b on (a.id=b.ids)
Hi there, I am a little confused with some data ideas in .NET 2.0. First, I now understand there are profiles for storing per-user information (such as address, etc). Now, each of my users will have say an inventory of equipment, that they can edit, add, etc. However I am not sure if using just regular tables would be better. Any suggestions? Also, I understand using the profiles does not let you use things such as the DetailsView control, which would allow for automatic editing, adding, deletion, etc. This would be very nice to have, rather than implementing it myself. If this is the case, how do I associate the new database with the ASPNETDB database? For example I will have a table in my new database that has columns [UserId, EquipmentName, Quantity, Description]. Now how do I get the logged in user's user ID to display only their equipment in a GridView say? Thank you very much! Tristan
If i want to replicate data only for a particular customer code using SQL 2012 replication - is it possible. I believe that either the entire database or few tables can be replicated using SQL 2012.
Hello Guys, I really need you help to debug this query. OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW. CONDITION 1: RateReview field should have yesterday's date CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL ONe Customer can have many transwactions Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets. Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and do not send any email. This would be done by some tool known as StrongMail.
SELECT CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID, CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID, Film_id as MovieId, First_nm as FirstName, Last_nm as LastName, Email_nm as EmailAddress,
from ( select MIN(CAST(customer_guid AS varchar(36))) as Customer_GUID, Transaction_GUID from tblTransaction (nolock) where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm < GETDATE()
and Terminate_dm is null and customer_guid not in ( select CAST(customer_guid AS varchar(36)) as Customer_GUID from tblTransaction (nolock) where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm < GETDATE()
and Terminate_dm is null ) group by transaction_guid, customer_guid )z inner jointblTransaction a onz.Transaction_GUID = a.Transaction_GUID
I am fairly new to sql. Reading a table I need to show amount for each customer. Also I need to add amount for each customer that has more than one entry. What would the sql statement look like? Thank you.
Records in file: Company Amount Customer1 24.000 Customer2 36.000 Customer3 72.000 Customer1 20.000 Customer3 15.000
Desired results: Company Amount Customer1 44.000 Customer2 36.000 Customer3 87.000
I have two tables customers and orders. customerID is the foreign key for order table. If I pass customername I need to get information about each customer how many orders holding?
