Cześć ! Jestem początkującą analityczką sprzedaży.
Mam problem z prawidłowym przedstawieniem pewnej sumy.
Widok jaki próbuje przedstawić to przedstawienie historii kontraktu produktu A w połączeniu z innymi produktami ( czyli czy klient miał zakupiony przed Produktem A produkty (B, C, D, E, F), jakie produkty dokupił w trakcie od podpisania umowy do 17 miesiąca trwania umowy oraz między 17 miesiącem trwania umowy do przedłużenia tego kontraktu.
Mój problem polega na tym, że myślałam, że left join dla każdego produktu rozwiąże problem rozdzielenia danego produktów, ale w np. jeżeli w pierwszym case'ie w ograniczonej dacie dla konkretnego customer_ID znajduje się produkt B oraz D, to case nie przedstawia ilości produktu B, ale sumuje B oraz D.
Wstawiam niżej uproszony kod. Już brakuje mi pomysłów, co mogłabym zmienić, aby ta suma została przedstawiona poprawnie.
Z góry dziękuje za pomoc !
Korzystam z sql oracle.
Select
k1.customer_ID,
t1.data_od,
t2.data_do,
SUM(case when k1.customer_ID = k2.customer_ID and k2.data_od <k1.data_od then k2.B
else 0 end) as beforeB,
SUM(case when k1.customer_ID = k3.customer_ID and k3.data_od <k1.data_od then k3.C
else 0 end) as beforeC,
SUM(case when k1.customer_ID = k4.customer_ID and k4.data_od <k1.data_od then k4.D
else 0 end) as beforeD,
SUM(case when k1.customer_ID = k5.customer_ID and k5.data_od <k1.data_od then k5.E
else 0 end) as beforeE,
SUM(case when k1.customer_ID = k6.customer_ID and k6.data_od <k1.data_od then k6.F
else 0 end) as beforeF,
SUM(case when k1.customer_ID = k2.customer_ID and k2.data_od BETWEEN k1.data_od AND k1.data17 then k2.B
else 0 end) as afterB,
SUM(case when k1.customer_ID = k3.customer_ID and k3.data_od BETWEEN k1.data_od AND k1.data17 then k3.C
else 0 end) as afterC,
SUM(case when k1.customer_ID = k4.customer_ID and k4.data_od BETWEEN k1.data_od AND k1.data17 then k4.D
else 0 end) as afterD,
SUM(case when k1.customer_ID = k5.customer_ID and k5.data_od BETWEEN k1.data_od AND k1.data17 then k5.E
else 0 end) as afterE,
SUM(case when k1.customer_ID = k6.customer_ID and k6.data_od BETWEEN k1.data_od AND k1.data17 then k6.F
else 0 end) as afterF,
SUM(case when k1.customer_ID = k2.customer_ID and k2.data_od BETWEEN k1.data17 AND k1.data_do then k2.B
else 0 end) as after1B,
SUM(case when k1.customer_ID = k3.customer_ID and k3.data_od BETWEEN k1.data17 AND k1.data_do then k3.C
else 0 end) as after1C,
SUM(case when k1.customer_ID = k4.customer_ID and k4.data_od BETWEEN k1.data17 AND k1.data_do then k4.D
else 0 end) as after1D,
SUM(case when k1.customer_ID = k5.customer_ID and k5.data_od BETWEEN k1.data17 AND k1.data_do then k5.E
else 0 end) as after1E,
SUM(case when k1.customer_ID = k6.customer_ID and k6.data_od BETWEEN k1.data17 AND k1.data_do then k6.F
else 0 end) as after1F
FROM
(select
COUNT(customer_ID),
customer_ID,
data_od,
data_do,
ADD_MONTHS(t1.data_od, 17) as data17
FROM tabela1
WHERE nazwa_produktu = 'A'
GROUP BY
customer_ID,
data_od,
data_do,
ADD_MONTHS(t1.data_od, 17)
) k1
LEFT JOIN
(select
COUNT(produkt_ID),
customer_ID,
data_od,
data_do
FROM tabela1
WHERE nazwa_produktu = 'B'
GROUP BY
customer_ID,
data_od,
data_do
) k2
ON k1.customer_ID = k2.customer_ID
LEFT JOIN
(select
COUNT(produkt_ID),
customer_ID,
data_od,
data_do,
customer_ID,
FROM tabela1
WHERE nazwa_produktu = 'C'
GROUP BY
customer_ID,
data_od,
data_do
) k3
ON k1.customer_ID = k2.customer_ID
LEFT JOIN
(select
COUNT(produkt_ID),
customer_ID,
data_od,
data_do,
customer_ID,
FROM tabela1
WHERE nazwa_produktu = 'D'
GROUP BY
customer_ID,
data_od,
data_do
) k4
ON k1.customer_ID = k4.customer_ID
LEFT JOIN
(select
COUNT(produkt_ID),
customer_ID,
data_od,
data_do,
customer_ID,
FROM tabela1
WHERE nazwa_produktu = 'E'
GROUP BY
customer_ID,
data_od,
data_do
) k5
ON k1.customer_ID = k5.customer_ID
LEFT JOIN
(select
COUNT(produkt_ID),
customer_ID,
data_od,
data_do,
customer_ID,
FROM tabela1
WHERE nazwa_produktu = 'F'
GROUP BY
customer_ID,
data_od,
data_do
) k6
ON k1.customer_ID = k6.customer_ID
GROUP BY
k1.customer_ID,
t1.data_od,
t2.data_do,