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 would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER( [CUSTNO] varchar(5) NOT NULL, [ID] CHAR(9) NOT NULL, [NAME] VARCHAR(128) NOT NULL, [ADDRESS] VARCHAR(128) NOT NULL, [DATEOFBIRTH] DATE NOT NULL,
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.
1) CustomerID 2) FirstName 3) MiddleName 4) SurName 5) Title 6) Marital Status 7) Education 8) Occupation 9) Annual Income 10) Line of Business 11) DOB 12) Father Name 13) Mother Name 14) SpouseName 15) Gender 16) Email 17) MainTel 18) Home Tel 19) Passport Number 20)---------------------- 21)- - - - - - - - - - -
100)------------------- Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.
We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks). A single address includes address1/address2/city/country/state/pincode fields. In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.
As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)
Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
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 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
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.
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 )
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?
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.
my source flat file has many rows per customer, but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups). I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database? As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
I have a contact table and a customer table. The two tables will contain columns like First name Last Name, Date of Birth Post Code, House Number Street Name etc.
I would like to find the different combinations in which I can relate the customer and contact data. Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me. From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?
I am very new to Data Mining and would like to have some direction as to how to progress with this.
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.
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables in SQL dbs which hold a lot of date consisting of statistics, errors, logs etc. They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
Hi all,I need sone help on generating a SQL statementi had 3 tables nowCustomer[color=blue][color=green]>> Name>> Acct No>> Address>> Phone[/color][/color]Invoice[color=blue][color=green]>> Invoice no>> Customer no[/color][/color]Invoice Details[color=blue][color=green]>> Invoice No>> Commence Date>> Expiry Date>> Amount[/color][/color]Every month, the customer will come in to pay for the bills. 1seperate row is generated for each invoice.I need to generate a report stating customer who had came in to payfor the month of march (expiry date=31/3/2004) but still have not makepayment for the month of April (date commence >1/4/2004).Can anyone help?
SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending') AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders. So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
I have a customer that wants to remove a power user from her company.
She has given him the SA password (against my advise) and he has his own user account setup in the database.
I am not sure if he has created any backdoor(s) into the SQL box or not - I left him kinda on his own and didn't pay too much attention to him.
I assume people have faced this problem before, and i am looking for a best practices or "here is what we did" post mortem of how they handled the issue.
I need to load a customer database onto our SQL 2008 server. I always use restore database option in the management studio and create a new database from device (customer database backup file), it used to work just fine. But when I do the same now, I get this below error:
CREATE DATABASE permission denied in database 'master'. RESTORE HEADERONLY is terminating abnormally.(Microsoft SQL Server,Error: 262)
I also tried to create new database option in the management studio, but get the same error. I did run management studio as 'Run as admin'.
