sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 03:05:35

i want to take a sum of time just like this

time
09:38:00
10:41:00
08:55:00
09:43:00
07:36:00
07:03:00
09:45:00
---------
63:21:00 Result
---------

time datatype is datetime

Thanks for the help

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 03:14:45

[code]
SELECT CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
FROM
(
SELECT DATEADD(mi,SUM(DATEDIFF(mi,0,time)),0) AS AggTimedate
FROM table
)t
[/code]

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 04:25:32

SELECT
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
FROM
(
SELECT
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log where eid=17090 and date >'20130101' and date <'20130131'
)t

i want to show eid column too

eid-----------result
17090--------163:54

immad uddin ahmed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 04:27:09

[code]SELECT eid,
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
FROM
(
SELECT eid,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log where eid=17090 and date >'20130101' and date <'20130131'
group by eid
)t[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:30:20

quote:
Originally posted by immad

SELECT
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
FROM
(
SELECT
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log where eid=17090 and date >'20130101' and date <'20130131'
)t

i want to show eid column too

eid-----------result
17090--------163:54

immad uddin ahmed

With 100 + posts do you mean you still dont know how to add an additional column also to the existing resultset?

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 04:47:43

quote:
Originally posted by visakh16

quote:
Originally posted by immad

SELECT
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Result
FROM
(
SELECT
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log where eid=17090 and date >'20130101' and date <'20130131'
)t

i want to show eid column too

eid-----------result
17090--------163:54

immad uddin ahmed

With 100 + posts do you mean you still dont know how to add an additional column also to the existing resultset?

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

i am doing this

SELECT
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Spendtime
FROM
(
SELECT
g.eid,
e.ename,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log g
left join employee e on g.eid=e.eid
where g.eid=17090 and date >'20130101' and date <'20130131'
group by g.eid,e.ename
)t

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:50:08

without putting the required column in the final select how do you think you'll get it displayed in the resultset?


SELECT eid,
ename, 
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS Spendtime
FROM
(
SELECT 
g.eid,
e.ename,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log g 
left join employee e on g.eid=e.eid
where g.eid=17090 and date >'20130101' and date <'20130131'
group by g.eid,e.ename
)t

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 06:22:22

SELECT
EID,
NAME,
Late,
Halfday,
Absent,
OFFDAY,
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME
FROM
(
SELECT
g.EID,
e.ename [NAME],
sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,
sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as HALFDAY,
sum(case when g.Remarks = 'ABSENT' AND G.TIMEIN IS NULL AND G.TIMEOUT IS NULL then 1 else 0 end) as Absent,
sum(case when g.Remarks = 'OFF DAY' then 1 else 0 end) as OFFDAY,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM attend_log g
left join employee e on g.eid = e.eid
where g.eid=17090 and date >'20130101' and date <'20130131'
group by g.eid,e.ename
)t

ITS GIVE ME THIS RESULT

EID--------NAME--------LATE-HALFDAY---ABSENT---OFFDAY--SPENDTIME
17090------ABCD----------6-----2------------0----------0----------163:54

ACTUAL RESULT IS THIS

EID---------NAME--------LATE-HALFDAY-ABSENT---OFFDAY--SPENDTIME
17090------ABCD----------6------2-----------6----------4---------163:54

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 06:32:11

Cross check once whether those case conditions satisfied or not in the specified duration (date>= '20130101' and date <='20130131' ) -- May be date condition is inclusive

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 06:36:10

how do you expect us to find the issue when we dont have any idea of what your rules are for determining ABSENT? As of now you're trying to take it from attend_log which i'm not sure will have details for absent days.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-25 : 06:48:18

post some sample data from attend_log for 17090, inclusive of the ABSENT & OFFDAY records

KH
[spoiler]Time is always against us[/spoiler]

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 06:52:59

BUT ITS NOT UPDATE ABSENT AND OFFDAY ONLY UPDATE LATES AND HALFDAY
BECOUSE ABSENT AND OFF DAY ROWS NOT INSERT IN ATTEND_LOG TABLE

ALTER procedure [dbo].[AT]
(
@empid nvarchar(50),
@department nvarchar(50),
@from datetime,
@to datetime
)
as
begin

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

select
E.CID,
E.BID,
t2.[date],
t.ebid,
t2.Eid,
e.ename Employeename,
case when datediff(dd,0,t2.date)%7 < 6 then t4.shift else 'O' end as shift,
d.name Designation,
t5.Dname Department,
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,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) as excesshorttime,
case when (t4.minute - Time_Minutes) > 0 then 'Short'
when (t4.minute - Time_Minutes) < 0 then 'Excess'
else NULL end as ExcessShort,

case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY'
ELSE ''
END AS Remarks,

case when t.BID = 2 and t.EBID = 1 then 'ITL 2'
when t.BID = 1 and t.EBID = 2 then 'ITL 1'
else ''
end
Comments

FROM (SELECT eid,[date]
FROM (select distinct eid from attend_log)a
cross join dbo.calendartable(@from,@to,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 (@empid) 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
where t5.did = @department or t2.eid=@empid
order by t2.[date], t.[Timein]

update
ATTEND_LOG
set

excessshort =

case when (t4.minute - Time_Minutes) > 0 Then ' ' else ' ' end
+ CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108)

,
SpendTime =
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108)
,
remarks =
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY'
ELSE ''
END

from

ATTEND_LOG t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) 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
where t.eid=@empid
end

immad uddin ahmed

immad
Posting Yak Master

230 Posts

Posted - 2013-06-25 : 07:12:17

SELECT
EID,
LATE,
[HALF DAY],
ABSENT,
[OFF DAY],
[EXCESS / SHORT],
ExcessShort,
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS [SPEND TIME]
FROM
(
SELECT
t.EID,
sum(case when t.Remarks = 'LATE' then 1 else 0 end) as LATE,
sum(case when t.Remarks = 'HALF DAY' then 1 else 0 end) as [HALF DAY],
case when datediff(dd,'20130101','20130131')%7 < 6 then 1 else 0 end as [OFF DAY],
case when datediff(dd,'20130101','20130131')%7 < 6 then 1 else 0 end as Absent,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate,
convert(Varchar(10),DATEADD(hh,SUM(DATEDIFF(mi,0,excessshort)),0),108)AS [EXCESS / SHORT],
case when (540 - 560) > 0 then 'Short'
when (540 - 560) < 0 then 'Excess'
else NULL end as ExcessShort

FROM ATTEND_LOG t
left join employee e on e.eid = t.eid
LEFT OUTER JOIN FRoaster (17074) 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
where t.eid=17074 and T.date >'20130101' and T.date <'20130131'
group by t.eid
)t

eid----------late--halfday--absent--offday----excess/short-excessshort------spendtime
17074------1------1-------1--------1----------17:00:00------Excess------204:35

i have a problem absent and off day i not showing correctly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 06:44:39

Dont expect any responses until you explain us clearly with some sample data on how your table data are and on what basis you want Absent and offday value to come.
Nobody will be able to make out your exact requirement from what you posted so far!

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-27 : 07:16:56

just copt nad paste it into sql server
create TABLE atend_log
(
eid int,
date datetime,
timein datetime,
timeout datetime,
spendtime datetime,
excessshort datetime,
remarks varchar(50)
)

insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-01 00:00:00.000','2013-06-19 08:39:00.000','2013-06-19 18:04:00.000','1900-01-01 09:25:00.000','1900-01-01 00:25:00.000','Late')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 08:42:00.000','2013-06-19 09:56:00.000','1900-01-01 08:50:00.000','1900-01-01 00:10:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 10:09:00.000','2013-06-19 12:23:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 12:32:00.000','2013-06-19 13:14:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 13:30:00.000','2013-06-19 18:10:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-03 00:00:00.000','2013-06-19 08:40:00.000','2013-06-19 17:55:00.000','1900-01-01 09:15:00.000','1900-01-01 00:15:00.000','Halfday')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 08:32:00.000','2013-06-19 10:53:00.000','1900-01-01 08:45:00.000','1900-01-01 00:15:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 11:09:00.000','2013-06-19 12:57:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:06:00.000','2013-06-19 13:18:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:45:00.000','2013-06-19 18:09:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-05 00:00:00.000','2013-06-19 08:47:00.000','2013-06-19 18:01:00.000','1900-01-01 09:14:00.000','1900-01-01 00:14:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-08 00:00:00.000','2013-06-19 08:35:00.000','2013-06-19 18:29:00.000','1900-01-01 09:54:00.000','1900-01-01 00:54:00.000','')

u cant see that jan 6 and jan 7 data
becouse jan 6 is sunday and jan 7 is employee is absent

i want to take a jan 6 and jan 7 data i try it but its give me wrong result
i hope u get it what u want

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 07:23:55

so where do you store those absent days? or do you atleast have a calendar table?

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-27 : 07:41:53

yes i have a calendar table and in this calendar table i have all dates of from january 1 2013 to december 31 2013
this calendar table name is calendar
i dont store absent days and off days

immad uddin ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources