I'm trying to create an xml with customer specific prices for a webshop I am working on atm.
I have to join data from 6 tables in order to acomplish that.
I ran into a problem I am unable to solve for a couple of days now.
The thing is that I join customer ID with discount group (works fine),
then I join it with data that are stored in another table that connects item groups with customer discount groups (this table is not complete - it has data if someone entered discount for specific customer for specific group of items). The end result lacks all items from item groups that have no data in this table.
then I join it with item discount group table where most items have item discount group written in it.
then i tried to full outer join it with a table of items that have webshop tag enabled (so I would get all items in the result that are included in the webshop but have no discounts for a customer entered) - this thing does not work
lastly I cross apply latest prices with items. (works fine)
Any help would be gravely appreciated.
Code:
select stranka.SifraStranke,
-- stranka.DomacaStran,
-- strFaktura.SifraGrucePopusti,
-- popust.OdstotekPopusta,
-- popust.SifraGruceArtikla,
-- artProdaja.SifraArtikla,
osnArtikel.SifraArtikla,
cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)
from STRStrankeSplosno stranka -- customer basic data table
left join
STRStrankeFaktura strFaktura -- customer financial data table
on stranka.SifraStranke=strFaktura.SifraStranke
left join
ARTPopustiStranke popust -- customer discount data table for each discount group
on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
ARTArtikliProdaja artProdaja -- item discount group data table
on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
ARTArtikliOsnovniP osnArtikel -- item basic data table
on osnArtikel.SifraArtikla=artProdaja.SifraArtikla
cross apply (
select top 1 cene.ProdajnaCena
from ARTArtikliCeneCP cene -- item prices data table
where cene.SifraArtikla=osnArtikel.SifraArtikla
and cene.SifraCenovnegaPodrocja='MC'
order by cene.DatumZacetkaVeljavnosti desc
) cene
where stranka.DomacaStran<>''
and osnArtikel.SpletObjava='D'
and stranka.StevilkaSifranta=2
and strFaktura.StevilkaSifranta=2
and popust.StevilkaSifranta=2
and artProdaja.StevilkaSifranta=2
and strFaktura.SifraGrucePopusti<>''
order by stranka.SifraStranke, osnArtikel.SifraArtikla