Hi,
this is my first post on this forum. I hope you can help me out.
I have a product stock table, and for same product there is a table which hold sell data for those products, with quantity, customer name, and date time stamp. Now, for the product stock table there is no datetime column. I need to be able to apply date time filter in the dashboard, and compare sold quantity for a given period with current product stock. Because, there is no datetime column in the stock table, in the resulting pivot table the stock quantity is not shown.
Is there a workaround, to always show stock quantity in the resulting pivot table, no matter datetime filter applied.
Here is my SQL code:
select
aa.acIdent AS [Šifra artikla],
bb.acname AS [Naziv artikla],
bb.acClassif AS [Klasifikacija],
aa.acWarehouse AS [Skladište],
aa.anStock AS [Stanje zalihe],
NULL AS [Izdata količina],
NULL AS [Datum izdavanja],
getdate() AS [Datum skladišta],
NULL AS [Kupac]
from
the_stock aa
join the_setitem bb on aa.acIdent=bb.acident
where acWarehouse IN ('001-CENTRALNO SKLADIŠTE GP','110–Skl GP Megamix Sarajevo','120–Skl GP Mostar') and bb.acClassif IN ('SNA','PTA','UVV','SLA','PEA')
UNION ALL
SELECT
bb.acIdent AS [Šifra Artikla],
bb.acName AS [Naziv artikla],
cc.acClassif AS [Klasifikacija],
aa.acIssuer AS [Skladište izdavanja],
NULL AS [Stanje zalihe],
bb.anQty AS [Izdata količina],
aa.adDate AS [Datum izdavanja],
NULL AS [Datum skladišta],
aa.acReceiver AS [Kupac]
from the_move aa
join the_moveitem bb on aa.ackey=bb.ackey
join the_setitem cc on bb.acIdent=cc.acIdent
where year(aa.adDate)=year(getdate()) and aa.acIssuer IN ('001-CENTRALNO SKLADIŠTE GP','110–Skl GP Megamix Sarajevo','120–Skl GP Mostar')
AND cc.acClassif IN ('PTA','SNA','SLA','UVV') AND aa.acReceiver NOT IN ('001-CENTRALNO SKLADIŠTE GP','110–Skl GP Megamix Sarajevo','120–Skl GP Mostar','Mlini d.o.o.','')