Przerabiam kod remanentu z rozbiciem na dostawy i chciałbym wyciągnąć numer dokumentu i datę ostatniego wydania. Próbuję od dłuższego czasu i coś mi nie wychodzi.
select
t.tw_id,
t.tw_symbol AS [Symbol],
t.tw_nazwa AS [Nazwa towaru],
t.tw_JednMiary AS [jm],
(SELECT dok_MagRuch.mr_data FROM dok_MagRuch WHERE dok_MagRuch.mr_SeriaId = A.mr_SeriaId AND dok_MagRuch.mr_DoId IS NULL) AS [Data PZ/PW],
max(a.mr_data) AS [Data wejścia na mag],
(SELECT m.mag_Nazwa FROM sl_Magazyn m WHERE m.mag_Id = A.mr_magid) AS [Magazyn],
(SELECT d.dok_NrPelny FROM dok__Dokument d, dok_Pozycja p WHERE p.ob_TowId = A.mr_TowId AND d.dok_Id = p.ob_DokMagId AND d.dok_Typ IN (9,10,12) AND p.ob_Id = A.mr_PozId) AS [Dokument przyjęcia],
A.mr_ilosc AS [Przyjęto],
A.mr_SeriaId,
A.mr_PozId,
A.mr_Id,
sum(A.mr_ilosc-isnull(R.mr_ilosc,0)) AS [Pozostalo],
(SELECT MAX (r.mr_Data) FROM dok_MagRuch r WHERE r.mr_DoId = A.mr_SeriaId) AS [Data ost. wydania],
(SELECT TOP 1 d.dok_NrPelny FROM dok__Dokument d, dok_Pozycja p, dok_MagRuch r WHERE d.dok_Id = p.ob_DokMagId AND p.ob_Id = r.mr_PozId AND r.mr_DoId = A.mr_SeriaId AND d.dok_Typ IN (9,11,13) ORDER BY r.mr_PozId DESC) AS [Dokument wydania],
W.mw_cena AS [Cena jednostkowa],
sum(dbo.fnInsMul( (A.mr_ilosc-isnull(R.mr_ilosc,0)) , W.mw_cena, 2 )) AS [Wartość]
from dok_magruch A
inner join tw__towar T on A.mr_TowId = T.tw_Id
inner join vwDokMagWart W on A.mr_SeriaId = W.mw_SeriaId
left join ((select isnull( sum( C.mr_ilosc ), 0 ) mr_ilosc, C.mr_doId from dok_magruch C where C.mr_data<=(SELECT DATEADD(dd, 0,{D:Data:})) group by C.mr_doId)) R on R.mr_DoId = A.mr_id
left join (select mr_SeriaId, max(ob_NumerSeryjny) AS ob_NumerSeryjny, max(ob_Termin) AS ob_Termin, max(ob_Opis) AS ob_Opis from dok_Pozycja left join dok_MagRuch on mr_PozId = ob_Id where mr_DoId IS NULL group by mr_SeriaId) P on P.mr_SeriaId = A.mr_SeriaId
where W.mw_pozid in (SELECT TOP 1 T.mw_pozid FROM vwDokMagWart T WHERE T.mw_SeriaId = W.mw_seriaid and T.mw_data<= (SELECT DATEADD(dd, CASE ({LI:Pow 6 m-cy#1#Pow 12 m-cy#2#Wybrana data#3#:Wybór okresu:1}) WHEN 1 THEN -182 WHEN 2 THEN -365 WHEN 3 THEN 0 END,{D:Data:}))
ORDER BY mw_data DESC, mw_pozid DESC) and A.mr_magid in(3, 5, 18)
and A.mr_data<= (SELECT DATEADD(dd, CASE ({LI:Pow 6 m-cy#1#Pow 12 m-cy#2#Wybrana data#3#:Wybór okresu:1}) WHEN 1 THEN -182 WHEN 2 THEN -365 WHEN 3 THEN 0 END,{D:Data:})) and A.mr_ilosc > isnull(R.mr_ilosc,0)
group by t.tw_id, t.tw_symbol, t.tw_nazwa, t.tw_JednMiary, A.mr_DoId, A.mr_ilosc, W.mw_cena, A.mr_TowId, A.mr_MagId, A.mr_PozId, A.mr_SeriaId, A.mr_Id