Thanks virusstom for reply
i have changed day to Empday
But still getting same error
following line of code i have changed
,DATEADD(day, nn.N, 0) AS Empday
Is there any more changes..?
Thanks in advance.
now code is look like this.
SELECT
n.N
, DATEADD(day, n.N, 0) AS day
, n.EnrollNo
, n.EMachineNo
, Start.StartDT
, Finish.FinishDT
, CAST(Finish.FinishDT - Start.StartDT AS time) AS ElapsedTime
FROM (
SELECT
nn.N
, DATEADD(day, nn.N, 0) AS Empday
, t_1.EnrollNo
, t_1.EMachineNo
FROM dbo.Number AS nn
CROSS JOIN (
SELECT DISTINCT
tt.EnrollNo
, tt.EMachineNo
FROM dbo.tblEmpGenralLog AS tt
) AS t_1
WHERE
(dateadd( day, nn.N, 0 ) >= '1900-05-01'
and dateadd( day, nn.N, 0 ) < '2030-06-01'
) AS n
LEFT OUTER JOIN (
SELECT
EnrollNo
, EMachineNo
, DATEDIFF(day, 0, EmpDateTime) AS StartDayNo
, MIN(EmpDateTime) AS StartDT
FROM dbo.tblEmpGenralLog AS t
GROUP BY
DATEDIFF(day, 0, EmpDateTime)
, EnrollNo
, EMachineNo
) AS Start
ON Start.StartDayNo = n.N
AND Start.EnrollNo = n.EnrollNo
AND Start.EMachineNo = n.EMachineNo
LEFT OUTER JOIN (
SELECT
EnrollNo
, EMachineNo
, DATEDIFF(day, 0, EmpDateTime) AS FinishDayNo
, MAX(EmpDateTime) AS FinishDT
FROM dbo.tblEmpGenralLog AS t1
GROUP BY
DATEDIFF(day, 0, EmpDateTime)
, EnrollNo
, EMachineNo
) AS Finish
ON Finish.FinishDayNo = n.N
AND Finish.EnrollNo = n.EnrollNo
AND Finish.EMachineNo = n.EMachineNo
;