Solved

SQL Query to tally cumulative hours

Posted on 2011-09-28
8
238 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now