SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 OFF DAY
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  06:25:27  Show Profile  Reply with Quote
in shift O is not coming but in remarks off day and absent is coming

means when sunday remakrs show off day and when employee absent remarks show absent
immad uddin ahmed

Edited by - immad on 06/18/2013 06:26:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  06:35:11  Show Profile  Reply with Quote
put a similar condition for shift too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  06:48:20  Show Profile  Reply with Quote
i write this syntax


case when remarks = 'OFF DAY' then 'O' else t4.Shift end shift,


Msg 207, Level 16, State 1, Procedure AT, Line 21
Invalid column name 'remarks'.


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  06:56:19  Show Profile  Reply with Quote
you want everything spoonfed dont you?
is it that difficult to replicate the given logic to a different column?


select
t2.[date],
t2.eid,
e.ename,
case when datediff(dd,0,t2.date)%7 < 5 then  t4.shift else 'G' end as shift,
d.name designation,
t5.dname,
t.[Timein] as timein,
t.[Timeout] as timeout,
CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,
CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) ELSE NULL END as excesshorttime,
CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] and spendtime is not null THEN 'Excess' else 'Short' END Excess,

case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late' 
when convert(varchar(10),t.[Timein],108) is null and convert(varchar(10),t.[Timeout],108) is null and datediff(dd,0,t2.date)%7 < 5 then 'Absent' 
else '' end Remarks,
case when (t4.minute - Time_Minutes) >= 120 then 'Half Day' 
else '' end HALFDAY

FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-01','2013-05-19',0,0)b
) t2 
left join (select row_number() over (partition by [date],eid ORDER BY timein ASC) AS Seq
,sum(DATEDIFF(minute, [Timein], [Timeout])) OVER (PARTITION BY [date],eid) AS Time_Minutes
,* 
FROM attend_log) t
on t.[date] = t2.[date] 
and t.eid = t2.eid
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (16963) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
--and t.Seq = 1
where t2.eid = 16963
order by t2.[date], t.[Timein]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  06:59:25  Show Profile  Reply with Quote
well i did it my self from crytsal report
i make a formula in crystal report and its showing 'O' in the report

thank any way

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  07:17:06  Show Profile  Reply with Quote
you're welcome

Suggest you to learn the solutions provided and ask in case of any doubt. Otherwise you wont benefit anything from the whole exercise.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  07:22:07  Show Profile  Reply with Quote

now you r talking
i like to ask question related to this query

FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-05-01','2013-05-31',0,0)b
) t2

what is cross join do and datediff do

and why you use seq=1 what the use of it

CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  07:32:17  Show Profile  Reply with Quote
quote:
Originally posted by immad


now you r talking
i like to ask question related to this query

FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-05-01','2013-05-31',0,0)b
) t2

what is cross join do and datediff do

and why you use seq=1 what the use of it

CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,


immad uddin ahmed


Now this is what i appreciate Way to go and way to learn!

1. the cross join as you probably know gives you the cartesian product. In this case i used cross join to the UDF which i created which will provide me the calendar details (ie all days between two dates which are passed as arguments). The cross join will make the dates repeat for each employees so that you'll get all employee names repeated for each of the dates. This is needed because you wanted every date reported for a employee between the two dates even when his details are not present in attend table (ie holidays, weekends).

2. The seq is for getting the first records for each employee in each day. Since there can be multiple swipes in a day for an employee seq =1 will only give you first swipe of the employee for the day. the case when will make sure you return the spendtime only for the first records in day and will set others to NULL which is what you wanted in your output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  07:52:03  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by immad


now you r talking
i like to ask question related to this query

FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-05-01','2013-05-31',0,0)b
) t2

what is cross join do and datediff do

and why you use seq=1 what the use of it

CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,


immad uddin ahmed


Now this is what i appreciate Way to go and way to learn!

1. the cross join as you probably know gives you the cartesian product. In this case i used cross join to the UDF which i created which will provide me the calendar details (ie all days between two dates which are passed as arguments). The cross join will make the dates repeat for each employees so that you'll get all employee names repeated for each of the dates. This is needed because you wanted every date reported for a employee between the two dates even when his details are not present in attend table (ie holidays, weekends).

2. The seq is for getting the first records for each employee in each day. Since there can be multiple swipes in a day for an employee seq =1 will only give you first swipe of the employee for the day. the case when will make sure you return the spendtime only for the first records in day and will set others to NULL which is what you wanted in your output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




i under stand it good to understand


immad uddin ahmed

Edited by - immad on 06/18/2013 08:01:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  07:57:27  Show Profile  Reply with Quote
i do mind
as this is not a platform for any personal conversation neither is the subject of discussion personal
The intention behind forums like this and people like us spending so much (unpaid) hours on this is to share our leraning across and benefit maximum number of people. So in best interests of forum and for community its essential we stick to a common international language (English) which all understand and speak

Imagine what would have happened if i had replied to you in my first language which is neither English nor Hindi nor Urdu
So try to share learnings across and let maximum people benefit from it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  07:58:49  Show Profile  Reply with Quote
i get it

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  10:06:06  Show Profile  Reply with Quote
quote:
Originally posted by immad

i get it

immad uddin ahmed


why did you modify your original request?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000