मेरे पास इस तरह की एक क्वेरी है:

DECLARE @stardate date = '31 OCT 2019'  ,
@endate date = '31 OCT 2019' ,
@emp_id int = 0,
@id int=1


DECLARE @lateVal as TIME
SET @lateVal = '08:15' -- Set Late Variable Here

;WITH calendar (FromDate) AS (
    SELECT @stardate AS FromDate
    UNION ALL
    SELECT DATEADD(day, 1, FromDate)
    FROM Calendar
    WHERE FromDate < @endate
) 


,
CTE AS 
(
select U.Name,U.Adluserid as 'Empid', cast(C.FromDate as date) AS [Date],
       min(case when IOType=0 then Edatetime end) as 'IN',
       max(case when IOType=1 then Edatetime end) as 'out',
       (case when max(Tr.Status)=1 then (select  Description from Tbl_Zeo_Status where Id=@id) when max(Tr.Status)=2 then 'Absent as Sick' else 'L' end) as leave_status,
       TS.Description,
       Tr.Status as statusid

       from calendar C

cross join Mx_UserMst U --on Trn.UsrRefcode=U.UserID
left join Mx_ACSEventTrn Trn  on cast(Edatetime as date)=cast(C.FromDate as date)  AND  Trn.UsrRefcode=U.AdlUserID
left join Tbl_Zeo_Empstatus E on  Trn.UsrRefcode=E.Emp_Id
left join Tbl_Zeo_EmpTrans Tr on Trn.UsrRefcode=Tr.Emp_Id and cast(Tr.Date as date)=cast(Edatetime as date)
left join Tbl_Zeo_Status TS on Tr.Status= Ts.Id
where cast(C.FromDate as date) between  @stardate and @endate  
 and (@emp_id=0 OR E.Emp_Id=@emp_id) and U.Name is not null and  U.UserIDEnbl = 1
group by U.Name,U.Adluserid , C.FromDate ,ts.Description,Tr.Status
)
 SELECT  ROW_NUMBER() OVER(ORDER BY name ASC) AS [id],[Empid],[Name],[IN],[OUT],
 (case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
 when cast([IN] as time) < @lateVal then 'Present' else 'Late' end) as status,[Description],
 [statusid]

 FROM CTE
 order by [Name],[IN]

अगर मेरा विवरण कॉलम शून्य है तो मैं स्थिति कॉलम मान दिखाना चाहता हूं इसलिए मैंने अपनी क्वेरी को इस तरह संपादित किया:

ISNULL( [Description],[status]) as Des

लेकिन यह त्रुटि दिखा रहा है अमान्य कॉलम नाम 'स्थिति'।

यह ऐसा क्यों दिखा रहा है

0
user3262364 4 नवम्बर 2019, 08:08

1 उत्तर

यह मान प्राप्त करने के लिए आप subquery का उपयोग कर सकते हैं।

;WITH calendar (FromDate) AS (
    SELECT @stardate AS FromDate
    UNION ALL
    SELECT DATEADD(day, 1, FromDate)
    FROM Calendar
    WHERE FromDate < @endate
) 
,
CTE AS 
(
select U.Name,U.Adluserid as 'Empid', cast(C.FromDate as date) AS [Date],
       min(case when IOType=0 then Edatetime end) as 'IN',
       max(case when IOType=1 then Edatetime end) as 'out',
       (case when max(Tr.Status)=1 then (select  Description from Tbl_Zeo_Status where Id=@id) when max(Tr.Status)=2 then 'Absent as Sick' else 'L' end) as leave_status,
       TS.Description,
       Tr.Status as statusid
       from calendar C
cross join Mx_UserMst U --on Trn.UsrRefcode=U.UserID
left join Mx_ACSEventTrn Trn  on cast(Edatetime as date)=cast(C.FromDate as date)  AND  Trn.UsrRefcode=U.AdlUserID
left join Tbl_Zeo_Empstatus E on  Trn.UsrRefcode=E.Emp_Id
left join Tbl_Zeo_EmpTrans Tr on Trn.UsrRefcode=Tr.Emp_Id and cast(Tr.Date as date)=cast(Edatetime as date)
left join Tbl_Zeo_Status TS on Tr.Status= Ts.Id
where cast(C.FromDate as date) between  @stardate and @endate  
 and (@emp_id=0 OR E.Emp_Id=@emp_id) and U.Name is not null and  U.UserIDEnbl = 1
group by U.Name,U.Adluserid , C.FromDate ,ts.Description,Tr.Status
)
select id, EmpId, [Name], [IN], [OUT], [status], isnull([Description], [status]) from ( select ROW_NUMBER() OVER(ORDER BY name ASC) AS [id],[Empid],[Name],[IN],[OUT],
 (case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
    when cast([IN] as time) < @lateVal then 'Present' else 'Late' end) as status, [Description],
 [statusid]
 FROM CTE) t1
 order by [Name],[IN]

या, आपको अपना case कथन दोहराना होगा।

isnull([Description],(case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
    when cast([IN] as time) < @lateVal then 'Present' else 'Late' end))
1
Ed Bangga 4 नवम्बर 2019, 08:31
@ user3262364, मैं देखता हूं, आपका क्या मतलब है, अद्यतन उत्तर देखें
 – 
Ed Bangga
4 नवम्बर 2019, 08:39