Solved

SQL Query to tally cumulative hours

Posted on 2011-09-28
8
253 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 70
SQL Improvement  ( Speed) 14 33
query optimization 6 15
too many installs coming along with SQL 2016? 1 18
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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