• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

SQL Query to tally cumulative hours

So, I will start off by showing the data structure and some sample rows that I am working with.  I have a view that I have built a select statement from as follows (I have the @time_start and @time_end because they don't know if they want it to cross day boundries or not such as go from 6pm yestarday to 6am today):

declare @yesterday datetime
declare @today_start varchar(20)
declare @today_end varchar(20)


set @yesterday = DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)
set @time_end = cast(year(getdate()) as varchar) + '-' + right('00'+cast(month(getdate()) as varchar) ,2) + '-' + right('00'+cast(day(getdate()) as varchar),2) + ' 23:59:59'
/*set @time_start = cast(year(@yesterday) as varchar) + '-' + right('00'+cast(month(@yesterday) as varchar) ,2) + '-' + right('00'+cast(day(@yesterday) as varchar),2) + ' 18:00:00'*/
set @time_start = cast(year(getdate()) as varchar) + '-' + right('00'+cast(month(getdate()) as varchar) ,2) + '-' + right('00'+cast(day(getdate()) as varchar),2) + ' 00:00:00'

select WhereName,DateTimeOfTxn,LastName,FirstName from ActivityDataView
where (DateTimeOfTxn between (@time_start) and (@time_end)) and (WhereName Like 'Front Door%')


Here is how the data lays out from the return

Front Door In,2011-09-28 05:09:06.000,Doe,John
Front Door In,2011-09-28 05:11:01.000,Doe2,John
Front Door In,2011-09-28 05:12:01.000,Doe,Jane
Front Door Out,2011-09-28 05:11:06.000,Doe,John
Front Door In,2011-09-28 05:12:15.000,Doe2,Jane
Front Door In,2011-09-28 05:15:05.000,Doe,John
Front Door Out,2011-09-28 11:59:25.000,Doe,John
Front Door Out,2011-09-28 11:59:45.000,Doe2,John
Front Door Out,2011-09-28 12:01:25.000,Doe,Jane
Front Door Out,2011-09-28 12:01:25.000,Doe2,Jane
Front Door In,2011-09-28 12:35:25.000,Doe,John
Front Door In,2011-09-28 12:36:45.000,Doe2,John
Front Door In,2011-09-28 12:39:25.000,Doe,Jane
Front Door In,2011-09-28 12:40:25.000,Doe2,Jane
Front Door Out,2011-09-28 16:35:25.000,Doe,John
Front Door Out,2011-09-28 16:40:45.000,Doe2,John
Front Door Out,2011-09-28 16:41:25.000,Doe,Jane
Front Door Out,2011-09-28 18:40:25.000,Doe2,Jane

Now that I have that, what is wanted is to dump out the Date, LastName, FirstName, TotalHoursOnSite (based on the various In and Out times rounded to the nearest 15 minutes if possible) like so:

2011-09-28,Doe,John,6.5
2011-09-28,Doe2,John,7
2011-09-28,Doe,Jane,7
2011-09-28,Doe2,Jane,6.75

I just can't wrap my head around this one...
Also, this has to run as a stored procedure from a remote program.
0
MonMusc
Asked:
MonMusc
  • 5
  • 3
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
What would you expect to see if there is no "closing" Front Door Out record for a person during the date ranges provided by system user?  Or would this condition never occur.

Thanks!
0
 
MonMuscAuthor Commented:
In that case it would have to use the end time date for the last door time.
0
 
Christopher GordonSenior Developer AnalystCommented:
Posted some T-SQL but there is A LOT going on here.  I've supplied some sample data so you can run and walk through in SSMS.  I hope this helps (or at least serves at a starting point).

I won't be back on until 9ish EST.
declare @myTable table (code nvarchar(40), transaction_date smalldatetime, lastName nvarchar(40), firstname nvarchar(40))

insert into @myTable values ('Front Door In','2011-09-28 05:09:06.000','Doe','John')
insert into @myTable values ('Front Door In','2011-09-28 05:11:01.000','Doe2','John')
insert into @myTable values ('Front Door In','2011-09-28 05:12:01.000','Doe','Jane')
insert into @myTable values ('Front Door Out','2011-09-28 05:11:06.000','Doe','John')
insert into @myTable values ('Front Door In','2011-09-28 05:12:15.000','Doe2','Jane')
insert into @myTable values ('Front Door In','2011-09-28 05:15:05.000','Doe','John')
insert into @myTable values ('Front Door Out','2011-09-28 11:59:25.000','Doe','John')
insert into @myTable values ('Front Door Out','2011-09-28 11:59:25.000','Doe','Jane')
insert into @myTable values ('Front Door Out','2011-09-28 9:39:25.000','Doe2','Jane')

insert into @myTable values ('Front Door In','2011-09-28 9:39:25.000','Simpson','Homer')
insert into @myTable values ('Front Door Out','2011-09-28 10:39:25.000','Simpson','Homer')
insert into @myTable values ('Front Door In','2011-09-28 13:39:25.000','Simpson','Homer')
insert into @myTable values ('Front Door Out','2011-09-28 16:39:25.000','Simpson','Homer')


--insert into @myTable values ('Front Door Out','2011-09-28 10:32:25.000','Doe2','John')

--populate this CTE with your current t-sql.  You'll need to rename fields throughout the code
;with Detail_Records as
(
	SELECT	*

		, ROW_NUMBER() over (Partition By firstName, LastName Order By Transaction_Date) as Instance_Id

	FROM	@myTable
)

--match the in record with out record
, Detail_Records_With_Time as
(
select	
		Detail_Records.lastName
	,	Detail_Records.firstname
	
	,	Detail_Records.code							as	In_Code
	,	Detail_Records.transaction_date				as	In_Time

	,	Detail_Records_Next.code					as	Out_Code
	,	Detail_Records_Next.transaction_date		as	Out_Time

	,	DATEDIFF(MINUTE, Detail_Records.transaction_date, isnull(Detail_Records_Next.transaction_date, dateadd(minute, 1439, cast(cast(detail_records.transaction_date as date) as smalldatetime)))) as Transaction_Difference_In_Minutes

	,	Detail_Records.code
	
from	Detail_Records

left outer join Detail_Records Detail_Records_Next on
		Detail_Records.lastName = Detail_Records_Next.lastName

	and	Detail_Records.firstname = Detail_Records_Next.firstName

	and	(Detail_Records.Instance_Id + 1) = Detail_Records_Next.Instance_Id
)

-- get summaries by individual
,	Totals_By_Individual as
(
	select		firstname
			,	lastName
			,	SUM(Transaction_Difference_In_Minutes) as Total_Time_In_Minutes
			
	from	Detail_Records_With_Time
	
	where	In_Code = 'Front Door In'

	group by firstname, lastName
)

-- get formatted hours and minutes by individual
select		*

		,	CAST((Total_Time_In_Minutes / 60) AS VARCHAR(8)) + ':' + CAST((Total_Time_In_Minutes % 60) AS VARCHAR(2)) Formatted_Time

from	Totals_By_Individual

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MonMuscAuthor Commented:
Well, this didn't work (Rownumber() is not a recognized function) then I started checking and found the friggen database is SQL2000!  So, since this is a vendor database I will have to write a quick procedure to transfer the data from that database into my SQL2008R2 server.  Urgghhhhhhhhhh.
I will do that and then get back with you on this.
0
 
MonMuscAuthor Commented:
Ok, after linking the 2000 server to my 2008R2 server and making this a stored procedure (had a little issue with the CTE but was just my brain freeze) this works way better than I had ever hoped!  I'm not sure what deep crevace you pulled this from (or how the heck you came up with all this craziness) but the points are so not enough to thank you for this!
0
 
MonMuscAuthor Commented:
In the short time it took to recieve an answer it was more than I could have hoped for.
0
 
MonMuscAuthor Commented:
Hmm, the only problem that I need to figure out is for those 3rd shift folks, it seems to break a little.  99% there in one shot, still very impressed with your solution!
0
 
Christopher GordonSenior Developer AnalystCommented:
Can you post an example of the data that causes the break, similar to the data you posted above?
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now