Working out a total time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Working out a total time

Author

Topic

stevelalor
Starting Member

4 Posts

Posted - 2007-08-09 : 09:06:42

Hi All,
I'm trying to return a column which is the time added up as a total time. Its a little bit tricky because I have had to use a function to calculate individual line rows.

This SQL statement is used to send to Crystal Reports. Perhaps someone has a better solution? I can't calculate the total time in SQL because this is all passed through as a string.

Hopefully the code below makes sense.

Many thanks

Steve

====================== CODE STARTS ============================

CREATE PROCEDURE spTableHours
(
@SmNumber int,
@FromDate datetime = NULL,
@ToDate datetime = NULL

)
AS
BEGIN

SELECT R1.ReportId,
CASE WHEN R1.ReportTypeId = 1 THEN
RT1.Prefix + RIGHT('00000' + RTRIM(CONVERT(varchar(10), R1.ReportNumber)), 3)
ELSE
RT1.Prefix + RIGHT('00000' + RTRIM(CONVERT(varchar(10), R1.ReportNumber)), 2)
END AS ReportReferenceNumber,
R1.SubjectHeading,
R1.DateReceived,
R1.DateRequired,
R1.DateReturned,
PC1.Name AS PriorityCategory,
dbo.fTotalTimeToDate(R1.ReportId) As TIME

-- I WOULD LIKE TO ADD A TOTAL HOURS FOR ALL THE REPORTS HERE, IT DOESN'T MATTER THAT ALL ROWS WILL HAVE THE SAME VALUE, ITS ONLY FOR A REPORT AND WILL ONLY BE DISPLAYED ONCE IN THE REPORT FOOTER.

FROM Reports R1
INNER JOIN ReportTypes RT1 ON R1.ReportTypeId = RT1.ReportTypeId
LEFT OUTER JOIN PriorityCategories PC1 ON R1.PriorityCategoryId = PC1.PriorityCategoryId
WHERE R1.SmNumber = @SmNumber AND
DATEDIFF(dd, ISNULL(@FromDate, R1.DateReceived), R1.DateReceived) >= 0 AND
DATEDIFF(dd, ISNULL(@ToDate, R1.DateReceived), R1.DateReceived) <= 0 AND
R1.ReportTypeId = 1 --OR R1.ReportTypeId = 2

ORDER BY R1.DateReceived

END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 09:26:06

Simply calculate your total in Crystal reports, in the footer. It's very easy to do and that's where it should be done, and it will be much more efficient and make your sql much shorter.

Do you know how to add totals to a group footer in Crystal? If not, let me know. Totals, subtotals and footers are a key to writing any reports using any reporting tool.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-09 : 09:28:25

Save your query results in a temp table, then sum the hours in the temp table, and combine the two to return to the client.

CODO ERGO SUM

stevelalor
Starting Member

4 Posts

Posted - 2007-08-09 : 09:44:26

Hi Jeff,

Yes I know how to add totals to a report footer, I can sum up an integer but this isn't an int. It is classed as a string when I pass it though.

I'm not able to sum a string, all I can do is count strings, this is why I can't use crystal.

On thing I have thought I may be able to do is use the VB code in crystal to strip the hours mins and seconds out of each string, then sum these up?

I'm not too sure how to do this in Crystal though. Any ideas?

Many thanks

Steve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-09 : 10:11:56

You can use a formula field to extract hour, minute and second informations and convert them to number before summing up

Suppose you pass 12:12:45

and you want to show result as hours then you can use a formula field having the code


tonumber(mid({column},1,2))
+tonumber(mid({column},4,2))/60.0+
tonumber(mid({column},7,2))/3600.0

And add that formula field to the section you want

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 11:59:50

If you are adding up and returning a total number of hours, just return an Hours value -- as an int -- per row in your results. Then simply sum up the Hours values in your report and there's your total. Format into "X Days Y Hours" or whatever you need as necessary in your report using some basic math.

Keep it simple. If you need to do math, always return numbers, not strings. If you need to total up dates or times, keep them in the proper data type, don't convert them to strings.

Can you give some examples of what dbo.fTotalTimeToDate(R1.ReportId) returns? And how you would like those values to be totaled? If you can simply return an int from this function, your job is easy. If you can't, then you should review your requirements.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources