Adding Amount For Each Customer

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


ADVERTISEMENT

SQL Server 2008 :: Find Locations With MAX Sales Amount Per Customer

Feb 13, 2015

I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.

CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250

I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.

CustLoc
A35
B15

If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.

View 2 Replies View Related

How To Separate Period Amount From YTD Amount

Mar 18, 2008

I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:

SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid

FROM
UrsinusCollege.dbo.gl7accounts a

join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid

where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')

order by a.accountnumber

Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.

Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.

Thanks very much.

Sue

View 6 Replies View Related

Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,



I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.



I kindly request for help.

Thanx in advance.

Ronnie

View 4 Replies View Related

SQL 2012 :: Log File Data Transfer Amount (MB) Versus Data File Transfer Amount (MB)

Mar 19, 2014

In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?

I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?

I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?

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

Need Most Recent Customer

Mar 12, 2008

What statements can be used to grab the most recent customer record?

View 3 Replies View Related

Customer IDs Without Identity

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

Implementing A Customer System

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

Problem With Mdf File For Customer

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

SQL 2012 :: Replication Only For A Customer?

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

Finding Orders Per Customer

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

Query To Get Customer With Same Set Of Loans

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

Customer Should Receive Email Only Once

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

Count Of Orders For Each Customer

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

Predict Product With Sex And Age Of Customer.

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

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

Which Algorithm Is Better For Customer Retention

Jul 25, 2006

Hi

Pl any one tell me which algorithm is better for Customer retention Using SQL server 2005 analysis services

It will be great if some one can give the same with example of data model with key column , and rest

Thanks in Advance

Rajesh Ladda

View 3 Replies View Related

Grouping Rows By Customer

Nov 8, 2006

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)



Thanks

Vlad

View 4 Replies View Related

Relate A Contact To Customer

Feb 8, 2007

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.

View 1 Replies View Related

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

Maintaining SQL Server At Customer Sites

Apr 18, 2003

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?

Thanks for any input

mipo

View 1 Replies View Related

Updating Orders From Customer Table

Sep 26, 2012

I want to update the orders that has invoice number empty when doing a change in the customer table Nm.

My order table is named order and customer is named customer, they both have a column named CustId and Nm (name).

This works fine and shows in my case column Nm in 4 rows (for orders);

select
Order.Nm
from
Order
Join Customer
on Order.CustId=Customer.CustId
where
Order.CustId=@variable and InvoNo=''

But the update statement gets error;

Update
Order
set
Order.Nm=Customer.Nm
from
Order
inner join Customer
on
Order.CustId=Customer.CustId
where
Ord.CustId=@Variable and InvoNo=''

I use SQL 2008....

View 13 Replies View Related

Determine That ID Is Unique In Customer Table

Oct 12, 2014

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,

[Code] ....

View 2 Replies View Related

SQL Statement For Generating Expired Customer

Jul 20, 2005

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?

View 4 Replies View Related

How To Debug The Customer Data Provider?

Apr 10, 2007

Hello,

I am doing a customer data provider dataextension such as FsiDataExtension.

But I want to know how to debug it?



Thank you.



Jeffers

View 1 Replies View Related

Reporting Services :: Adding All Columns To Table Without Adding One By One

Sep 3, 2015

Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.

View 7 Replies View Related

Select Customer Last Inserted Order Details

Sep 15, 2007

Hi,
Check this SQL


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

View 4 Replies View Related

Customer Wants To Remove Power User (was Need Some Advise)

Jul 19, 2007

Hi everyone,

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.

thanks
tony

View 4 Replies View Related







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