حل شد اونم چه حل شدنی
عجیب ترین کدی که نوشتم. به احتمال زیاد پرفورمنسشم داغووون
; with cte as(SELECT [Ensani_MorakhasiPersonnelTbl].[PersonnelId],
case
when SUBSTRING([Date2],6,2)> SUBSTRING([Date1],6,2) AND CONVERT(int,SUBSTRING([Date1],6,2))= 6 then CountDay-CONVERT(int,SUBSTRING([Date1],9,2))
when SUBSTRING([Date1],6,2)< SUBSTRING([Date2],6,2) AND CONVERT(int,SUBSTRING([Date2],6,2))=6 then CONVERT(int,SUBSTRING([Date2],9,2))
else CountDay end as CountDay FROM [Ensani_MorakhasiPersonnelTbl] inner join PersonnelTbl on [Ensani_MorakhasiPersonnelTbl].PersonnelId=PersonnelTbl.PersonnelId
where [Date1] between '1396/06/01' and '1396/06/31' or [Date2] between '1396/06/01' and '1396/06/31'
)
, cte1 as(SELECT [Ensani_MorakhasiPersonnelTbl].[PersonnelId]
,SUM([Ensani_MorakhasiPersonnelTbl].CountTime) as CountTime
FROM [Ensani_MorakhasiPersonnelTbl]
where [Date3] between '1396/06/01' and '1396/06/31'
group by [Ensani_MorakhasiPersonnelTbl].[PersonnelId]
),
cte2 as(
select PersonnelTbl.PersonnelId,sum(cte.CountDay) as CountDay,PersonnelTbl.Name+' '+PersonnelTbl.Family as PerName,0.00 as CountTime
from cte join PersonnelTbl on PersonnelTbl.PersonnelId=cte.PersonnelId
group by PersonnelTbl.PersonnelId,PersonnelTbl.Name,Personn elTbl.Family
union
select PersonnelTbl.PersonnelId,0 as CountDay,PersonnelTbl.Name+' '+PersonnelTbl.Family as PerName,CONVERT(decimal(3,2),sum(cte1.CountTime)) as CountTime
from cte1 join PersonnelTbl on PersonnelTbl.PersonnelId=cte1.PersonnelId
group by PersonnelTbl.PersonnelId,PersonnelTbl.Name,Personn elTbl.Family
)
select cte2.PersonnelId,cte2.PerName,sum(CountDay),sum (CountTime )
from cte2
group by cte2.PersonnelId,cte2.PerName