Solved

SQL Query to tally cumulative hours

Posted on 2011-09-28
8
260 Views
Last Modified: 2012-05-12
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
Comment
Question by:MonMusc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36770012
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
 

Author Comment

by:MonMusc
ID: 36777668
In that case it would have to use the end time date for the last door time.
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 36789971
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MonMusc
ID: 36806010
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
 

Author Comment

by:MonMusc
ID: 36812730
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
 

Author Closing Comment

by:MonMusc
ID: 36812731
In the short time it took to recieve an answer it was more than I could have hoped for.
0
 

Author Comment

by:MonMusc
ID: 36817507
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36817518
Can you post an example of the data that causes the break, similar to the data you posted above?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question