Show column value indipendetly of datetime filter

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.','')

Don't use a UNION. Join those 2 queries so that your have the stock amount on every line rather than just once per product.
Your queries aren't really UNION compatible - adding null columns doesn't count!

1 Like