Concat with Auto-increment column

I have a column with consist of customer number.e.g.
c001
c002
c003

How do I add the character "c" to the auto-incremental number everything I add?

You mean you want to add "c" to your identity column? Well, you cannot prefix an identity column because the column must be numeric - how is it going to increment if it's alpha.

However, you can create a compute column that does the prefixing for you.

e.g.

Code Snippet

create table t(pk int identity primary key, custid as 'c'+right(1000+pk,3));

insert t default values;

insert t default values;

insert t default values;

select * from t;

|||

You can try this :

--create a function in that you can make all the conact or incrementations you need

USE [test]

GO

CREATE FUNCTION [dbo].[ConcAuto](@.incr INT)

RETURNS varchar(11)

AS

BEGIN

DECLARE @.Result varchar(11)

SET @.Result = 'C'+cast(@.incr as varchar(10))

RETURN

(

@.Result

)

END

GO

--then create a column , col, as computed column

CREATE TABLE [dbo].[aa](

[id] [int] IDENTITY(1,1) NOT NULL,

[col] AS ([dbo].[ConcAuto]([id])),

[name] [nchar](10) NULL

) ON [secondary]

GO

Complex Price List

I need help with an odd request. I have been asked to create a view
price list that includes the price each customer will pay for any
product. ProductID, customerID, and price is all I really need. Our
rules for figuring out who pays what, is somewhat complex, and I would
prefer to avoid cursoring throught what will be millions of records if
possible. In order of presidence, if there is a specific customer
price we allways use that number. Here is what the tables look like:
Customers
CustomerID CustomerName PriceListID ...
27 Acme, Inc. 1
35 Bonzi, LLC 1
41 Chuck Corp. 2
Products
ProductID ProductName ...
10 Widget
11 Thingy
12 Watchamacallit
CustPrice
CustomerID ProductID Price
27 10 2.99
41 11 10.00
35 12 30.00
If that was the end of the story my view would match the CustPrice
table, but there is a pricelist that we use if there is no special
pricing for the customer. If the price list does include the productid
in question, we use the Retail price, if the Retail price also does not
include the product then set the price to $1000 because it is a special
negotable item. This is how the product pricing tables look:
PriceLists
PriceListID PriceListName
1 Retail
2 Preferred Customer
PriceListItems
PriceListID ProductID Price
1 10 7.99
1 11 15.00
2 10 6.99
I have added a discription to show where the price came from.What my
veiw should look like in the end is this:
CustomerID ProductID Price
27 10 2.99 --Customer price
27 11 15.00 --retail
pricelist
27 12 1,000.00 --Default price
35 10 7.99 --retail
pricelist
35 11 15.00 --retail
pricelist
35 12 30.00 --Customer price
41 10 7.99 --retail
pricelist
(used because there no
preferred pricelist for the item)
41 11 10.00 --Preferred
pricelist
41 12 1,000.00 --Default price
The big question is how do I correlate customers and products with
three different pricing systems'
Please Help!roger@.wwstar.com wrote:
> I need help with an odd request. I have been asked to create a view
> price list that includes the price each customer will pay for any
> product. ProductID, customerID, and price is all I really need. Our
> rules for figuring out who pays what, is somewhat complex, and I would
> prefer to avoid cursoring throught what will be millions of records if
> possible. In order of presidence, if there is a specific customer
> price we allways use that number. Here is what the tables look like:
> Customers
> CustomerID CustomerName PriceListID ...
> 27 Acme, Inc. 1
> 35 Bonzi, LLC 1
> 41 Chuck Corp. 2
> Products
> ProductID ProductName ...
> 10 Widget
> 11 Thingy
> 12 Watchamacallit
> CustPrice
> CustomerID ProductID Price
> 27 10 2.99
> 41 11 10.00
> 35 12 30.00
> If that was the end of the story my view would match the CustPrice
> table, but there is a pricelist that we use if there is no special
> pricing for the customer. If the price list does include the productid
> in question, we use the Retail price, if the Retail price also does not
> include the product then set the price to $1000 because it is a special
> negotable item. This is how the product pricing tables look:
> PriceLists
> PriceListID PriceListName
> 1 Retail
> 2 Preferred Customer
> PriceListItems
> PriceListID ProductID Price
> 1 10 7.99
> 1 11 15.00
> 2 10 6.99
> I have added a discription to show where the price came from.What my
> veiw should look like in the end is this:
> CustomerID ProductID Price
> 27 10 2.99 --Customer price
> 27 11 15.00 --retail
> pricelist
> 27 12 1,000.00 --Default price
> 35 10 7.99 --retail
> pricelist
> 35 11 15.00 --retail
> pricelist
> 35 12 30.00 --Customer price
> 41 10 7.99 --retail
> pricelist
> (used because there no
> preferred pricelist for the item)
> 41 11 10.00 --Preferred
> pricelist
> 41 12 1,000.00 --Default price
> The big question is how do I correlate customers and products with
> three different pricing systems'
> Please Help!
>
This can be done with a couple of UNIONs, but you're going to have to
produce valid CREATE TABLE and INSERT statements first.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> This can be done with a couple of UNIONs, but you're going to have to
> produce valid CREATE TABLE and INSERT statements first.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I see how to get the custprices into the query.
I can do a join between Customers and PriceListProducts to get the
field I need, but how do I tell it to omit customer/Proct combinations
that I already have?
Then I could look up the list prices for customer/products not in the
second set, how do I do that?
Then I need to fill in all the blanks, which is beyond the scope of my
knowledge.|||If you have millions of rows maybe a function is better:
create function dbo.GetPrice (
@.CustomerID int,
@.ProductID int
) returns double
begin
declare @.Price as double
select @.Price = cp.Price
from CustPrice cp with(nolock)
where cp.CustomerID = @.CustomerID
and cp.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
select @.Price = pli.Price
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where c.CustomerID = @.CustomerID
and pli.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
return 1000
end
go
This might be a little off because I'm not sure what you mean when you
said:
"If the price list does include the productid in question, we use the
Retail price "
If you still need a view then it would be like this:
select cp.CustomerID, cp.ProductID, cp.Price, 'cp' as Source
from CustPrice cp with(nolock)
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
union all
select c.CustomerID, p.ProductID, 1000, 'default'
from Customers c with(nolock),
Products p with(nolock)
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = p.ProductID)
and not exists
(select 1
from PriceListItems pli with (nolock)
where pli.PriceListID = c.PriceListID
and pli.ProductID = p.ProductID)
Indexes would be important with this code.
JJ|||Lets think this through. Since it appears you want every customer
against every product, I would start with a cross join between
Customers and Products
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
We need customer prices for all the rows returned by that. We will
use a LEFT OUTER JOIN so that we don't loose any of the above rows.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
That has all the rows we need prices for, with the highest priority
pricess (if any). But we also sometimes need prices from the default
PriceListItems. That calls for another LEFT OUTER JOIN. Note that it
is joining to Crossed.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
With that we should have what we need to build the SELECT list:
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
ORDER BY Crossed.CustomerID, Crossed.ProductID
At least I think so. Without tables and test data this is untested,
but I believe the approach is correct.
Roy Harvey
Beacon Falls, CT
On 9 Aug 2006 13:21:53 -0700, roger@.wwstar.com wrote:

>I need help with an odd request. I have been asked to create a view
>price list that includes the price each customer will pay for any
>product. ProductID, customerID, and price is all I really need. Our
>rules for figuring out who pays what, is somewhat complex, and I would
>prefer to avoid cursoring throught what will be millions of records if
>possible. In order of presidence, if there is a specific customer
>price we allways use that number. Here is what the tables look like:
>Customers
>CustomerID CustomerName PriceListID ...
>27 Acme, Inc. 1
>35 Bonzi, LLC 1
>41 Chuck Corp. 2
>Products
>ProductID ProductName ...
>10 Widget
>11 Thingy
>12 Watchamacallit
>CustPrice
>CustomerID ProductID Price
>27 10 2.99
>41 11 10.00
>35 12 30.00
>If that was the end of the story my view would match the CustPrice
>table, but there is a pricelist that we use if there is no special
>pricing for the customer. If the price list does include the productid
>in question, we use the Retail price, if the Retail price also does not
>include the product then set the price to $1000 because it is a special
>negotable item. This is how the product pricing tables look:
>PriceLists
>PriceListID PriceListName
>1 Retail
>2 Preferred Customer
>PriceListItems
>PriceListID ProductID Price
>1 10 7.99
>1 11 15.00
>2 10 6.99
>I have added a discription to show where the price came from.What my
>veiw should look like in the end is this:
>CustomerID ProductID Price
>27 10 2.99 --Customer price
>27 11 15.00 --retail
>pricelist
>27 12 1,000.00 --Default price
>35 10 7.99 --retail
>pricelist
>35 11 15.00 --retail
>pricelist
>35 12 30.00 --Customer price
>41 10 7.99 --retail
>pricelist
> (used because there no
>preferred pricelist for the item)
>41 11 10.00 --Preferred
>pricelist
>41 12 1,000.00 --Default price
>The big question is how do I correlate customers and products with
>three different pricing systems'
>Please Help!|||JJ,
That did great! Thanks!
There is the one part that I didn't explain properly that I still need
to figure out.
I wrote:

> "If the price list does include the productid in question, we use the
> Retail price "
I should have wrote:
"If the price list does NOT include the productid in question, we use
the
Retail price "
Meaning, if Bob is to use the preferred customer price list, but he
buys a "thingy" which is not on the preferred customer pricelist
(PriceListID=2), the price Bob will be charged is the retail price
(PriceListID=1).
Thanks for your help,
Roger|||if the rule is that the customer allways gets the lower price of any
that are available to him, then the code should be:
--Replace the middle "union all" block with:
union all
select c.CustomerID, pli.ProductID, min(pli.Price) as Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
group by c.CustomerID, pli.ProductID
If the customer would get the "preferred price" even if it was higher
then
--need to replace the middle "union all" block with this block:
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
inner join (select m.ProductID, max(m.PriceListID) maxPriceListID
from PriceListItems m with(nolock)
group by m.ProductID) as m
on c.PriceListID = m.MaxPriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
the first one will perform better if that is the rule. It happens we
can get away with "max(m.PriceListID)" because this is a simple
example, but usually you would add a column in the PriceList table like
"Priority" and then join to the PriceList table and take a max of
that.
I still like the function idea better especially if you are only
usually pulling back a list once you have the CustomerID already
select. It is easier to maintain.
You can still use it in a select
select CustomerID, ProductID, dbo.GetPrice(CustomerID, ProductID) as
price
from ...
where CustomerID = @.CustomerID
Put me on the preferred customer list.
JJ|||On 9 Aug 2006 15:49:01 -0700, roger@.wwstar.com wrote:

>There is the one part that I didn't explain properly that I still need
>to figure out.
>I wrote:
>
>I should have wrote:
>"If the price list does NOT include the productid in question, we use
>the
>Retail price "
>Meaning, if Bob is to use the preferred customer price list, but he
>buys a "thingy" which is not on the preferred customer pricelist
>(PriceListID=2), the price Bob will be charged is the retail price
>(PriceListID=1).
OK so we have to make some more changes. We will do yet another outer
join to pick up PriceListID=1. And the COALESCE will have the result
from that query inserted before the ultimate default.
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, PL1.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
LEFT OUTER
JOIN PriceListItems as PL1
ON Crossed.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
ORDER BY Crossed.CustomerID, Crossed.ProductID
There is another approach that might be worth considering. Start with
the idea that price list 1 and price list 2 are universal, so we can
join to them directly from the Products table BEFORE the cross join.
We would then have to reference them conditionally in the results.
Since the query is getting kind of hairy, I am going to use a view for
readability sake.
CREATE VIEW ProductsStdPrices
AS
SELECT P.*,
PL1.Price as PL1_Price,
PL2.Price as PL2_Price
FROM Products
LEFT OUTER
JOIN PriceListItems as PL1
ON P.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
LEFT OUTER
JOIN PriceListItems as PL2
ON P.ProductID = PL2.ProductID
AND PL2.PriceListID = 2
GO
Now we substitute the view for the Products table in the cross join,
chop off the last two outer joins, and change the COALESCE. The
change to the COALESCE would have required at least one CASE
expression put inside it, so I opted to replace the entire thing with
a CASE.
SELECT Crossed.CustomerID, Crossed.ProductID,
CASE WHEN CP.Price IS NOT NULL
THEN CP.Price
WHEN Crossed.PriceListID = 2
AND Crossed.PL2_Price IS NOT NULL
THEN Crossed.PL2_Price
WHEN Crossed.PL1_Price IS NOT NULL
THEN Crossed.PL1_Price
ELSE 1000.00
END
FROM (SELECT * FROM Customers
CROSS JOIN ProductsStdPrices) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
ORDER BY Crossed.CustomerID, Crossed.ProductID
Which again, I think will work, but is untested.
Roy Harvey
Beacon Falls, CT

Complex Price List

I need help with an odd request. I have been asked to create a view
price list that includes the price each customer will pay for any
product. ProductID, customerID, and price is all I really need. Our
rules for figuring out who pays what, is somewhat complex, and I would
prefer to avoid cursoring throught what will be millions of records if
possible. In order of presidence, if there is a specific customer
price we allways use that number. Here is what the tables look like:
Customers
CustomerID CustomerName PriceListID ...
27 Acme, Inc. 1
35 Bonzi, LLC 1
41 Chuck Corp. 2
Products
ProductID ProductName ...
10 Widget
11 Thingy
12 Watchamacallit
CustPrice
CustomerID ProductID Price
27 10 2.99
41 11 10.00
35 12 30.00
If that was the end of the story my view would match the CustPrice
table, but there is a pricelist that we use if there is no special
pricing for the customer. If the price list does include the productid
in question, we use the Retail price, if the Retail price also does not
include the product then set the price to $1000 because it is a special
negotable item. This is how the product pricing tables look:
PriceLists
PriceListID PriceListName
1 Retail
2 Preferred Customer
PriceListItems
PriceListID ProductID Price
1 10 7.99
1 11 15.00
2 10 6.99
I have added a discription to show where the price came from.What my
veiw should look like in the end is this:
CustomerID ProductID Price
27 10 2.99 --Customer price
27 11 15.00 --retail
pricelist
27 12 1,000.00 --Default price
35 10 7.99 --retail
pricelist
35 11 15.00 --retail
pricelist
35 12 30.00 --Customer price
41 10 7.99 --retail
pricelist
(used because there no
preferred pricelist for the item)
41 11 10.00 --Preferred
pricelist
41 12 1,000.00 --Default price
The big question is how do I correlate customers and products with
three different pricing systems'
Please Help!roger@.wwstar.com wrote:
> I need help with an odd request. I have been asked to create a view
> price list that includes the price each customer will pay for any
> product. ProductID, customerID, and price is all I really need. Our
> rules for figuring out who pays what, is somewhat complex, and I would
> prefer to avoid cursoring throught what will be millions of records if
> possible. In order of presidence, if there is a specific customer
> price we allways use that number. Here is what the tables look like:
> Customers
> CustomerID CustomerName PriceListID ...
> 27 Acme, Inc. 1
> 35 Bonzi, LLC 1
> 41 Chuck Corp. 2
> Products
> ProductID ProductName ...
> 10 Widget
> 11 Thingy
> 12 Watchamacallit
> CustPrice
> CustomerID ProductID Price
> 27 10 2.99
> 41 11 10.00
> 35 12 30.00
> If that was the end of the story my view would match the CustPrice
> table, but there is a pricelist that we use if there is no special
> pricing for the customer. If the price list does include the productid
> in question, we use the Retail price, if the Retail price also does not
> include the product then set the price to $1000 because it is a special
> negotable item. This is how the product pricing tables look:
> PriceLists
> PriceListID PriceListName
> 1 Retail
> 2 Preferred Customer
> PriceListItems
> PriceListID ProductID Price
> 1 10 7.99
> 1 11 15.00
> 2 10 6.99
> I have added a discription to show where the price came from.What my
> veiw should look like in the end is this:
> CustomerID ProductID Price
> 27 10 2.99 --Customer price
> 27 11 15.00 --retail
> pricelist
> 27 12 1,000.00 --Default price
> 35 10 7.99 --retail
> pricelist
> 35 11 15.00 --retail
> pricelist
> 35 12 30.00 --Customer price
> 41 10 7.99 --retail
> pricelist
> (used because there no
> preferred pricelist for the item)
> 41 11 10.00 --Preferred
> pricelist
> 41 12 1,000.00 --Default price
> The big question is how do I correlate customers and products with
> three different pricing systems'
> Please Help!
>
This can be done with a couple of UNIONs, but you're going to have to
produce valid CREATE TABLE and INSERT statements first.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> This can be done with a couple of UNIONs, but you're going to have to
> produce valid CREATE TABLE and INSERT statements first.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I see how to get the custprices into the query.
I can do a join between Customers and PriceListProducts to get the
field I need, but how do I tell it to omit customer/Proct combinations
that I already have?
Then I could look up the list prices for customer/products not in the
second set, how do I do that?
Then I need to fill in all the blanks, which is beyond the scope of my
knowledge.|||If you have millions of rows maybe a function is better:
create function dbo.GetPrice (
@.CustomerID int,
@.ProductID int
) returns double
begin
declare @.Price as double
select @.Price = cp.Price
from CustPrice cp with(nolock)
where cp.CustomerID = @.CustomerID
and cp.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
select @.Price = pli.Price
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where c.CustomerID = @.CustomerID
and pli.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
return 1000
end
go
This might be a little off because I'm not sure what you mean when you
said:
"If the price list does include the productid in question, we use the
Retail price "
If you still need a view then it would be like this:
select cp.CustomerID, cp.ProductID, cp.Price, 'cp' as Source
from CustPrice cp with(nolock)
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
union all
select c.CustomerID, p.ProductID, 1000, 'default'
from Customers c with(nolock),
Products p with(nolock)
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = p.ProductID)
and not exists
(select 1
from PriceListItems pli with (nolock)
where pli.PriceListID = c.PriceListID
and pli.ProductID = p.ProductID)
Indexes would be important with this code.
JJ|||Lets think this through. Since it appears you want every customer
against every product, I would start with a cross join between
Customers and Products
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
We need customer prices for all the rows returned by that. We will
use a LEFT OUTER JOIN so that we don't loose any of the above rows.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
That has all the rows we need prices for, with the highest priority
pricess (if any). But we also sometimes need prices from the default
PriceListItems. That calls for another LEFT OUTER JOIN. Note that it
is joining to Crossed.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
With that we should have what we need to build the SELECT list:
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
ORDER BY Crossed.CustomerID, Crossed.ProductID
At least I think so. Without tables and test data this is untested,
but I believe the approach is correct.
Roy Harvey
Beacon Falls, CT
On 9 Aug 2006 13:21:53 -0700, roger@.wwstar.com wrote:
>I need help with an odd request. I have been asked to create a view
>price list that includes the price each customer will pay for any
>product. ProductID, customerID, and price is all I really need. Our
>rules for figuring out who pays what, is somewhat complex, and I would
>prefer to avoid cursoring throught what will be millions of records if
>possible. In order of presidence, if there is a specific customer
>price we allways use that number. Here is what the tables look like:
>Customers
>CustomerID CustomerName PriceListID ...
>27 Acme, Inc. 1
>35 Bonzi, LLC 1
>41 Chuck Corp. 2
>Products
>ProductID ProductName ...
>10 Widget
>11 Thingy
>12 Watchamacallit
>CustPrice
>CustomerID ProductID Price
>27 10 2.99
>41 11 10.00
>35 12 30.00
>If that was the end of the story my view would match the CustPrice
>table, but there is a pricelist that we use if there is no special
>pricing for the customer. If the price list does include the productid
>in question, we use the Retail price, if the Retail price also does not
>include the product then set the price to $1000 because it is a special
>negotable item. This is how the product pricing tables look:
>PriceLists
>PriceListID PriceListName
>1 Retail
>2 Preferred Customer
>PriceListItems
>PriceListID ProductID Price
>1 10 7.99
>1 11 15.00
>2 10 6.99
>I have added a discription to show where the price came from.What my
>veiw should look like in the end is this:
>CustomerID ProductID Price
>27 10 2.99 --Customer price
>27 11 15.00 --retail
>pricelist
>27 12 1,000.00 --Default price
>35 10 7.99 --retail
>pricelist
>35 11 15.00 --retail
>pricelist
>35 12 30.00 --Customer price
>41 10 7.99 --retail
>pricelist
> (used because there no
>preferred pricelist for the item)
>41 11 10.00 --Preferred
>pricelist
>41 12 1,000.00 --Default price
>The big question is how do I correlate customers and products with
>three different pricing systems'
>Please Help!|||JJ,
That did great! Thanks!
There is the one part that I didn't explain properly that I still need
to figure out.
I wrote:
> "If the price list does include the productid in question, we use the
> Retail price "
I should have wrote:
"If the price list does NOT include the productid in question, we use
the
Retail price "
Meaning, if Bob is to use the preferred customer price list, but he
buys a "thingy" which is not on the preferred customer pricelist
(PriceListID=2), the price Bob will be charged is the retail price
(PriceListID=1).
Thanks for your help,
Roger|||if the rule is that the customer allways gets the lower price of any
that are available to him, then the code should be:
--Replace the middle "union all" block with:
union all
select c.CustomerID, pli.ProductID, min(pli.Price) as Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
group by c.CustomerID, pli.ProductID
If the customer would get the "preferred price" even if it was higher
then
--need to replace the middle "union all" block with this block:
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
inner join (select m.ProductID, max(m.PriceListID) maxPriceListID
from PriceListItems m with(nolock)
group by m.ProductID) as m
on c.PriceListID = m.MaxPriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
the first one will perform better if that is the rule. It happens we
can get away with "max(m.PriceListID)" because this is a simple
example, but usually you would add a column in the PriceList table like
"Priority" and then join to the PriceList table and take a max of
that.
I still like the function idea better especially if you are only
usually pulling back a list once you have the CustomerID already
select. It is easier to maintain.
You can still use it in a select
select CustomerID, ProductID, dbo.GetPrice(CustomerID, ProductID) as
price
from ...
where CustomerID = @.CustomerID
Put me on the preferred customer list.
JJ|||On 9 Aug 2006 15:49:01 -0700, roger@.wwstar.com wrote:
>There is the one part that I didn't explain properly that I still need
>to figure out.
>I wrote:
>> "If the price list does include the productid in question, we use the
>> Retail price "
>I should have wrote:
>"If the price list does NOT include the productid in question, we use
>the
>Retail price "
>Meaning, if Bob is to use the preferred customer price list, but he
>buys a "thingy" which is not on the preferred customer pricelist
>(PriceListID=2), the price Bob will be charged is the retail price
>(PriceListID=1).
OK so we have to make some more changes. We will do yet another outer
join to pick up PriceListID=1. And the COALESCE will have the result
from that query inserted before the ultimate default.
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, PL1.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
LEFT OUTER
JOIN PriceListItems as PL1
ON Crossed.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
ORDER BY Crossed.CustomerID, Crossed.ProductID
There is another approach that might be worth considering. Start with
the idea that price list 1 and price list 2 are universal, so we can
join to them directly from the Products table BEFORE the cross join.
We would then have to reference them conditionally in the results.
Since the query is getting kind of hairy, I am going to use a view for
readability sake.
CREATE VIEW ProductsStdPrices
AS
SELECT P.*,
PL1.Price as PL1_Price,
PL2.Price as PL2_Price
FROM Products
LEFT OUTER
JOIN PriceListItems as PL1
ON P.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
LEFT OUTER
JOIN PriceListItems as PL2
ON P.ProductID = PL2.ProductID
AND PL2.PriceListID = 2
GO
Now we substitute the view for the Products table in the cross join,
chop off the last two outer joins, and change the COALESCE. The
change to the COALESCE would have required at least one CASE
expression put inside it, so I opted to replace the entire thing with
a CASE.
SELECT Crossed.CustomerID, Crossed.ProductID,
CASE WHEN CP.Price IS NOT NULL
THEN CP.Price
WHEN Crossed.PriceListID = 2
AND Crossed.PL2_Price IS NOT NULL
THEN Crossed.PL2_Price
WHEN Crossed.PL1_Price IS NOT NULL
THEN Crossed.PL1_Price
ELSE 1000.00
END
FROM (SELECT * FROM Customers
CROSS JOIN ProductsStdPrices) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
ORDER BY Crossed.CustomerID, Crossed.ProductID
Which again, I think will work, but is untested.
Roy Harvey
Beacon Falls, CT

Complex Join, I think.

I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.

customer
id | name
1 | fred
2 | tom
3 | eric
4 | fred

custrate
cust_id | rate_id
1 | 120
1 | 200
2 | 130
2 | 300
3 | 120
4 | 130

I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
inner
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
case when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null|||select * from customer c
where not exists
(select 1 from custrate r
where c.id=r.cust_id
group by r.cust_id
having count(*)%2=0 -- Must be paired
)
PS. There is something amiss with Rudy's query|||PS. There is something amiss with Rudy's queryindeed there was, 2 things amiss

try it now --select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
left outer
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null:)|||4:30?

As in AM?

you just getting in?|||Rudy has no need for sleep.
Sleep is inefficient.

:p|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)

You must have a heck of a time during the summer when it doesn't get dark until after 10:00 PM ... or is it about 10:30 in your area?|||indeed, sometimes i get sleepy well before it gets dark!