Dokument - -= SQL frågor =- - Utskick kundreferens

Utskick kundreferens (MBG 2018-04)

Uppdatera Kundref med senaste fakturadatum (Krsodat)

update Kundref
set Krsudat=(select Top 1 Oinvdate from Histinf where Ocustomer=Krknr and Ostatus='O' order by Oinvdate desc)
where IsNull((select Top 1 Oinvdate from Histinf where Ocustomer=Krknr and Ostatus='O' order by Oinvdate desc),'')<>''

 

Kunder och kontakter senaste fem åren samt nyhetsbrev

select Kcustomer,Kname,Kadr0,Kadr1,Kadr2,Kcountry,Klastdate
,Krid,Krkpers,Krsodat,Krnews
,(select count(*) from Kundref where Krknr=Kcustomer ) AntKontakter
from kundref
join Kund on Krknr=Kcustomer
where
(Krid IN 
(select Krid from kundref where Krnews=1)
)
or
(Krid IN
(select (select top 1 Krid from Kundref where Krknr=T1.Krknr order by Krsodat desc) from kundref T1
join Kund on Krknr=Kcustomer
where Klastdate>DateAdd(year,-5,GetDate())
)
)
order by Kcustomer,Klastdate desc

 

Kunder utan kontakter senaste fem åren

select Kcustomer,Kname,Kadr0,Kadr1,Kadr2,Kcountry,Klastdate
,(select count(*) from Kundref where Krknr=Kcustomer ) AntKontakter
from kund
where Klastdate>DateAdd(year,-5,GetDate())
and (select count(*) from Kundref where Krknr=Kcustomer )=0

 

2018-04-25