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


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

Trigger That Won't Allow A Member To Exceed Certain Number Of Loans At A Time Based On Their Category

Apr 14, 2015

I have an assignment to make a library. Right now I'm at the point of implementing business rules. One is that I need to create a trigger that won't allow a member to exceed a certain number of loans at a time, based on their category (student = 5, Teacher =10 and Researcher = 20).

View 1 Replies View Related

Need Help With Query -- Sales Data By Customer.GroupCode In Monthly Columns

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

SQL Server 2008 :: MDX Query Filtering - Return Sales For Each Customer For Past 6 Weeks

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

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

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

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

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

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

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

T-SQL (SS2K8) :: Split String To Show Only Customer

Aug 7, 2014

The data we are getting from our shipping company has the customer name and customer number attached.

so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

I need to match this up with our customer table... on Customer Name.

View 9 Replies View Related







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