SQLTeam.com | Weblogs | Forums

Get the Total minutes per week n SQL

#1

Hi Everyone,

Need help here :smile:

I have this table :slightly_smiling_face:

Time In Out TotalMins Per WEEK
Monday, January 01, 2018 7:30 16:30
Tuesday, January 02, 2018 7:30 16:30
Wednesday, January 03, 2018 7:30 16:30
Thursday, January 04, 2018 7:30 16:30
Friday, January 05, 2018 8:01 17:01
2,400
Monday, January 08, 2018 8:07 17:07
Tuesday, January 09, 2018 8:31 17:30
Wednesday, January 10, 2018 7:52 16:52
Thursday, January 11, 2018 7:52 17:30
Friday, January 12, 2018 8:10 17:10
2,437

What I want is to get the total mins per week as shown in my sample.

How I will do this in sql?

Thank you in advance

#2

Please provide:

  • table definition in the form of create table statement
  • sample data in the form of insert statements
  • expected output from the sample data you provide
#3
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Time]) % 7, [Time]) AS Week_Date,
    SUM(DATEDIFF(MINUTE, [In], [Out])) AS Total_Mins
FROM ...
GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Time]) % 7, [Time])
1 Like