Attach db ... but changing its db name

I've a copy of my customer db detached from its server.
I need to import into my SQL to compare data and structure with my
own to release him some new feature. Problem is how to import it.
If I simple attach db, SQL server mustn't do it, trapping duplicate
db name or something else (I wish is so and not overwrite db).
If I detach my db, and attach customer db ... I must rename db ...
but how? until today I never read of some function that rename
db ...
Can anyone give me more info please?
--
Sincerely
Andrea Morowhile attaching db with sp_attach_db you can specify the required name of
the database to be. If you want to rename the existing database you can make
use of system stored proceudre sp_renamedb
--
-Vishal
Andrea Moro <moroandreaET@.tiscalinet.it> wrote in message
news:#kH0j9LcDHA.1204@.TK2MSFTNGP12.phx.gbl...
> I've a copy of my customer db detached from its server.
> I need to import into my SQL to compare data and structure with my
> own to release him some new feature. Problem is how to import it.
> If I simple attach db, SQL server mustn't do it, trapping duplicate
> db name or something else (I wish is so and not overwrite db).
> If I detach my db, and attach customer db ... I must rename db ...
> but how? until today I never read of some function that rename
> db ...
> Can anyone give me more info please?
> --
> Sincerely
> Andrea Moro
>sql

at least one item among all

I do not know how to project the customers who bought at least one of each of the color tv, white/black, mono. It is like finding which customer bought 1 tv from each type available at the same time. in the TELEVISION table the types are: color, white/black, mono.

As an example someone buys 5 tv among them, he has 2 color tv, 1 white/black, 2 mono however he did purchase one of each type of tv.

I need to project this output:

CustID Customer Name State

-- --

101 Jim White Ohio

234 Karl Smith Pennsylvania

Here is the tables structure

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

EmpIDFK size Quantity

TotalPrice Weight

CustomerIDFK SerialNumberPK LineNbr

CUSTOMER Table

CustomerIDPK

FName

LName

Address

City

State

Hi Mermaid

The example below will return the data you requested.

Chris

DECLARE @.INVOICE TABLE (InvoiceNbrPK INT, CustomerIDFK INT)
DECLARE @.TELEVISION TABLE (Type VARCHAR(13), SerialNumberPK INT)
DECLARE @.INV_LINE_ITEM TABLE (InvoiceNbrFK INT, SerialNumberFK INT, Quantity INT)
DECLARE @.CUSTOMER TABLE (CustomerIDPK INT, FName VARCHAR(20), LName VARCHAR(20))

INSERT INTO @.TELEVISION(Type, SerialNumberPK)
SELECT 'Mono', 1 UNION
SELECT 'Colour', 2 UNION
SELECT 'Black & White', 3

INSERT INTO @.INVOICE(InvoiceNbrPK, CustomerIDFK)
SELECT 1, 1 UNION
SELECT 2, 2 UNION
SELECT 3, 3 UNION
SELECT 4, 4

INSERT INTO @.CUSTOMER(CustomerIDPK, FName, LName)
SELECT 1, 'Kent', 'Waldrop' UNION
SELECT 2, 'Arnie', 'Rowland' UNION
SELECT 3, 'Louis', 'Davidson' UNION
SELECT 4, 'Umachandar', 'Jayachandran'

--Set up the data so that Kent and Umachander have both bought one of each TV
INSERT INTO @.INV_LINE_ITEM(InvoiceNbrFK, SerialNumberFK, Quantity)
SELECT 1, 1, 3 UNION
SELECT 1, 2, 2 UNION
SELECT 1, 3, 1 UNION
SELECT 2, 2, 5 UNION
SELECT 2, 3, 2 UNION
SELECT 3, 2, 3 UNION
SELECT 4, 1, 1 UNION
SELECT 4, 2, 1 UNION
SELECT 4, 3, 1

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
INNER JOIN @.INVOICE i ON i.CustomerIDFK = c.CustomerIDPK
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
GROUP BY c.CustomerIDPK,
c.FName + ' ' + c.LName
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2)

|||

Maybe something like this?

declare @.customer table
( custId integer,
FName varchar(20),
LName varchar(20),
State varchar(30)
)
insert into @.customer values (101, 'Jim', 'White', 'Ohio')
insert into @.customer values (102, 'Donna', 'White', 'Illinois')
insert into @.customer values (234, 'Karl', 'Smith', 'Pennsylvania')
insert into @.customer values (235, 'Karl', 'Benson', 'Guam')
--select * from @.customer

declare @.invoice table
( invoiceNbr varchar(15),
custId integer
)
insert into @.invoice values (1, 101)
insert into @.invoice values (2, 102)
insert into @.invoice values (3, 234)
insert into @.invoice values (4, 234)
insert into @.invoice values (5, 234)
--select * from @.invoice

declare @.television table
( serialNumber varchar(25),
type varchar(12)
)
insert into @.television values ('Dlx-0001', 'Color')
insert into @.television values ('Dlx-0002', 'Color')
insert into @.television values ('Dlx-0003', 'Color')
insert into @.television values ('Clr-0001', 'Color')

insert into @.television values ('BW-00001', 'white/black')
insert into @.television values ('BW-00002', 'white/black')

insert into @.television values ('Mon-0001', 'Mono')
insert into @.television values ('Mon-0002', 'Mono')
insert into @.television values ('Mon-0003', 'Mono')
--select * from @.television

declare @.inv_line_item table
( invoiceNbr varchar(15),
serialNumber varchar(25)
)
insert into @.inv_line_item values (1, 'Dlx-0001')
insert into @.inv_line_item values (1, 'BW-00001')
insert into @.inv_line_item values (1, 'Mon-0001')

insert into @.inv_line_item values (2, 'Dlx-0003')
insert into @.inv_line_item values (2, 'Mon-0003')

insert into @.inv_line_item values (3, 'Dlx-0002')
insert into @.inv_line_item values (4, 'BW-00002')
insert into @.inv_line_item values (5, 'Mon-0002')
--select * from @.inv_line_item

select custId,
[Customer Name],
state
from ( select c.custId,
c.fName + ' ' + c.LName as [Customer Name],
state,
count(distinct t.type) as typeCount
from @.customer c
inner join @.invoice i
on c.custId = i.custId
inner join @.inv_line_item l
on i.invoiceNbr = l.invoiceNbr
inner join @.television t
on l.serialNumber = t.serialNumber
and t.type in ('Color', 'Mono', 'White/Black')
group by c.custId,
c.state,
c.lName,
c.fName
having count(distinct t.type) = 3
) x

-- custId Customer Name state
--
-- 101 Jim White Ohio
-- 234 Karl Smith Pennsylvania

I like Chirs' solution better; I thought about doing it like that but I didn't have the HAVING condition worked out like that. Nice!

|||

Hi Chris,

I will try your solution and see what will be the output.

Thank you very much.

|||

Hi Chris,

Your example did return what I was expecting as projection. Thank you so much for the hint, you made my day.

Thanks again.

|||

Hi Kent,

I prefer Chris solution because you set up the count to be equal to 3. I tried your example and got something else because of the count. I really appreciate your input and thank you for helping me out. I can breath now, .

|||

Hi Mermaid
That's interesting. Do you have more than three distinct types in the Television table? You did specify only three in your initial post. Other than the hard-coding, both Kent's and my solutions are virtually identical.
I must admit that when I put my version together I was in two minds over whether or not to hard-code the number of distinct types into the HAVING clause.
Chris

|||

Here is another simpler and slightly better performing way to answer the question. I used the schema that Chris posted. The gist of the solution is that you get customers that do not have any television sets that are not accounted for in their invoices. This basically checks the inverse condition. This query will perform orders of magnitude faster than the GROUP BY approach due to lesser scans, no aggregation and simpler joins.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE NOT EXISTS(

SELECT *

FROM @.TELEVISION AS t
WHERE NOT EXISTS(
SELECT *
FROM @.INVOICE AS i
JOIN @.INV_LINE_ITEM AS ilt
ON i.CustomerIDFK = c.CustomerIDPK
WHERE ilt.InvoiceNbrFK = i.InvoiceNbrPK
and t.SerialNumberPK = ilt.SerialNumberFK
and ilt.Quantity > 0))

And you can actually simplify the GROUP BY query by doing below.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE EXISTS(
SELECT 1
FROM @.INVOICE i
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
And i.CustomerIDFK = c.CustomerIDPK
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2))

|||

Hi,

I try your first example the projection did not return anything. I think that the reverse is not working but your second example works the same way as Chris's. I noted both queries and thank you for your input. I do appreciate your help.

|||Could you please post the data / schema for which the first query doesn't work? That logic looks fine to me.|||

HI umachandar,

I worked on your example last night and the result is correct. Thanks for helping me out.

Association Scenario

Hi,

I have a product basket scenario in where I have to recommend contracts to a customer based on the product and the quantity he/she buys. Product Quantity is an important factor which administers the user in the purchase of a particular contract

I have the following tables with me.

Customer product transaction table, Customer Contract transaction tablebut there is no direct relationship between contract and product in the database. The only way the two can be linked is through the customer.

If I create a mining structure with Customer-Product information as the nested table and Customer-Contract information as the nested table with customer being the link between the two, the model is showing some irreverent contract recommendations.

what is the solution for the above problem? Is it because the is no direct relationship between the product and the contract?

How can I overcome this problem?

You are on the right track - you have the two nested tables that you need. What you want to do is to make the Product nested table as Input and the Contract nested table as Predict Only. Also, you may want to try excluding the product quantity to see how that impacts results.

Another important factor is how you set parameters for the AR algorithm. Setting the MINIMUM_SUPPORT and MINIMUM_PROBABILITY parameters are crucial - try lowering them until you find interesting results.

|||

Hi,

I tried the above changes but there was not much difference in the results. It still gives irrelevant recommendations.

Can you tell me how differentare the following two mining structures

1. Customertable => CASE Table

Customer – Product table => NESTED Table

Customer – Contract table => NESTED Table

2. Customer – Product table => CASE table

Customer – Contract table => NESTED Table

|||I'm not an expert in association but i think that in the first case

you're bound to have three kind of information : contract 1 /contract 2

OR product1 / product 2 OR contract / product (in fact it depends on

the key of your nested tables but in any case you'll get

contact/product association).
In the second case you won't have product1/product2 association. and the contract/ product association will have a support of 1

I don't really catch your problem but if you want to make the second mining structure, you will have to add customerID to your tables product and contract. With some sql queries, you should be able to prepare correctly your data|||

In the first structure it is possible for a single customer to have many products. In the second structure, each customer can only have one product.

Using the first model you can make Product INPUT and Contract PREDICT ONLY and you are asking the question the right way.

When you say it gives "irrelevant recommendations" what do you mean exactly? It could be that only the "irrelevant" ones are supported by the data, or you need to tweak the parameters a bit more.

If you aren't seeing what you want, use the same structure to create a decision tree model. This way you can look at the trees that are generated to see if there are meaningful patterns in the data. By default you will have 255 inputs (e.g. products) and 255 outputs (e.g. contracts), but that should be enough to get you started, you can adjust those parameters as necessary to refine the models.

Also, just for a question, how many customers, products, and contracts are there?

Thx

-Jamie

|||

Hi Jamie,

In the database there are totally around 800 customers, 300 products and 9 contracts.

These contracts are grouped logically in to long term, short term (depending on the product quantity) and some are applicable to certain products only.

Data in my database may look like the following

Customer - Product

Customer

Product

Quantity

Date

C1

P1

X

Jan 2005

C1

P1

Y

Feb 2005

C1

P2

X

March 2005

Customer – Contract

Customer

Contract

Date

C1

Contract1

June 2005 (For P1 bought in Jan 2005 as above)

C1

Contract2

March 2005 (For P1 bought in Feb 2005 as above)

C1

Contract3

March 2005 (For P2 bought)

So the relevant combinations would be

P1 – Quantity X – Contract1 (long term contract due to quantity X)

P1 – Quantity Y – Contract2 (short term contract due to quantity Y)

P2 – Quantity X – Contract 3 (Contract for the product type P3)

The other combinations are irrelevant.

These combinations or groupings are high level understanding and are not reflected at the technical level.

Some of the mining model recommendations are

1 . For the purchase of P1 of Quantity X apart from recommending Contract1 it also lists Contract2 and Contract3( irrelevant).

2. For P2 it recommends both Contract3 and Contract1 (irrelevant).

Assing rows to a table

I have a table where the columns are products and the
rows are customers. It stores sales info by customer and
product. I would like to add a row at the bottom af my
customer list that would be used for totaling each
column. Does anyone know how I can do this?
hi john,
if i understand you correctly you are looking for COMPUTE BY clause of
SELECT statement.
you can check the resultset of following query on northwind database.
use northwind
go
select b.customerid, a.unitprice
from [order details]a join orders b
on a.orderid =b.orderid
order by b.customerid
compute sum (a.unitprice ) by b.customerid
go
if this is not what you want pls post relevent table structure, sample
records, and expected result set out of it.Also specify whether you can
looking for in INSERT /UPDATE/SELECT statment.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Assign Sequential Numbers

I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp) documentation.

-PatP|||Hey...

SQLTeam still down?

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO

INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...

SQLTeam still down? Nah, at least I can see it from here.

-PatP