وقت بخیر دوستان من دستور زیر رو نوشتم اما خروجی که بهم میده کامل نیست. نتیجه درسته ولی چرا باید ستون اول و سوم null بیاد !!!!! کجای دستورم اشتباه هست؟
; 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]
)
select PersonnelTbl.PersonnelId,sum(cte.CountDay) as CountDay,PersonnelTbl.Name+' '+PersonnelTbl.Family as PerName,cte1.CountTime
from cte inner join PersonnelTbl on PersonnelTbl.PersonnelId=cte.PersonnelId full join cte1 on cte1.PersonnelId =PersonnelTbl.PersonnelId
group by PersonnelTbl.PersonnelId,PersonnelTbl.Name,Personn elTbl.Family,CountTime
اینم خروجی
weweqweqw.png
پاسخ با نقل قول