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
ADVERTISEMENT
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
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
View Related
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
May 16, 2014
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.
Table LoanCustomer:
[KEY] [LOAN] [Customer]
1L1C5
2L2C1
3L2C2
4L3C1
5L3C2
6L4C3
7L4C4
8L5C6
9L5C7
10L6C7
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
)
View 3 Replies
View Related
Jan 22, 2008
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 )
CREATE TABLE customer (
custnum int NULL ,
groupcode varchar (4) 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.
Thanks
View 4 Replies
View Related
Feb 4, 2015
I'm having problems filtering in my MDX query.
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]
[Code] ....
The dates are hard-coded for testing purposes.
View 0 Replies
View Related
Apr 7, 2008
Hi,
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.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
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
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
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
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
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
Mar 12, 2008
What statements can be used to grab the most recent customer record?
View 3 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Feb 1, 2007
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
View 4 Replies
View Related
Oct 29, 2007
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?
View 11 Replies
View Related
Apr 15, 2008
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
View 2 Replies
View Related
Dec 14, 2007
Hello,
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.
Thanks.
View 1 Replies
View Related
Mar 31, 2008
Hi
I have a column in a table which has html text. Eg
<p>[Video:-123xyz] <br />A video showing blah blah</p>
I wish to update the data between the two square brackets within the rest of the text to:
<p>[View:http://www.video123.com/-123xyz] <br />A video showing blah blah</p>
...so basically i need to find all occurrences of '[Video:' and replace it with '[View:http://www.video123.com/' .
Please can someone point me in the right direction?
cheers
View 14 Replies
View Related
May 7, 2004
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
View 8 Replies
View Related
Jun 16, 2015
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]....
View 4 Replies
View Related
May 30, 2006
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
_Hendra_"
View 1 Replies
View Related
Jul 20, 2005
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*/
View 6 Replies
View Related
Oct 19, 2006
I am having problems with a SQL query.
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)
View 5 Replies
View Related
Mar 4, 2006
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
View 3 Replies
View Related
Jan 26, 2007
I have given a Zip file with mdf + ldf file to a customer and he cannot attach it to his server
what can be the problem ?
any rights ?
thank you
View 4 Replies
View Related
Dec 17, 2014
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.
View 2 Replies
View Related
Jan 22, 2014
I have three SQL tables that I am trying to join. One is Called Customer and the other Orders.
Customer contains these fields that I need
CustomerID,Email,FirstName,LastName,
Orders has these that i need
OrderNumber,CustomerID,OrderTotal,OrderSubTotal
OrdersShoppingCart has these that I need
OrderNumber,CustomerID,OrderedProductSKU,OrderedProductName,OrderedProductPrice
What I would like to try to export is this
CustomerID
FirstName
LastName
Order ID
SKU
Order Total
This would be grouped for each customer for all total orders. Do you think this is possible?
View 9 Replies
View Related
Jan 31, 2007
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
View 8 Replies
View Related
Mar 22, 2007
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
View 1 Replies
View Related
Mar 3, 2008
Hi,
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?
View 3 Replies
View Related
Sep 17, 2006
i have table:
customer(customerid, age,sex....)
orderdata(orderid, customerid,day)
orderdetails(orderid, productid, quantity)
products(productid, productname,...)
now, i want to show some product for customer when i now him age and sex.
e.ct: if he is a man and age =20 i show product : ball, pull, sport close....... if man is a woman , i show lips, babara, t_shirt, skirt....
if man is a chirdren, i will show joy, story for chidren....
how i create my mining model. and how i query for result in DTS
View 1 Replies
View Related
