March 30, 2009 at 11:11 pm
i was doing a report using crystal report xi wherein i am computing for the working time rendered by each technicians. i have a table named OSCL and into this table, I have a field name u_techInTime and u_techOutTime wherein the value is in number format. I wanted to display in my report the working time using this formula:
{OSCL.U_TechOutTime}-{OSCL.U_TechInTime}
but the problem is u_techouttime and u_techintime is not in time format hh:mm.
I was been given with the ff solution:
SELECT DATEADD(minute, (u_techOutTime % 100 - u_techintime % 100),
DATEADD(hour, (u_techOutTime / 100 - u_techintime / 100),
CASE WHEN (u_techOutTime < u_techintime) THEN 1 ELSE 0 END)) AS ElapsedTime
FROM (
SELECT 2000 AS u_techintime, 2130 AS u_techOutTime UNION ALL
SELECT 2355 AS u_techintime, 0045 AS u_techOutTime UNION ALL
SELECT 0000 AS u_techintime, 0300 AS u_techOutTime UNION ALL
SELECT 1200 AS u_techintime, 1503 AS u_techOutTime UNION ALL
SELECT 0731 AS u_techintime, 0830 AS u_techOutTime
) OSCL
but on the statement CASE WHEN i encountered problem like a number, currency amount, date, time, string is expected here.
How will I going to eliminate this problem so that i could come up with the right solution. thanks
March 31, 2009 at 7:29 am
Can you post the table DDL and some test data as recommended in the first link in my signature?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 1, 2009 at 6:33 am
kylecm2001,
That was my solution from this thread
http://www.sqlservercentral.com/Forums/Topic685851-338-1.aspx
If you were having problems implementing my solution why didn't you reply to the original thread rather than create another here? I've checked all your posts and they seem to be about the same or very similar problems.
http://www.sqlservercentral.com/Forums/Topic687692-149-1.aspx
http://www.sqlservercentral.com/Forums/Topic686733-149-1.aspx
http://www.sqlservercentral.com/Forums/Topic686722-149-1.aspx
http://www.sqlservercentral.com/Forums/Topic685851-338-1.aspx
Multiple posting and posting out of context just wastes time as multiple people will ask you the same clarification questions.
From the screenshots in this thread
http://www.sqlservercentral.com/Forums/Topic686722-149-1.aspx
it looks like there are separate columns for date and time in the OSCL table, where the date columns are stored as datetime data types and the time columns stored as numeric (? int) data types. Therefore, I'm guessing that the table structure is something like this...
CREATE TABLE OSCL (
...
u_techInDate datetime,
u_techInTime int,
u_techOutDate datetime,
u_techOutTime int,
...
)
Please can you confirm the correct columns and data types of your OSCL table.
In my response to your original post I had asssumed that the table stored times only as you made no mention of the date columns. I stated the implications of this assumption in my response, i.e. that there would be no time differences greater than 24 hours. If I had known that there were date columns, I would have given you a different solution.
On a point of database design, it is usually much better to store dates and times together in a single datetime column in your underlying table rather than split them into separate date and time columns. I realise that this not where you are at in terms of the data you are trying to report on, and changing the table structure may not be an otion for you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply