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 | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/17/2013 :  05:12:55  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

what did you pass as minimum and maximum date values inside function? show your full query used

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






select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1
from (SELECT eid,[date]
FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-05-02','2013-05-08',0,0)b

)t
left join attend_log u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t.[date],
t.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM attend_log t
left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
where t.eid = 17090
order by t.[date], t.[Timein]


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/17/2013 :  05:21:09  Show Profile  Reply with Quote
this is still not what i suggested. I told you to change the order in the join but your query doesnt have it yet!


select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1 
from (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',0,0)b

)t
left join attend_log u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t2.[date],
t2.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM  #temp1 t2 
left join attend_log t
on t.[date] = t2.[date] 
and t.[Timein] = t2.First_Record
where t2.eid = 17090
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/17/2013 :  06:12:30  Show Profile  Reply with Quote
drop table #temp1
select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1
from (SELECT eid,[date]
FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('2013-05-02','2013-05-08',0,0)b
)t
left join attend_log u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t2.[date],
t2.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM #temp1 t2
left join attend_log t
on t.[date] = t2.[date]
and t.[Timein] = t2.First_Record
where t2.eid = 17090
order by t2.[date], t.[Timein]




this query giving me error


Warning: Null value is eliminated by an aggregate or other SET operation.

(7 row(s) affected)



Msg 207, Level 16, State 1, Line 12
Invalid column name 'eid'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'eid'.


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/17/2013 :  06:18:16  Show Profile  Reply with Quote
actually you dont need #temp table at all i gues
wont this be enough?


select
t2.[date],
t2.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',0,0)b
) t2 
left join (select row_number() over (partition by [date] ORDER BY  timein ASC) AS Seq
                  ,sum(DATEDIFF(minute, [Timein], [Timeout])) OVER (PARTITION BY [date]) AS Time_Minutes
                  ,* 
           FROM attend_log) t
on t.[date] = t2.[date] 
and t.Seq = 1
where t2.eid = 17090
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/17/2013 :  07:19:41  Show Profile  Reply with Quote




your query showing me wrong data,wrong spendtime,wrong excess short and only 5 may time in time out null if i select from 1 may to 20 may then sunday dates show me 3 times and its show me null but its giving me time in and time out but actullay sunday is the off day of that employee so how its showing time in time out

your query give me this data


date----------------------------eid-------timein----------------------------timeout--------------------spendtime------excesshsort
2013-05-01 00:00:00.000---17090----2013-06-13 05:44:00.000----2013-06-13 05:44:00.000--21:20:00----12:20:00
2013-05-02 00:00:00.000---17090----2013-06-13 06:42:00.000----2013-06-13 17:26:00.000---08:50:00-----23:50:00
2013-05-03 00:00:00.000---17090---2013-06-13 06:42:00.000----2013-06-13 15:07:00.000----21:41:00----12:41:00
2013-05-04 00:00:00.000--17090----2013-06-13 06:43:00.000----2013-06-13 15:28:00.000----11:48:00-----02:48:00
2013-05-05 00:00:00.000---17090-------------NULL------------------NULL------------------------19:10:00-------------10:10:00
2013-05-06 00:00:00.000---17090-------2013-06-13 06:27:00.000--2013-06-13 14:59:00.000-----04:31:00----19:31:00
2013-05-07 00:00:00.000---17090-------2013-06-13 06:25:00.000--2013-06-13 15:07:00.000-----15:54:00----06:54:00
2013-05-08 00:00:00.000----17090-------2013-06-13 06:29:00.000---2013-06-13 15:06:00.000-----05:13:00----20:13:00
2013-05-09 00:00:00.000----17090------2013-06-13 06:27:00.000----2013-06-13 14:50:00.000----07:58:00----22:58:00
2013-05-10 00:00:00.000-----17090-----2013-06-13 06:02:00.000----2013-06-13 07:09:00.000----12:45:00---03:45:00
2013-05-11 00:00:00.000------17090-----2013-06-13 07:18:00.000---2013-06-13 07:19:00.000----23:25:00----09:35:00
2013-05-12 00:00:00.000------17090-----2013-06-13 06:54:00.000---2013-06-13 14:45:00.000------19:58:00---10:58:00
2013-05-13 00:00:00.000------17090-----2013-06-13 06:47:00.000---2013-06-13 07:29:00.000------04:52:00--19:52:00
2013-05-14 00:00:00.000------17090-----2013-06-13 06:54:00.000---2013-06-13 14:00:00.000------09:11:00---00:11:00
2013-05-15 00:00:00.000------17090-----2013-06-13 06:45:00.000---2013-06-13 07:14:00.000------07:04:00---22:04:00
2013-05-16 00:00:00.000------17090-----2013-06-13 06:47:00.000--2013-06-13 07:38:00.000------05:27:00---20:27:00
2013-05-17 00:00:00.000------17090-----2013-06-13 06:34:00.000--2013-06-13 13:01:00.000------06:36:00---21:36:00
2013-05-18 00:00:00.000-------17090-----2013-06-13 06:43:00.000--2013-06-13 07:37:00.000----08:46:00----23:46:00
2013-05-19 00:00:00.000-------17090-----2013-06-13 06:09:00.000--2013-06-13 06:47:00.000-----18:31:00---09:31:00
2013-05-20 00:00:00.000-------17090-----2013-06-13 06:39:00.000--2013-06-13 15:00:00.000-----12:29:00----03:29:00




this is correct data one problem only no weekdays are coming red line i write it by my self

date------------------------------eid-------------timein-------------------------timeout-------------spendtime----excessshort
2013-05-02 00:00:00.000----17090--2013-06-13 09:20:00.000--2013-06-13 18:17:00.000--08:57:00.000-00:03:00.000
2013-05-03 00:00:00.000--17090--2013-06-13 09:09:00.000--2013-06-13 14:01:00.000--07:08:00.000--01:52:00.000
2013-05-03 00:00:00.000--17090--2013-06-13 15:56:00.000--2013-06-13 18:12:00.000--------NULL---------------NULL
2013-05-04 00:00:00.000--17090--2013-06-13 09:03:00.000--2013-06-13 10:41:00.000----08:53:00.000--00:07:00.000
2013-05-04 00:00:00.000---17090--2013-06-13 10:51:00.000--2013-06-13 12:10:00.000---------------NULL---------NULL
2013-05-04 00:00:00.000-----17090-----2013-06-13 12:15:00.000--2013-06-13 18:11:00.000---------NULL--------NULL
2013-05-05 00:00:00.000-----17090-------NULL----------------------------------NULL------------------NULL-------------NULL



if i select 1 may to 30 may its give me those data who show timein and timeout no week days showing u give me the query but i f i merge with my real query its giving me wrong data i am also doing this but its very complex

immad uddin ahmed

Edited by - immad on 06/17/2013 07:20:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/17/2013 :  09:30:01  Show Profile  Reply with Quote

select
t2.[date],
t2.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',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
and t.Seq = 1
where t2.eid = 17090
order by t2.[date], t.[Timein]


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

Edited by - visakh16 on 06/17/2013 09:31:46
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  00:54:18  Show Profile  Reply with Quote
now its giving me correct data and its giving me off day dates and absent dates but if a employee swap card multiple times then its not giving me the full result

its showing me that result


date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000----07:08:00----01:52:00
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL


the result is green is okay

i want this type of result



date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000---07:08:00--------01:52:00
2013-05-03 00:00:00.000--17090---2013-06-13 15:56:00.000-----2013-06-13 18:12:00.000----NULL-------------NULL
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-04 00:00:00.000--17090----2013-06-13 10:51:00.000----2013-06-13 12:10:00.000----NULL-----NULL
2013-05-04 00:00:00.000--17090----2013-06-13 12:15:00.000----2013-06-13 18:11:00.000----NULL-----NULL
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL





immad uddin ahmed

Edited by - immad on 06/18/2013 00:55:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/18/2013 :  00:59:32  Show Profile  Reply with Quote
thats because of condition Seq=1. Renove it and you'll get the others


select
t2.[date],
t2.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime
FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',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
and t.Seq = 1
where t2.eid = 17090
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 :  01:59:50  Show Profile  Reply with Quote
query is fine but
its giving me that result


date---------------------------eid-----------------timein--------------------timeout---------------spendtime-------excess
2013-05-01 00:00:00.000--17090----------------NULL-----------------------NULL------------------NULL------------NULL
2013-05-02 00:00:00.000--17090--2013-06-13 09:20:00.000--2013-06-13 18:17:00.000--08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090--2013-06-13 09:09:00.000---2013-06-13 14:01:00.000----07:08:00---01:52:00
2013-05-03 00:00:00.000----17090--2013-06-13 15:56:00.000---2013-06-13 18:12:00.000----07:08:00---01:52:00
2013-05-04 00:00:00.000----17090--2013-06-13 09:03:00.000---2013-06-13 10:41:00.000---08:53:00----00:07:00
2013-05-04 00:00:00.000----17090--2013-06-13 10:51:00.000---2013-06-13 12:10:00.000---08:53:00---00:07:00
2013-05-04 00:00:00.000----17090--2013-06-13 12:15:00.000---2013-06-13 18:11:00.000---08:53:00----00:07:00
2013-05-05 00:00:00.000---17090-----------------NULL------------------NULL-------------------------NULL-------NULL


i want this type of result

date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000---07:08:00--------01:52:00
2013-05-03 00:00:00.000--17090---2013-06-13 15:56:00.000-----2013-06-13 18:12:00.000----NULL-------------NULL
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-04 00:00:00.000--17090----2013-06-13 10:51:00.000----2013-06-13 12:10:00.000----NULL-----NULL
2013-05-04 00:00:00.000--17090----2013-06-13 12:15:00.000----2013-06-13 18:11:00.000----NULL-----NULL
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL





if employee swap card multiple times then its spendtime show only in one row

i have a query i did this before but how i merge this query into your query

this is a query

drop table #temp1
select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1 from attend_log
where eid = 17090
group by [date]
GO


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/18/2013 :  02:10:36  Show Profile  Reply with Quote

select
t2.[date],
t2.eid,
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(540 - Time_Minutes), 0), 108)  ELSE NULL END as excesshorttime
FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',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
and t.Seq = 1
where t2.eid = 17090
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 :  02:17:52  Show Profile  Reply with Quote
nope wrong result

date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000----07:08:00----01:52:00
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL


i want this type of result

date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000---07:08:00--------01:52:00
2013-05-03 00:00:00.000--17090---2013-06-13 15:56:00.000-----2013-06-13 18:12:00.000----NULL-------------NULL
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-04 00:00:00.000--17090----2013-06-13 10:51:00.000----2013-06-13 12:10:00.000----NULL-----NULL
2013-05-04 00:00:00.000--17090----2013-06-13 12:15:00.000----2013-06-13 18:11:00.000----NULL-----NULL
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

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

nope wrong result

date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000----07:08:00----01:52:00
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL


i want this type of result

date----------------------------eid--------------timein-------------------timeout-----------------------spend---------excessshort
2013-05-01 00:00:00.000-17090-------------NULL----------------------NULL--------------------------NULL--------------NULL
2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000----2013-06-13 18:17:00.000----08:57:00-----00:03:00
2013-05-03 00:00:00.000--17090---2013-06-13 09:09:00.000-----2013-06-13 14:01:00.000---07:08:00--------01:52:00
2013-05-03 00:00:00.000--17090---2013-06-13 15:56:00.000-----2013-06-13 18:12:00.000----NULL-------------NULL
2013-05-04 00:00:00.000--17090----2013-06-13 09:03:00.000----2013-06-13 10:41:00.000----08:53:00-----00:07:00
2013-05-04 00:00:00.000--17090----2013-06-13 10:51:00.000----2013-06-13 12:10:00.000----NULL-----NULL
2013-05-04 00:00:00.000--17090----2013-06-13 12:15:00.000----2013-06-13 18:11:00.000----NULL-----NULL
2013-05-05 00:00:00.000----17090--------------NULL-------------------NULL---------------------------NULL--------------NULL


immad uddin ahmed


it was a copy paste typo


select
t2.[date],
t2.eid,
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(540 - Time_Minutes), 0), 108)  ELSE NULL END as excesshorttime
FROM (SELECT eid,[date] 
FROM (select distinct eid from attend_log)a 
cross join dbo.calendartable('2013-05-02','2013-05-08',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
and t.Seq = 1
where t2.eid = 17090
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 :  02:22:38  Show Profile  Reply with Quote
Thanks

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

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

Thanks

immad uddin ahmed


welcome

------------------------------------------------------------------------------------------------------
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 :  03:09:38  Show Profile  Reply with Quote
can u give me this type of data when sunday date come its give me 'O' in shift column




this is query

select
t2.[date],
t2.eid,
e.ename,
t4.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 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]



immad uddin ahmed

Edited by - immad on 06/18/2013 03:16:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/18/2013 :  03:36:41  Show Profile  Reply with Quote
How do you expect me to hlep you on this when I not even know whats in your shiftview table? As of now, I see t4.shift used in query so only thing i know is it comes straight from the view but dunno anything on its data

------------------------------------------------------------------------------------------------------
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 :  03:57:20  Show Profile  Reply with Quote
ok i am sory its little bit complex but


this is a shift view


ALTER View [dbo].[ShiftView]
AS
Select
T.SID,
T.CID,
T.BID,
T.Shift,
T.LongName,
T.SType,
T.TimeIn,
T.TimeOut,
T.LTime,
T.HDTime,
t.minute,
CONVERT(DATETIME, DATEADD(SS, ((DATEPART(HH, CONVERT(varchar(30), T.TimeOut - T.TimeIn, 108)) * 3600 + DATEPART(MI,
CONVERT(varchar(30), T.TimeOut - T.TimeIn, 108)) * 60 + DATEPART(SS, CONVERT(varchar(30), T.TimeOut - T.TimeIn, 108))) / 4), '19010101')) AS EarlyArival,
CONVERT(varchar(30), T.TimeOut - T.TimeIn, 108) AS ShiftTimeDiff,
(CASE WHEN DATEPART(hh, T.TimeIn)
> 12 THEN 24 - DATEPART(hh, T.TimeIn) + DATEPART(hh, T.TimeOut) WHEN DATEPART(hh, T.TimeIn) < 12 THEN DATEDIFF(Hour, T.TimeIn, T.TimeOut) END)
AS DutyHours,
(DATEDIFF(ss,T.TimeIn,T.TimeOut)) as TimeDiffInSec

FROM
SHIFT T



and this is general shift data



CID--1
BID--1
SID---12
shift---G
longname---General
stype----null
timein---1/23/2013 9:00:00 AM
timeout---1/23/2013 6:00:00 PM
ltime----1/23/2013 9:16:00 AM
hdtime-----1/23/2013 2:00:00 PM
night----0
minute--540


this is off day shift data


CID--1
BID--1
SID---19
shift---O
longname---OFF DAY
stype----null
timein---NULL
timeout---NULL
ltime----NULL
hdtime-----NULL
night----NULL
minute--NULL


its horizontal but i write in a vertical type data



i hope u under stand

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/18/2013 :  04:01:45  Show Profile  Reply with Quote
ok..then wats the issue? its present as O itself in the shift table for offdays including Sunday. Then how do you think you'll get a different value? what according to you should be the output?
I think current output is correct as per your shift table data.

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

Edited by - visakh16 on 06/18/2013 04:02:45
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 06/18/2013 :  04:16:33  Show Profile  Reply with Quote
yes its correct but its only show G shift in shift column no O in shift column if there is no O then in remarks its say absent if absent then employee cut its salaray thats why i want in O beocouse right now when employee absent. shift column ,null timein null, timeout null spend time, null and excessshort null
same as sunday when sunday come shift column null timein null timeout null spend time time null and excessshort null so how i diffreniate

that i use case that
case when timein is null and time out is null then absent else '' end remarks

but what about off day how i say off day in remarks same data like absent

immad uddin ahmed

Edited by - immad on 06/18/2013 04:17:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/18/2013 :  04:21:10  Show Profile  Reply with Quote
make it like

...
case when timein is null and time out is null and datediff(dd,0,t2.date)%7 < 5 then absent else '' end remarks
..




------------------------------------------------------------------------------------------------------
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 | Next 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