Der Kundenwert (Customer Value) - Business Intelligence - page 121

ISNULL((sum (b.Retouren)*5.8),0)Retourkosten,
sum (a.Nettosumme)- ISNULL(sum (b.Ret_Nettowert),0)- (sum
(a.Bestellungen)*9.5)- ISNULL((sum (b.Retouren)*5.8),0)Nettoertrag,
Cast(DATEDIFF ( day ,ld.first_order ,ld.last_order )as numeric) Tage,
→ Hier die Tage anfügen
cast ((cast(DATEDIFF ( day ,ld.first_order ,ld.last_order )as numeric)/
30.42)as DECIMAL(10,2))Monate → Hier die Monate anfügen
from
(SELECT c.riskID, min([postingDate]) first_order,max([postingDate])last_order
FROM [dbo].[iw_sales]s,[dbo].[iw_customer]c
where s.customerNo = c.customerNo
group by c.riskID)ld,
→ Select der Lebensdauer vorne einfügen (Alias ld)
(SELECT c.riskID, s.customerNo,
count (distinct s.orderNo)Bestellungen, sum (s.quantity)Artikel,
cast (sum (s.amount *s.quantity)as decimal(10,2))Nettosumme
FROM [dbo].[iw_customer]c, [dbo].[iw_sales]s
where c.customerNo = s.customerNo
and s.type = 2
group by c.riskID, s.customerNo)a
left outer join
(select rl.customerNo, count (distinct rl.returnNo)Retouren,
sum (rl.quantity)Ret_Artikel, sum (rl.line_amount)Ret_Nettowert
from [dbo].[iw_return_line]rl
where rl.type = 2
group by rl.customerNo)b
on a.customerNo = b.customerNo → outer join Alias a - Alias b
where ld.riskID = a.riskID
→ join zwischen Alias ld und Alias a erst nach dem outer join!
group by a.riskID, cast(DATEDIFF ( day ,ld.first_order ,ld.last_order )as
numeric), → Tage in Gruppierung aufnehmen
cast ((cast(DATEDIFF ( day ,ld.first_order ,ld.last_order )as numeric)/
30.42)as DECIMAL(10,2)) → Monate in Gruppierung aufnehmen
Lassen Sie die Abfrage laufen. Das Ergebnis sehen Sie in Bild 15.4.
Bild 15.4■ Abfrage Customer Lifetime Value