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
5

Hi

I could not get the week using T-SQL
so i hard coded it for demo purposes!!!!!

please click arrow to the left for DROP CREATE Sample Data 
drop table #sampledata 
go 

create table #sampledata
(
time1 varchar(100),
week1 int, 
In1 time , 
Out1 time 
)
go 

insert into #sampledata select 'Monday, January 01, 2018',1,	'7:30','16:30'
insert into #sampledata select 'Tuesday, January 02, 2018',1,	'7:30',	'16:30'	
insert into #sampledata select 'Wednesday, January 03, 2018',1,	'7:30',	'16:30'	
insert into #sampledata select 'Thursday, January 04, 2018',1,	'7:30',	'16:30'
insert into #sampledata select 'Friday, January 05, 2018',1,	'8:01',	'17:01'	
insert into #sampledata select 'Monday, January 08, 2018',2,	'8:07',	'17:07'	
insert into #sampledata select 'Tuesday, January 09, 2018',2,	'8:31',	'17:30'	
insert into #sampledata select 'Wednesday, January 10, 2018',2,	'7:52',	'16:52'	
insert into #sampledata select 'Thursday, January 11, 2018',2,	'7:52',	'17:30'
insert into #sampledata select 'Friday, January 12, 2018',2,	'8:10',	'17:10'


select 'sample data ', * from #sampledata
go

image

please click arrow to the left for SQL .. 
; with cte as 
(
	select 
	   *
	  ,datediff(mi,In1,Out1)  as minutes1 
	  ,week1 as or_by 
	from 
	   #sampledata
) , cte_sum_minutes as 
(
    select 
	    week1
	   ,sum(minutes1) as summin 
	from 
	    cte 
	group by 
	    week1 
) 
select  time1,week1,In1 ,Out1,Null  ,or_by from cte 
union all 
select null  ,week1,null,null,summin,1000  from cte_sum_minutes
order by week1,or_by
go

image
image734×307 7.84 KB

6

Great Idea but it got ya, Scott...
Of course, the way the op didn't post the data makes it hard to tell.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
7

@mherz815 ,

First, help us help you in the future... please post readily consumable data instead of just text.

This will do it for you... remove anything that you don't want. Personally, I'd include the minutes for each day but left that out because you did.

   WITH cte AS
(
 SELECT WeekEndingDate = DATEADD(dd,DATEDIFF(dd,0,CONVERT(DATE,SUBSTRING([TIME],CHARINDEX(',',[TIME])+1,23)))/7*7,4)
,*
,[Minutes] = DATEDIFF(mi,[In],[Out])
   FROM (VALUES --This simulates reading from a table
         ('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') 	
        ,('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') 	
        )TestData([Time],[In],[Out])
)
 SELECT [Time]  = CASE 
                    WHEN GROUPING(WeekEndingDate)=0 AND GROUPING([Time])=0 THEN [Time] 
                    WHEN GROUPING(WeekEndingDate)=0 AND GROUPING([Time])=1 THEN '*WeekEnding '+CONVERT(CHAR(12),WeekEndingDate,107)
                    ELSE '**All Weeks Total'
                  END
        ,[In]   = CASE
                    WHEN GROUPING(WeekEndingDate)=0 AND GROUPING([Time])=0 THEN MAX([IN])
                    ELSE ''
                  END
        ,[Out]  = CASE
                    WHEN GROUPING(WeekEndingDate)=0 AND GROUPING([Time])=0 THEN MAX([Out])
                    ELSE ''
                  END
        ,[Total Minutes per Week] = 
                  CASE
                    WHEN GROUPING(WeekEndingDate)=0 AND GROUPING([Time])=0 THEN ''
                    ELSE LEFT(CONVERT(VARCHAR(10),CONVERT(MONEY,SUM([Minutes])),1),CHARINDEX('.',CONVERT(VARCHAR(10),CONVERT(MONEY,SUM([Minutes])),1))-1)
                  END
   FROM cte
  GROUP BY WeekEndingDate,[Time] WITH ROLLUP
  ORDER BY  GROUPING(WeekEndingDate), WeekEndingDate
           ,GROUPING([Time]),CONVERT(DATE,SUBSTRING([TIME],CHARINDEX(',',[TIME])+1,23))
;

That returns the following including your weekly subtotals and a weekending date and a grand total.

image
image996×543 24.6 KB

8

Thank you everyone for your help :smile: