Solved

SQL Query to tally cumulative hours

Posted on 2011-09-28
8
241 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
replication - alerts? 4 30
TSQL previous 5 22
Where to download and how to install sqldmo.dll 5 27
Sql Query Datatype 2 14
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

932 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

11 Experts available now in Live!

Get 1:1 Help Now