Dziekuję Serdecznie za zestawienie z cechą, ale mam problem
teoretycznie wkleiłem odpowiednie linijki ale wyskakuje mi błąd INVALID NAME cht_IdCecha mysle ze chodzi o linijkę z kodem już filtrowania cechy.
select tw_Symbol [Symbol]
,tw_Nazwa [Nazwa]
,case tw_Zablokowany when 1 then CAST(0 as bit) else CAST(1 as bit) end [Aktywny]
,ISNULL(w.ilosc4, 0) [4 lata ilość]
,ISNULL(w.ilosc3, 0) [3 lata ilość]
,ISNULL(w.ilosc2, 0) [2 lata ilość]
,ISNULL(w.ilosc1, 0) [2016 ilość - poprzedni rok]
,ISNULL(w.ilosc0, 0) [2017 ilość- -biezacy rok]
,(select SUM(st_Stan)
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (1,6,7)) [STAN NA MAG]
,(select st_Stan
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (1)) [TAS]
,(select st_Stan
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (6)) [Vent s.c.]
,(select st_Stan
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (7)) [ZNUM s.c.]
,(select st_Stan
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (2)) [VENT AUTO]
,(select st_Stan
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (3)) [Znum]
,(select SUM(st_Stan)
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (4)) [Nomir]
from tw__Towar
left join
(
select ob_TowId
,SUM(case when dok_DataWyst between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 0, 0) and DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1, GETDATE())) - 0, 0)) then ob_IloscMag else 0 end) ilosc0
,SUM(case when dok_DataWyst between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) and DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1,GETDATE())) - 1, 0)) then ob_IloscMag else 0 end) ilosc1
,SUM(case when dok_DataWyst between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) and DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1, GETDATE())) - 2, 0)) then ob_IloscMag else 0 end) ilosc2
,SUM(case when dok_DataWyst between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 3, 0) and DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1, GETDATE())) - 3, 0)) then ob_IloscMag else 0 end) ilosc3
,SUM(case when dok_DataWyst between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 4, 0) and DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1, GETDATE())) - 4, 0)) then ob_IloscMag else 0 end) ilosc4
from dok__Dokument
join dok_Pozycja on dok_Id = ob_DokMagId
INNER JOIN tw__Towar ON (ob_TowId = tw_Id)
INNER JOIN tw_Stan ON (tw_Id = st_TowId) AND (st_MagId = dok_MagId)
LEFT JOIN dbo.tw_CechaTw ON (tw_Id = cht_IdTowar)
where dok_Status <> 2
and dok_Typ in (13)
and dok_DataWyst >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 5, 0)
group by ob_TowId
) w on tw_Id = ob_TowId
where tw_Usuniety = 0
and
(
tw_Zablokowany = {LI:(dowolnym)#-1#aktywne#0#nieaktywne#1#:Towary o statusie:-1}
or
-1 = {LI:(dowolnym)#-1#aktywne#0#nieaktywne#1#:Towary o statusie:-1}
)
and
(select SUM(st_Stan)
from tw_Stan
where st_TowId = tw_Id
and st_MagId in (1,2,3,4,6,7)) > {LI:(dowolnym)#-1#niezerowym#0:o stanie:-1}
AND (tw_IdGrupa IN ({CHL_DB: SELECT grt_Id, grt_Nazwa FROM sl_GrupaTw ORDER BY grt_Nazwa:Grupa Towarów:0:Nie wybrana}))
AND ( ISNULL(cht_IdCecha,0) = (
CASE WHEN {DB: SELECT ctw_Id = 0, ctw_Nazwa = 'Nie wybrana' UNION ALL SELECT ctw_Id, ctw_Nazwa FROM dbo.sl_CechaTw ORDER BY ctw_Nazwa:Cecha:0:Nie wybrana} = 0 THEN ISNULL(cht_IdCecha,0)
ELSE {DB: SELECT ctw_Id = 0, ctw_Nazwa = 'Nie wybrana' UNION ALL SELECT ctw_Id, ctw_Nazwa FROM dbo.sl_CechaTw ORDER BY ctw_Nazwa:Cecha:0:Nie wybrana} END))
