Marżowość

Przykład na rozwiązanie problemu obliczania marży na dokumentach sprzedaży z uwzględnieniem narzutów procentowych na grupy towarów.

1. Zdefiniuj słownik narzutów
2. Dodaj widok

CREATE VIEW vStawki as(
SELECT
	year(t.okres + '-01') as rok,
	month(t.okres+'-01') as miesiac,
	t.okres,
	isnull(t.st1,0) as st1, -- wkladki
	isnull(t.st2,0) as st2, --pasty
	isnull(t.st3,0) as st3, --gumy
	isnull(t.st4,0) as st4  --sznurowadla
	FROM 
	(SELECT
		wiersz.ref.value('(Key/text())[1]', 'varchar(50)') AS okres,
		wart.ref.value('(anyType/text())[1]', 'decimal (18,2)') AS 'st1',
		wart.ref.value('(anyType/text())[2]', 'decimal (18,2)') AS 'st2',
		wart.ref.value('(anyType/text())[3]', 'decimal (18,2)') AS 'st3',
		wart.ref.value('(anyType/text())[4]', 'decimal (18,2)') AS 'st4'
		FROM
		PM.Dashboard.DictionaryItem i
		CROSS apply
		(SELECT   cast(value AS xml)) AS X(X)
		CROSS apply X.X.nodes('MultiValueItem') AS wiersz(ref)
		CROSS apply wiersz.ref.nodes('Values') AS wart(ref)
		WHERE    i.DictionaryId = 1	
	)AS t
	)

3. Dodaj zestaw danych z filtrami. Dostosuj według potrzeb.

set arithabort on;
;with stawki as(	
select s.rok, s.miesiac, s.st1 as stawka, 'MWG'  as kod FROM PM.dbo.vStawki s 
union
select s.rok, s.miesiac, s.st2 as stawka, 'MWGP'   as kod FROM PM.dbo.vStawki s 
union 
select s.rok, s.miesiac, s.st3 as stawka, 'MG'   as kod FROM PM.dbo.vStawki s 
union
select s.rok, s.miesiac, s.st4 as stawka, 'F-MGF'   as kod FROM PM.dbo.vStawki s 
)
,sp as (
	SELECT year(d.data) as rok, Month(d.data) as miesiac,
	m.Nazwa as Magazyn,
	Kanal = CASE WHEN f.Data in(02, 03, 04, 06, 07, 08, 09, 10, 11, 16, 17, 25,27) THEN 'Przedstawiciele Kraj' ELSE 'Inne kanaly' END,
	Region = ISNULL(f.Data,'Nieokreslony'), 
	gr.Data as GrupaTowarowa,
mag.Data as CechaMagazyn,	
	p.SumaNetto as Netto ,p.IloscValue as Ilosc, 
	ISNULL(o1.PrzychodWartosc,0) + ISNULL(o2.PrzychodWartosc,0) as Koszt,
	p.SumaNetto - ISNULL(o1.PrzychodWartosc,0) - ISNULL(o2.PrzychodWartosc,0) as Marza	
	FROM 
	Dokhandlowe d join PozycjeDokHan p on p.Dokument = d.id
	LEFT JOIN PozRelHandlowej rel1 on ((rel1.PodrzednyDok = p.Dokument and rel1.PodrzednaIdent = p.Ident))
	left join (SELECT SUM(o1.PrzychodWartosc) as PrzychodWartosc,o1.RozchodDokument, o1.RozchodPozycjaIdent from Obroty o1 group by o1.RozchodDokument, o1.RozchodPozycjaIdent) as o1
	on  (rel1.NadrzednyDok = o1.RozchodDokument and rel1.NadrzednaIdent = o1.RozchodPozycjaIdent)

	LEFT JOIN PozRelHandlowej rel2 on ((rel2.NadrzednyDok = p.Dokument and rel2.NadrzednaIdent = p.Ident))
	left join (SELECT SUM(o2.PrzychodWartosc) as PrzychodWartosc,o2.RozchodDokument, o2.RozchodPozycjaIdent from Obroty o2 group by o2.RozchodDokument, o2.RozchodPozycjaIdent) as o2
	on  (rel2.PodrzednyDok = o2.RozchodDokument and rel2.PodrzednaIdent = o2.RozchodPozycjaIdent)

	LEFT JOIN Features gr on gr.Parent = p.Towar and gr.Name = 'grupa' and gr.ParentType = 'Towary' and gr.Lp = 0
	LEFT JOIN Features f on f.Parent = d.Odbiorca and f.Name = 'Region' and f.ParentType = 'Kontrahenci' and f.Lp = 0
	LEFT JOIN Features mag on mag.Parent = p.Towar and mag.Name = 'magazyn' and mag.ParentType = 'Towary' and mag.Lp = 0
	
join DefDokHandlowych def on def.id = d.Definicja	
join Magazyny m on m.id = d.magazyn
	where d.Kategoria = 2
	and (def.Kategoria =2 and def.DuplikatWartosci = 'false') 
	and FILTER(d.data, @okres)
	-- and d.data between '2015-01-01' and '2015-12-01'
	)
   ,ksp as (
--kfv2 - kwz2
SELECT 
year(d.data) as rok, Month(d.data) as miesiac,
	m.Nazwa as Magazyn,
	Kanal = CASE WHEN f.Data in(02, 03, 04, 06, 07, 08, 09, 10, 11, 16, 17, 25,27) THEN 'Przedstawiciele Kraj' ELSE 'Inne kanaly' END,
	Region = ISNULL(f.Data,'Nieokreslony'), 
	gr.Data as GrupaTowarowa,
	mag.Data as CechaMagazyn	,
p.SumaNetto  - pFV.SumaNetto as Netto, p.IloscValue - pFV.IloscValue Ilosc ,ISNULL(o.KorektaKosztu,0) as Koszt,
p.SumaNetto - pFV.SumaNetto - ISNULL(o.KorektaKosztu,0) as Marza
FROM Dokhandlowe d join PozycjeDokHan p on p.Dokument = d.id
LEFT JOIN PozRelHandlowej relKWZ on (relKWZ.NadrzednyDok = p.Dokument and relKWZ.NadrzednaIdent = p.Ident)
LEFT JOIN PozRelHandlowej relFV on (relFV.PodrzednyDok = p.Dokument and relFV.PodrzednaIdent = p.Ident)
LEFT JOIN PozycjeDokHan pFV on pFV.Dokument = relFV.NadrzednyDok  and pFV.Ident = relFV.NadrzednaIdent
left join (select SUM(o.IloscValue) as KorektaIlosci, 
			SUM(o.PrzychodWartosc) as KorektaKosztu, 
			o.RozchodDokument,
			o.RozchodPozycjaIdent from obroty o 
			group by o.RozchodDokument, o.RozchodPozycjaIdent
			) o on relKWZ.PodrzednyDok = o.RozchodDokument and relKWZ.PodrzednaIdent = o.RozchodPozycjaIdent 
				LEFT JOIN Features gr on gr.Parent = p.Towar and gr.Name = 'grupa' and gr.ParentType = 'Towary' and gr.Lp = 0
	LEFT JOIN Features f on f.Parent = d.Odbiorca and f.Name = 'Region' and f.ParentType = 'Kontrahenci' and f.Lp = 0
	LEFT JOIN Features mag on mag.Parent = p.Towar and mag.Name = 'magazyn' and mag.ParentType = 'Towary' and mag.Lp = 0
	
	join DefDokHandlowych def on def.id = d.Definicja
	join magazyny m on m.id = d.magazyn

where d.Kategoria = 3
	and FILTER(d.data, @okres)
	-- and d.data between '2015-01-01' and '2015-12-01'
)

SELECT 'SP' as Kierunek, sp.rok, magazyn, sp.miesiac, Kanal, Region, GrupaTowarowa, CechaMagazyn, 
SUM(Netto) as Netto, 
SUM(Koszt) as KosztPW, 
SUM(Koszt * (1 +(ISNULL(stawki.stawka,0)/100))) as Koszt,
AVG(ISNULL(Stawki.stawka,0)) as ProcentNarzut,
SUM(Marza) as MarzaBezNarzutu
FROM sp left join stawki on stawki.rok = sp.rok and stawki.miesiac = sp.miesiac and stawki.kod = sp.CechaMagazyn
GROUP BY sp.rok,  sp.miesiac, Kanal, Region, GrupaTowarowa, magazyn, CechaMagazyn
UNION 
SELECT 'KOR' as Kierunek, ksp.rok, magazyn, ksp.miesiac, Kanal, Region, GrupaTowarowa,CechaMagazyn, 
SUM(Netto) as Netto, 
SUM(Koszt) as KosztPW, 
SUM(Koszt * (1+(ISNULL(stawki.stawka,0)/100))) as Koszt, 
AVG(ISNULL(stawki.stawka,0)) as ProcentNarzut,
SUM(Marza) as MarzaBezNarzutu
FROM ksp left join stawki on stawki.rok = ksp.rok and stawki.miesiac = ksp.miesiac and stawki.kod = ksp.CechaMagazyn
GROUP BY ksp.rok,  ksp.miesiac, Kanal, Region, GrupaTowarowa, magazyn, CechaMagazyn