StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMerging to Stored Procedures, one to many
text
Body
copied!<p>so I am not sure what I want can be done but here goes... I have two stored proceedures... one counts the amount of free time a person has to work per day and puts the record set in a temp table for speed... the results are as, fields being Date, PersonID, Hours, Minutes. </p> <pre><code>2013-04-03 10000305 8 30 2013-05-03 10000305 8 30 </code></pre> <p>Within their working day they complete calls, each call has a duration recorded. My second SP works out the time they actually worked during that day. Results are as follows, fields are the same, Date, Person, Hours, Minutes. </p> <pre><code>2013-04-25 10000305 7 20 2013-04-26 10000305 11 7 </code></pre> <p>The problem: I have to call two SP's and then organise. What I was hoping was that I could put all into one SP and Just have a summary which would look like the below. The problem is the second SP counts many many records during one day to get its Sum, whereas the first SP is just one day and a duration. When I tried to add together my results went crazy and bought back millions of records. Is there a simple way or is it not possible?</p> <pre><code>**Date** **User** **Hours** **Minutes** **Actual Hours** **Act Mins** 2013-04-25 10000305 8 30 11 7 </code></pre> <p>Here is a code snippet, both are very similar in how they work</p> <pre><code>CREATE TABLE #TotalUsedTime (PersonID Int, [Date] DateTime, Office Int, [Hours] Int, [Minutes] Int) DECLARE @GetUsedTime NVARCHAR(MAX); SET @GetUsedTime = N' Select PersonID, [Date], Office, TotalUsedTime / 3600 AS Hours, (TotalUsedTime % 3600) / 60 AS Minutes From ( Select PersonID, [Date], Office, DateDiff(Second, Starttime, End) AS TotalUsedTime From Calls Where Completed IS NOT NULL) x ' INSERT #TotalUsedTime EXEC SP_Executesql @GetUsedTime; Select Cast([Date] as date) Date, #TotalUsedTime.PersonID, Cast(sum([hours] * 60 + [minutes])/60 as int) As Hours, Sum([hours] * 60 + [minutes]) - 60 * cast(sum([hours] * 60 + [minutes])/60 as int) As Minutes, People.Fullname From #TotalUsedTime Inner Join People on People.PersonID = #TotalUsedTime.PersonID Where #TotalUsedTime.[Date] Between @StartDate and @EndDate and Office = @Office and People.PersonID = @PersonID Group by cast([DATE] as Date), #TotalUsedTime.PersonID, People.FullName] End </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload