YVS1102 YVS1102 - 3 months ago 10
SQL Question

Sum data per week SQL query

I'm trying to sum

alias
columns. I'm bad in English, so I will make a list about what I want to do.


  1. There is an attendance in 2 weeks

  2. If the employee have working more than
    08:00 hours
    in a week he will get
    RO
    (can multiple)



For now I'm able to show results with the extratime that employees have in a day.

Here is my query

select
a.Nip, b.FullName, c.attendancedate, c.inTime, c.OutTime,
DATEPART(wk, c.attendanceDate) week,
case
when DATEADD(HOUR, -8, OutTime) <= InTime then '00:00'
else CONVERT(VARCHAR(5),DATEADD(HOUR, -8, OutTime - InTime), 108)
end AS total
from
DinasHoDetail a
left join
Employee b on a.Nip = b.Nip
left join
DinasHoHeader d on a.KodeDinasHeader = d.KodeDinasHeader
left join
attendance c on a.Nip = c.Nip and attendancedate between d.startdate and d.enddate


And here is the result

enter image description here

So can I add one more columns after total called with
ro
. The ro will contain a value from
total/8 hours group by week & Nip


From week 36 and Nip 1502427 here is the example

02:00 + 02:00 + 02:00 / 8 hours


I'm sorry for my bad english.

I'm really want to create my table in
Fiddle
. Every time I run the query I always get an error

Answer

GROUP BY and date functions to calculate RO for every Nip and weekday

select a.Nip,b.FullName,DATEPART( wk, c.attendanceDate) week, 
   sum(case 
      when DATEADD(HOUR, -8, OutTime) <=InTime then 0 
      else DATEDIFF(HOUR, InTime, OutTime) - 8
      end)/8 AS RO  
from DinasHoDetail a 
left join Employee b on a.Nip = b.Nip
left join DinasHoHeader d on a.KodeDinasHeader = d.KodeDinasHeader
left join attendance c on a.Nip =c.Nip and attendancedate 
between d.startdate and d.enddate
group by a.Nip,b.FullName,DATEPART( wk, c.attendanceDate)