I forum Ricerca FAQ
Fai una domanda
Ultimo messaggio
grigon
grigon
il 3 dicembre 2009 alle 00:47
16 messaggi dal 11 settembre 2009
Salve a tutti. In calce riporto le tabelle di esempio. La procedura che ho scritto, spero correttamente, mi restituisce la composizione delle 5 ricette. La procedura si complica però quando voglio ricavare le quantità assolute delle caratteristiche univoche per ogni ricetta. Nel senso che, fino al punto che la ricetta è semplice 'forse c'arrivo alla soluzione' ma quando devo esplodere i prodotti in percentuale contenuti nel premix contenuto in percentuale nella ricetta contenuta a sua volta in percentuale nella ricetta iniziale, la cosa si complica assai!
Esiste un metodo già assodato per affrontare il problema. Personalmente mi vengono in mente solo soluzioni complicate quindi poco efficienti ed ineleganti.

Grazie a tutti. Giuseppe

Qui le tabelle:

SET NOCOUNT ON;
USE tempdb;
GO

if exists (select * from sysobjects where id = object_id('dbo.Prodotti') and sysstat & 0xf = 3)
drop table dbo.Prodotti
GO
if exists (select * from sysobjects where id = object_id('dbo.DettaglioProdotti') and sysstat & 0xf = 3)
drop table dbo.DettaglioProdotti
GO
if exists (select * from sysobjects where id = object_id('dbo.Caratteristiche') and sysstat & 0xf = 3)
drop table dbo.Caratteristiche
GO


CREATE TABLE dbo.Prodotti (
Id int NOT NULL IDENTITY (0, 1),
IdProdotto int NOT NULL,
AnagraficaProdotto nvarchar (50) NOT NULL,
b_Premix bit NOT NULL DEFAULT (0),
b_Componente bit NOT NULL DEFAULT (0));

CREATE TABLE dbo.DettaglioProdotti (
Id int NOT NULL IDENTITY (0, 1),
IdProdotto int NOT NULL,
IdDettaglioProdotto int NOT NULL,
Percento int NOT NULL);

CREATE TABLE dbo.Caratteristiche (
Id int NOT NULL IDENTITY (0, 1),
IdComponente int NOT NULL,
AnagraficaCaratteristica nvarchar (50) NOT NULL,
Valore int NOT NULL);


INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Componente) VALUES ( 101, 'PRODOTTO SEMPLICE', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Componente) VALUES ( 102, 'PRODOTTO CON PREMIX', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Componente) VALUES ( 103, 'PRODOTTO CON PRODOTTO', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Componente) VALUES ( 104, 'PRODOTTO CON PRODOTTO E PREMIX', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Componente) VALUES ( 105, 'PRODOTTO CON (PRODOTTO E PREMIX) E PREMIX', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Premix) VALUES ( 500, 'PREMIX 500', 1);
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto, b_Premix) VALUES ( 501, 'PREMIX 501', 1);
-- Componenti
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 1, 'Componente 1');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 2, 'Componente 2');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 3, 'Componente 3');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 4, 'Componente 4');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 5, 'Componente 5');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 6, 'Componente 6');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 7, 'Componente 7');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 8, 'Componente 8');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 9, 'Componente 9');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 10, 'Componente 10');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 11, 'Componente 11');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 12, 'Componente 12');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 13, 'Componente 13');
INSERT INTO dbo.Prodotti (IdProdotto, AnagraficaProdotto) VALUES ( 14, 'Componente 14');
-- Prodotto 101
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 1, 30);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 2, 25);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 3, 20);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 4, 15);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 5, 8);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 101, 6, 2);
-- Prodotto 102
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 102, 1, 40);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 102, 12, 30);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 102, 13, 15);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 102, 500, 10); -- PREMIX
-- Prodotto 103
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 103, 7, 50);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 103, 8, 20);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 103, 9, 15);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 103, 101, 10); -- PRODOTTO
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 103, 10, 5);
-- Prodotto 104
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 104, 1, 50);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 104, 10, 20);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 104, 11, 15);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 104, 101, 10); -- PRODOTTO
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 104, 500, 5); -- E PREMIX
-- Prodotto 105
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 105, 11, 50);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 105, 14, 20);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 105, 15, 15);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 105, 102, 10); --PRODOTTO E PREMIX
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 105, 501, 5); -- PREMIX
-- Premix 500
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 500, 12, 50);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 500, 13, 40);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 500, 14, 10);
-- Premix 501
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 501, 10, 40);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 501, 11, 30);
INSERT INTO dbo.DettaglioProdotti (IdProdotto, IdDettaglioProdotto, Percento) VALUES ( 501, 12, 30);
-- Caratteristiche x componente 1
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 1', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 2', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 4', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 5', 50);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 6', 60);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 7', 70);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 1, 'Caratteristica 8', 80);
-- Caratteristiche x componente 2
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 2, 'Caratteristica 2', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 2, 'Caratteristica 3', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 2, 'Caratteristica 9', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 2, 'Caratteristica 5', 40);
-- Caratteristiche x componente 3
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 3, 'Caratteristica 1', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 3, 'Caratteristica 2', 20);
-- Caratteristiche x componente 4
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 4, 'Caratteristica 4', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 4, 'Caratteristica 5', 50);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 4, 'Caratteristica 6', 60);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 4, 'Caratteristica 7', 70);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 4, 'Caratteristica 8', 80);
-- Caratteristiche x componente 5
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 5, 'Caratteristica 5', 50);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 5, 'Caratteristica 6', 60);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 5, 'Caratteristica 7', 70);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 5, 'Caratteristica 8', 80);
-- Caratteristiche x componente 6
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 6, 'Caratteristica 8', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 6, 'Caratteristica 7', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 6, 'Caratteristica 6', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 6, 'Caratteristica 5', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 6, 'Caratteristica 4', 50);
-- Caratteristiche x componente 7
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 7, 'Caratteristica 7', 70);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 7, 'Caratteristica 8', 80);
-- Caratteristiche x componente 8
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 8, 'Caratteristica 1', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 8, 'Caratteristica 2', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 8, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 8, 'Caratteristica 4', 40);
-- Caratteristiche x componente 9
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 9, 'Caratteristica 2', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 9, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 9, 'Caratteristica 4', 40);
-- Caratteristiche x componente 10
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 10, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 10, 'Caratteristica 4', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 10, 'Caratteristica 5', 50);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 10, 'Caratteristica 6', 60);
-- Caratteristiche x componente 11
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 11, 'Caratteristica 1', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 11, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 11, 'Caratteristica 5', 50);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 11, 'Caratteristica 7', 70);
-- Caratteristiche x componente 12
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 12, 'Caratteristica 2', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 12, 'Caratteristica 4', 40);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 12, 'Caratteristica 6', 60);
-- Caratteristiche x componente 13
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 13, 'Caratteristica 1', 10);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 13, 'Caratteristica 3', 30);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 13, 'Caratteristica 6', 60);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 13, 'Caratteristica 8', 80);
-- Caratteristiche x componente 14
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 14, 'Caratteristica 2', 20);
INSERT INTO dbo.Caratteristiche (IdComponente, AnagraficaCaratteristica, Valore) VALUES ( 14, 'Caratteristica 6', 60);
GO


CREATE PROCEDURE dbo.V_Recipe @Codice int
AS
SELECT P.IdProdotto, P.AnagraficaProdotto, DP.IdDettaglioProdotto, PP.AnagraficaProdotto, DP.Percento
FROM dbo.Prodotti P
INNER JOIN dbo.DettaglioProdotti DP ON DP.IdProdotto = P.IdProdotto
INNER JOIN dbo.Prodotti PP ON PP.IdProdotto = DP.IdDettaglioProdotto
WHERE P.IdProdotto = @Codice
ORDER BY DP.Percento
GO

exec V_Recipe 101
exec V_Recipe 102
exec V_Recipe 103
exec V_Recipe 104
exec V_Recipe 105
RispondiQuoting
Andrea Montanari
Andrea Montanari
il 3 dicembre 2009 alle 03:04
1.976 messaggi dal 27 luglio 2005
Contributi
salve,
non so se ho ben compreso il tuo problema..
ad ogni modo ti propongo una soluzione che si basa su una common table expression ricorsiva, in modo da esplodere i prodotti compositi nei loro singoli componenti..
ottenuto questo, con la sommatoria delle percentuali di composizione (e qui mi sorge il dubbio di non aver valutato correttamente il dato, ma comincia ad essere parecchio tardi e le cellule grigie sono in crisi ) ottengo la ricetta esplosa nei singoli componenti, e la percentuale la baso sulla sommatoria sopracento di tutti questi, similarmente a

DECLARE @Codice int = 104;
SELECT P.IdProdotto, P.AnagraficaProdotto, DP.IdDettaglioProdotto, PP.AnagraficaProdotto, DP.Percento
FROM dbo.Prodotti P
INNER JOIN dbo.DettaglioProdotti DP ON DP.IdProdotto = P.IdProdotto
INNER JOIN dbo.Prodotti PP ON PP.IdProdotto = DP.IdDettaglioProdotto
WHERE P.IdProdotto = @Codice
ORDER BY DP.Percento
GO

CREATE PROCEDURE dbo.usp_GetRecipe (
@Codice int
)
AS BEGIN
WITH cte AS (
SELECT dp.IdDettaglioProdotto, dp.Percento, p.b_Componente, p.b_Premix, p.AnagraficaProdotto
FROM dbo.Prodotti p
JOIN dbo.DettaglioProdotti dp ON p.IdProdotto = dp.IdProdotto
WHERE p.IdProdotto = @codice
UNION ALL
SELECT dp.IdDettaglioProdotto, dp.Percento, p.b_Componente, p.b_Premix, p.AnagraficaProdotto
FROM cte c
JOIN dbo.DettaglioProdotti dp ON dp.IdProdotto = c.IdDettaglioProdotto
JOIN dbo.Prodotti p ON p.IdProdotto = dp.IdDettaglioProdotto
WHERE p.b_Premix = 0 AND p.b_Componente = 0
),
cte2 AS (
SELECT IdDettaglioProdotto, SUM(Percento) AS [Percento]
FROM cte c
WHERE /*c.b_Componente = 0 AND */ c.b_Premix = 0
GROUP BY IdDettaglioProdotto
),
cte3 AS (
SELECT SUM(Percento) AS [Percento]
FROM cte2
)
SELECT p.IdProdotto, p.AnagraficaProdotto,
cte3.Percento [max percento ottenuto], cte2.Percento AS [Percentuale originale sommata],
(cte2.Percento * 100.0) / cte3.PercentoAS [Percentuale finale]
FROM cte2
JOIN dbo.Prodotti p ON p.IdProdotto = cte2.IdDettaglioProdotto
JOIN cte3 ON 1 = 1
WHERE p.b_Componente = 0 AND p.b_Premix = 0
ORDER BY (cte2.Percento * 100.0) / cte3.Percento;
END;
GO
EXEC dbo.usp_GetRecipe 101;
EXEC dbo.usp_GetRecipe 102;
EXEC dbo.usp_GetRecipe 103;
EXEC dbo.usp_GetRecipe 104;
EXEC dbo.usp_GetRecipe 105;
GO
DROP PROCEDURE dbo.usp_GetRecipe;

sicuramente il codice non sara' formattato correttamente e quindi molto illeggibile..

quindi proviamo per callout successivi:
WITH cte AS (
SELECT dp.IdDettaglioProdotto, dp.Percento, p.b_Componente, p.b_Premix, p.AnagraficaProdotto
FROM dbo.Prodotti p
JOIN dbo.DettaglioProdotti dp ON p.IdProdotto = dp.IdProdotto
WHERE p.IdProdotto = @codice
UNION ALL
SELECT dp.IdDettaglioProdotto, dp.Percento, p.b_Componente, p.b_Premix, p.AnagraficaProdotto
FROM cte c
JOIN dbo.DettaglioProdotti dp ON dp.IdProdotto = c.IdDettaglioProdotto
JOIN dbo.Prodotti p ON p.IdProdotto = dp.IdDettaglioProdotto
WHERE p.b_Premix = 0 AND p.b_Componente = 0
),

con questo ottengo tutti gli ingredienti ricorsivamente esplosi, compresi i composti stessi,,

cte2 AS (
SELECT IdDettaglioProdotto, SUM(Percento) AS [Percento]
FROM cte c
WHERE /*c.b_Componente = 0 AND */ c.b_Premix = 0
GROUP BY IdDettaglioProdotto
),
scremo i composti per avere i soli componenti base ed aggrego la percentuale di composizione..

cte3 AS (
SELECT SUM(Percento) AS [Percento]
FROM cte2
)
ottengo la percentuale sopracento totale..

SELECT p.IdProdotto, p.AnagraficaProdotto,
cte3.Percento [max percento ottenuto], cte2.Percento AS [Percentuale originale sommata],
(cte2.Percento * 100.0) / cte3.PercentoAS [Percentuale finale]
FROM cte2
JOIN dbo.Prodotti p ON p.IdProdotto = cte2.IdDettaglioProdotto
JOIN cte3 ON 1 = 1
WHERE p.b_Componente = 0 AND p.b_Premix = 0
ORDER BY (cte2.Percento * 100.0) / cte3.Percento;

proiezione finale con percentuale ricalcolata su base sopracento invece che cento..

ma ripeto, mi sa che la valutazione non sia corretta per il calcolo percentuale in quanto andrebbe ribasata per ogni composto.. ma ora vado a nanna , magari e' uno spunto di partenza per il ricalcolo..
saluti
Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
RispondiQuoting
Andrea Montanari
Andrea Montanari
il 3 dicembre 2009 alle 12:59
1.976 messaggi dal 27 luglio 2005
Contributi
salve,
sempre se ho compreso il tuo problema, e a mente lucida (per quanto la natura mi consente), direi che ho in effetti sbagliato l'aggregazione della percentuale componentistica..

CREATE PROCEDURE dbo.usp_GetRecipe (
@Codice int
)
AS BEGIN
WITH cte AS (
SELECT dp.IdDettaglioProdotto, CONVERT(decimal(12, , dp.Percento) AS [Percento], p.b_Componente, p.b_Premix, p.AnagraficaProdotto FROM dbo.Prodotti p
JOIN dbo.DettaglioProdotti dp ON p.IdProdotto = dp.IdProdotto WHERE p.IdProdotto = @codice
--AND (p.b_Componente = 1 OR p.b_Premix = 1)
UNION ALL
SELECT dp.IdDettaglioProdotto, CONVERT(decimal(12, , (dp.Percento * c.Percento) / 100.0) AS [Percento], p.b_Componente, p.b_Premix, p.AnagraficaProdotto
FROM cte c
JOIN dbo.DettaglioProdotti dp ON dp.IdProdotto = c.IdDettaglioProdotto JOIN dbo.Prodotti p ON p.IdProdotto = dp.IdDettaglioProdotto WHERE p.b_Premix = 0 AND p.b_Componente = 0
),
cte2 AS (
SELECT IdDettaglioProdotto, SUM(Percento) AS [Percento]
FROM cte c
WHERE /*c.b_Componente = 0 AND */ c.b_Premix = 0
GROUP BY IdDettaglioProdotto
)
SELECT p.IdProdotto, p.AnagraficaProdotto,
cte2.Percento
FROM cte2
JOIN dbo.Prodotti p ON p.IdProdotto = cte2.IdDettaglioProdotto WHERE p.b_Componente = 0 AND p.b_Premix = 0
ORDER BY cte2.Percento;
END;
GO
EXEC dbo.usp_GetRecipe 104;
GO
DROP PROCEDURE dbo.usp_GetRecipe ;
--<--------

IdProdotto AnagraficaProdotto Percento ---------
------------------------------------------------
-------------------------------------
6 Componente 6 0.20000000 14 Componente 14 0.50000000 5 Componente 5 0.80000000 4 Componente 4 1.50000000 3 Componente 3 2.00000000 13 Componente 13 2.00000000 12 Componente 12 2.50000000 2 Componente 2 2.50000000 11 Componente 11 15.00000000 10 Componente 10 20.00000000 1 Componente 1 53.00000000
questa invece dovrebbe essere corretta, dove la percentuale dei componenti viene risolta in relazione alla percentuale gerarchica del composto di appartenenza..
quindi ad ogni iterazione gerarchica, nel caso di composto, la relativa percentuale diventa la base di ricalcolo..
saluti
Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
RispondiQuoting

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.