Visa antalet artiklar som en viss leverantör är billigast på

hur ser kopplingen mellan produkt, leverantör och pris ut?

Hej!

Min databasdesign ser ut så här:

http://www.webforum.nu/showthread.php?p=1182604#post1182604

Mvh
Henrik

Något sånt här du menar?

Notera dock att antalet produkter som är "billigast" kan vara fler än antalet produkter totalt sett. (Du ser exempel på det i min kod). Detta beror på att flera leverantörer kan ha samma pris på en produkt och då räknas båda som att de är billigast. Här kan du naturligtvis välja att göra på annat sätt, exempelivis inte räkna med att någon är billigast.

declare @Article table (AtricleNumber int, Name varchar(50))
insert into @Article
	select 1, 'artikel 1'
	union all 
	select 2, 'artikel 2'
	union all 
	select 3, 'artikel 3'
	union all 
	select 4, 'artikel 4'
	union all 
	select 4, 'artikel 5'

declare @Supplier table (SupplierId int, Name varchar(50))
insert into @Supplier
	select 1, 'Storel'
	union all 
	select 2, 'Solar'

declare @Price table (ArticleNumber int, SupplierId int, Price int)
insert into @Price
	select 1, 1, 100
	union all 
	select 2, 1, 500
	union all 
	select 1, 2, 200
	union all 
	select 2, 2, 800
	union all 
	select 3, 1, 1000
	union all 
	select 4, 2, 1000
	union all 
	select 5, 1, 1000
	union all 
	select 5, 2, 1000

select 
	count(*) NumberOfArticlesWithBestPrice, 
	SupplierId from @price t1 
where 
	price = (select min(price) from @price t2 where t1.articlenumber = t2.articlenumber) 
group by 
	supplierId

Hej! :)

Jag kanske har missat något, det är inte helt omöjligt... Har aldrig gjort så här avancerade saker med SQL innan...

Men resultatet blir enligt bifogad bild. Tycker inte det blir helt rätt...

Fört att lista alla artiklar ser min sql-fråga ut så här:

SELECT tblArticle.articlenumber, 
           tblArticle.articleName, 
           tblPrice.articlePrice, 
           tblPrice.articleCategoryNumber, 
           tblArticle.articleUnit,
           tblCategory.CategoryName, 
           tblCategory.categoryNumber, 
           tblDiscount.articleDiscount, 
           tblSupplier.SupplierName, 
           tblSupplier.SupplierId, 
CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END 
          AS articlePriceWithDiscount 
FROM tblArticle INNER JOIN 
	tblPrice ON tblArticle.articlenumber = tblPrice.articleNumber INNER JOIN 
	tblSupplier ON tblPrice.articleSupplierId = tblSupplier.SupplierId LEFT OUTER JOIN 
	tblDiscount ON tblPrice.articleCategoryNumber = tblDiscount.categoryNumber AND 
	tblPrice.articleSupplierId = tblDiscount.supplierId LEFT OUTER JOIN 
	tblCategory ON tblPrice.articleSupplierId = tblCategory.supplierId AND 
	tblPrice.articleCategoryNumber = tblCategory.categoryNumber 
ORDER BY articlePriceWithDiscount

Mvh
Henrik

Varför tycker du inte att det blir rätt?
Du har supplierId och kan därmed joina mot tabellen supplier om du vill få fram namnet på leverantören.

Storel är billigast på 156 000 artiklar
Solar är biligast på 58 000 artiklar
...

Här tycker jag att databasen skall leverera två saker.. Namnet på leverantören och antalet artiklar som den leverantören är billigast på, och det gör frågan.

Såhär skulle det kunna se ut om man joinar mot supplier samt sorterar efter den som är billigast på flest artiklar

select 
	s.Name,
	count(*) NumberOfArticlesWithBestPrice
from 
	@price p 
	inner join @Supplier s on p.supplierId = s.supplierId
where 
	price = (select min(price) from @price where p.articlenumber = articlenumber) 
group by 
	s.Name
order by 
	count(*) desc

Hej! :)

Jo, det blir ju rätt. Men det är lite för få artiklar tycker jag... Fattas det inte något här?

Är inte de värdena du skrivit "hårdkodade".. Du får ha överseende, men jag är inte så bra på detta.. :)

Med den sista biten kod kom ju leverantörs namnet med....(y)

Mvh
Henrik

Ok. Det du får göra är att byta ut mina tabellnamn mot de som du har i din databas.

Det borde troligtvis bli ungefär såhär.

select 
	s.Name,
	count(*) NumberOfArticlesWithBestPrice
from 
	tblPrice p 
	inner join tblSupplier s on p.articleSupplierId = s.supplierId
where 
	price = (select min(price) from tblPrice where p.articlenumber = articlenumber) 
group by 
	s.Name
order by 
	count(*) desc

testade att att provköra, men fick följande fel..

Msg 207, Level 16, State 1, Line 45
Ogiltigt kolumnnamn, 'price'.
Msg 207, Level 16, State 1, Line 45
Ogiltigt kolumnnamn, 'price'.
Msg 207, Level 16, State 1, Line 47
Ogiltigt kolumnnamn, 'Name'.
Msg 207, Level 16, State 1, Line 39
Ogiltigt kolumnnamn, 'Name'.

Mvh
Henrik

Brimba skrev:

Ok. Det du får göra är att byta ut mina tabellnamn mot de som du har i din databas.

Du har inte vad jag kan se något tabellnamn/fält som heter price eller Name? Utan i ditt fall heter de väl articlePrice och supplierName?

Som sagt, jag är inte så bra på så avancerade SQL-frågor...

Men nu testade jag följande:

select 
	s.supplierName,
	count(*) NumberOfArticlesWithBestPrice
from 
	tblPrice p 
	inner join tblSupplier s on p.articleSupplierId = s.supplierId
where 
	articlePrice = (select min(articlePrice) from tblPrice where p.articlenumber = articlenumber) 
group by 
	s.supplierName
order by 
	count(*) desc

och det verkar fungera... se bild...

Men om jag vill ha med pris med rabatt, enligt min SQL-sats, hur gör jag då?

Mvh
Henrik

Men det påverkar väl inte slutresultatet eftersom rabatten ligger på artikeln och inte på leverantören?

Men det borde bli något i stil med detta:

select 
	s.supplierName,
	count(*) NumberOfArticlesWithBestPrice
from 
	tblPrice p 
	inner join tblSupplier s on p.articleSupplierId = s.supplierId
where 
	CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END = (select min(CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END) from tblPrice where p.articlenumber = articlenumber) 
group by 
	s.supplierName
order by 
	count(*) desc

Ok...

rabatten ligger i tblDiscount

Men måste inte tblDiscount joinas också?

Mvh
Henrik

Men måste inte tblDiscount joinas också?

Jo, den måste in i joinen. Men det fixar du. :)

okidoki

:)

Nähä.. som vanligt... :(

På något sätt måste väl denna raden in i koden.. men, jag får inte till det...

LEFT OUTER JOIN 
	tblDiscount ON tblPrice.articleCategoryNumber = tblDiscount.categoryNumber AND 
	tblPrice.articleSupplierId = tblDiscount.supplierId

hela sql-frågan för att visa alla artiklar ser ut så här:

SELECT tblArticle.articlenumber, 
           tblArticle.articleName, 
           tblPrice.articlePrice, 
           tblPrice.articleCategoryNumber, 
           tblArticle.articleUnit,
           tblCategory.CategoryName, 
           tblCategory.categoryNumber, 
           tblDiscount.articleDiscount, 
           tblSupplier.SupplierName, 
           tblSupplier.SupplierId, 
CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END 
          AS articlePriceWithDiscount 
FROM tblArticle INNER JOIN 
	tblPrice ON tblArticle.articlenumber = tblPrice.articleNumber INNER JOIN 
	tblSupplier ON tblPrice.articleSupplierId = tblSupplier.SupplierId LEFT OUTER JOIN 
	tblDiscount ON tblPrice.articleCategoryNumber = tblDiscount.categoryNumber AND 
	tblPrice.articleSupplierId = tblDiscount.supplierId LEFT OUTER JOIN 
	tblCategory ON tblPrice.articleSupplierId = tblCategory.supplierId AND 
	tblPrice.articleCategoryNumber = tblCategory.categoryNumber 
ORDER BY articlePriceWithDiscount

Mvh Henrik

Prova detta:

select 
	s.supplierName,
	count(*) NumberOfArticlesWithBestPrice
from 
	tblPrice p 
	inner join tblSupplier s on p.articleSupplierId = s.supplierId
	left join tblDiscount d ON p.articleCategoryNumber = d.categoryNumber AND p.articleSupplierId = d.supplierId
where 
	CASE WHEN articleDiscount is null THEN 
          p.articlePrice ELSE 
          p.articlePrice*(1-([d.articleDiscount]*0.01)) END = (select min(CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END) from tblPrice left join tblDiscount ON tblPrice.articleCategoryNumber = tblDiscount.categoryNumber AND tblPrice.articleSupplierId = tblDiscount.supplierId where tblPrice.articlenumber = articlenumber) 
group by 
	s.supplierName
order by 
	count(*) desc

Hej Brimba!

Tack för att du tar dig tid...

Men nu fick jag detta:

Msg 207, Level 16, State 1, Line 11
Ogiltigt kolumnnamn, 'd.articleDiscount'.

Mvh
Henrik

Sa du inte att articleDiscount låg i tblDiscount?

Hur som helst, prova detta:

select 
	s.supplierName,
	count(*) NumberOfArticlesWithBestPrice
from 
	tblPrice p 
	inner join tblSupplier s on p.articleSupplierId = s.supplierId
	left join tblDiscount d ON p.articleCategoryNumber = d.categoryNumber AND p.articleSupplierId = d.supplierId
where 
	CASE WHEN articleDiscount is null THEN 
          p.articlePrice ELSE 
          p.articlePrice*(1-([articleDiscount]*0.01)) END = (select min(CASE WHEN articleDiscount is null THEN 
          articlePrice ELSE 
          articlePrice*(1-([articleDiscount]*0.01)) END) from tblPrice left join tblDiscount ON tblPrice.articleCategoryNumber = tblDiscount.categoryNumber AND tblPrice.articleSupplierId = tblDiscount.supplierId where tblPrice.articlenumber = articlenumber) 
group by 
	s.supplierName
order by 
	count(*) desc

:)

Det fungerade ju bättre.. :)

Men hur "vet man" att den räknar rätt?

Jag tycker det blir lite lite, med tanke på att databasen innehåller nästan 400 000 artiklar?

sen är ju 2 av leverantörerna inte med... Någon leverantör måste ju vara billagast på något..

Bifogar bild på resultatet

Jämför med:
http://www.webforum.nu/attachment.php?attachmentid=10966
Där det inte tas hänsyn till rabatten...

Mvh
Henrik