Domyślam się, że muszę powiązać jakoś tabelę cen z tabelą zasobów, coś w ten deseń:
Kod: Zaznacz cały
select * from cdn.twrceny
inner join cdn.twrzasoby on twz_twrnumer=twc_twrnumer
Moderator: mikey
Kod: Zaznacz cały
select * from cdn.twrceny
inner join cdn.twrzasoby on twz_twrnumer=twc_twrnumer
Kod: Zaznacz cały
@PAR ?@S100|TowKod|&Kod towaru:REG= @? @TIP(Podaj fragment kodu towaru) PAR@
@PAR ?@S200|TowNaz|&Nazwa towaru:REG= @? @TIP(Podaj fragment nazwy towaru) PAR@
@PAR ?@N-15|IdGrupy|&Id grupy towarowej:0 @? @RL(-1) @RH(99999999) @TIP(Aby uzyskać ID grupy towarowej, na liście grup towarowych naciśnij wykrzyknik) PAR@
@PAR ?@N-14.2|min|Marża poniżej:REG=50 @? @RL(-99999999) @RH(99999999) PAR@
@PAR ?@N-14.2|max|Marża powyżej:REG=250 @? @RL(-99999999) @RH(99999999) PAR@
DECLARE @KursE DECIMAL(15,4)
SELECT @KursE = WaE_KursL / 100 FROM CDN.WalElem
WHERE (WaE_Symbol = 'EUR') AND (WaE_Lp = 1)
AND WAE_KURSTS =
(
SELECT MAX(WaE_KursTS) FROM CDN.WalElem
WHERE WaE_Symbol = 'EUR' AND WaE_Lp = 1
)
SELECT 1 AS Lp, TK.Twr_Kod AS Kod, TK.Twr_Nazwa AS Nazwa, TWC1.TwC_Wartosc AS Cena_sprzed, TWC1.TwC_Waluta AS Waluta, @KursE AS Kurs_EUR,
CASE WHEN TWC1.TwC_Waluta = 'PLN' THEN TWC1.TwC_Wartosc ELSE
CAST(TWC1.TwC_Wartosc * @KursE AS DECIMAL (18,2)) END AS Cena_sprzed_po_kursie,
(
SELECT TWC2.TwC_Wartosc FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
) AS Cena_zakupu,
(
SELECT TWC2.TwC_Waluta FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
) AS Waluta_zak,
CAST
(
(
(
(TWC1.TwC_Wartosc * CASE WHEN TWC1.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END)
) -
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2)
) /
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2) * 100
AS DECIMAL(15,2)
) AS Marza_procent
, TWC1.TwC_Marza AS Marza_zakladana
FROM CDN.TwrKarty TK
INNER JOIN CDN.TwrGrupyDom ON TK.Twr_GIDTyp = TGD_GIDTyp AND TK.Twr_GIDNumer = TGD_GIDNumer
LEFT JOIN CDN.TwrCeny TWC1 ON TWC1.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC1.TwC_TwrTyp = TK.Twr_GIDTyp
LEFT JOIN CDN.TwrZasoby ON TK.Twr_GIDNumer = TwZ_TwrNumer AND TK.Twr_GIDTyp = TwZ_TwrTyp
WHERE TWC1.TwC_TwrLp = TK.Twr_CenaSpr -- cena domyślna // 1 -- Cena EUR
AND TWC1.TwC_Wartosc <> 0
AND TK.Twr_Archiwalny <> 1
AND
(
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2)
) <> 0
AND
(
(
CAST
(
(
(
TWC1.TwC_Wartosc * CASE WHEN TWC1.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
) -
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2)
) /
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2) * 100
AS DECIMAL(15,2)
)
) > ??max OR
(
CAST
(
(
(
TWC1.TwC_Wartosc * CASE WHEN TWC1.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
) -
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2)
) /
ROUND((
SELECT TWC2.TwC_Wartosc * CASE WHEN TWC2.TwC_Waluta = 'PLN' THEN 1 ELSE @KursE END
FROM CDN.TwrCeny AS TWC2
WHERE TWC2.TwC_TwrNumer = TK.Twr_GIDNumer AND TWC2.TwC_TwrTyp = TK.Twr_GIDTyp AND TWC2.TwC_TwrLp = 0
), 2) * 100
AS DECIMAL(15,2)
)
) < ??min
)
AND TK.Twr_Nazwa LIKE
CASE WHEN ??TowNaz = '' THEN '%'
ELSE '%' + ??TowNaz + '%' END
AND TK.Twr_Kod LIKE
CASE WHEN ??TowKod = '' THEN '%'
ELSE '%' + ??TowKod + '%' END
AND
(
(TGD_GrONumer = ??IdGrupy)
OR (TGD_GrONumer IN (SELECT TwL_GIDNumer FROM CDN.TwrLinki WHERE TwL_GIDTyp = -16 AND TwL_GRONumer = ??IdGrupy))
)
-- CDN.CzyTowarWGrupie(TK.Twr_GIDNumer, ??IdGrupy) = 1
GROUP BY TK.Twr_GIDNumer, TK.Twr_GIDTyp, TK.Twr_Kod, TK.Twr_Nazwa, TWC1.TwC_Wartosc, TWC1.TwC_Waluta, TWC1.TwC_Marza
HAVING (SUM(TwZ_IlSpr) <> 0 OR SUM(TwZ_IlMag) <> 0)
ORDER BY TK.Twr_Kod