הקוד הזה מחזיר לי טבלה מלאה ב NULL , אולי משהו יודע למה SQL

[מ ו ת]

הקוד הזה מחזיר לי טבלה מלאה ב NULL , אולי משהו יודע למה SQL

declare @reportDateM1 varchar(100)
declare @reportDateM2 varchar(100)
--נתוני התאריך
set @reportDateM1 ='2018-05-01'
set @reportDateM2 ='2018-05-31'
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(oprj.PrjName)
from OPRJ
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
create table #TEMP1001(summ float, name nvarchar(100), code nvarchar(100))
insert into #TEMP1001(summ,name,code)
(select distinct sum(jdt1.Debit) sm,ocrd.CardName c,oprj.PrjCode p
from ocrd
join jdt1
on jdt1.ContraAct = ocrd.CardCode
join ojdt
on ojdt.TransId = JDT1.TransId
join oprj
on oprj.PrjCode = jdt1.Project
where (jdt1.Account = '8102' and (jdt1.DueDate >= @reportDateM1 and JDT1.DueDate <= @reportDateM2)
and not ( LineMemo LIKE N'%עסקה%' or LineMemo LIKE N'%עיסקה%' or LineMemo like N'%עסקת%')) and (oprj.PrjCode =1001)
group by ocrd.CardName,oprj.PrjCode
union
select sum(dpo1.Price) sm,ocrd.CardName c,oprj.PrjCode p
from ocrd
join odpo
on odpo.CardCode = ocrd.CardCode
join dpo1
on dpo1.DocEntry =odpo.DocEntry
join OPRJ
on oprj.PrjCode = dpo1.Project
where (odpo.DocDueDate >= @reportDateM1 and odpo.DocDueDate <= @reportDateM2) and (oprj.PrjCode =1001)
group by ocrd.CardName,oprj.PrjCode)
set @query ='SELECT distinct c,'+ (@cols)+' from
(
select #temp1001.name c ,oprj.PrjCode p,
case (oprj.PrjCode)
when 2 then #TEMP1001.summ*0.4384
when 3 then #TEMP1001.summ*0.4179
when 4 then #TEMP1001.summ*0.0566
when 5 then #TEMP1001.summ*0.0871
else 0
end as s
from #TEMP1001
cross join OPRJ
where oprj.PrjCode>1 and oprj.PrjCode<6 )x
pivot
(
max(s)
for p in (' + @cols + ')
) p '
execute(@query)