data og en between

Skal bruge en where hvor.

dag,måned og år felt er i hver deres felt.

pf_dg = dag
pf_md = måned
pf_aar = år

Jeg skal så lave en between så det hedder

pf_dg+'-'+pf_md+'-'+pf_aar between @fra and @til

Der skal bruges i reporting service, men sql'en burde være den samme.



Denne query virker ikke i reporting service, men fint i query analyser...

select pf_titelnr,pf_oplnr,pf_udgnr,


(select sum(pf_salgib) as salg from pf_varesdag where convert(datetime,convert(varchar(2),pf_dg) +'-'+ convert(varchar(2),pf_md)+'-'+ convert(varchar(4),pf_aar)) between convert(datetime,@datofra) and convert(datetime,@datotil) and pf_titelnr = a.pf_titelnr and pf_oplnr = a.pf_oplnr and pf_udgnr = a.pf_udgnr group by pf_titelnr) as salg
,
(select sum(pf_salgib) as totalsalg from pf_varesdag where pf_titelnr = a.pf_titelnr group by pf_titelnr) as totalsalg
,
(select top 1 pf_titnavnk from pf_titel where pf_titelnr = a.pf_titelnr and pf_oplnr = a.pf_oplnr and pf_udgnr = a.pf_udgnr) as pf_titnavnk
,
(select top 1 pf_udgivelse from pf_titel where pf_titelnr = a.pf_titelnr and pf_oplnr = a.pf_oplnr and pf_udgnr = a.pf_udgnr) as pf_udgivelse

from pf_titel a


where pf_udgstkode NOT IN ('SPE','BK','EX','REK')
and
pf_fnr = @for
and
pf_redaktionsgruppenr = @red
and
(select sum(pf_salgib) as salg from pf_varesdag where convert(datetime,convert(varchar(2),pf_dg) +'-'+ convert(varchar(2),pf_md)+'-'+ convert(varchar(4),pf_aar)) between convert(datetime,@datofra) and convert(datetime,@datotil) and pf_titelnr = a.pf_titelnr and pf_oplnr = a.pf_oplnr and pf_udgnr = a.pf_udgnr group by pf_titelnr) is not null


group by pf_titelnr,pf_oplnr,pf_udgnr
order by salg desc