| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-09-08, 04:00
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
explode decilmal number
|
|
gud day to all,
i would like to ask how can i explode decimal number in mysql?
e.g. 1.25, 1.50, 1.75.
i would like to separate the whole number from the decimal.
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 04:07
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,250
|
|
have a look at the floor/round functions
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
07-09-08, 04:26
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
|
thanks, i extracted the whole numbers, but how about the decimal?
i cant find function to extract decimal numbers.
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 05:36
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,250
|
|
original number - integer part = decimal part
mind you does this need to be done at the SQL level, or should it be done in the presentational level. it could be either, depending on what you are planning to do with the data.. if its needed for sorting, grouping or other data manipulation purposes then do it a the SQL level, otherwise Id consider woing it in the presentation level
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
07-09-08, 06:11
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
what do you mean by presentation level?
i think i have to do it in sql.
i have 3 fields
break_hours, reg_hours, idle hours
some break_hours is greater than 1, like 1.5, 1.25, 1.75.
and i would like to add the decimal to reg_hours, not the whole number 1.
first i will split the break_hour from whole to decimal number.
i will add the reg_hours to decimal number and sum the result.
i will place the whole number to idle hours and sum it.
i wil try your suggestions.
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 06:55
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,250
|
|
you dont need to do masses of calcualtions in the SQL layer... often you can do simple mathmatics in the presentation layer (ie what ever is the front end your SQL is supplying.. be that ASP, PHP, VB, Delphi whatever).
granted I would not do this if the information had to be presented in several different areas .. Id do it in a query
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
07-09-08, 07:20
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
|
Originally Posted by healdem
often you can do simple mathmatics in the presentation layer
|
like in this case, where he wants to use SUM?
|
|
07-09-08, 07:27
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
my front end is php,
if i will do the calculations, formulas and operations in php,
that would be too difficult, case like too many fields.
im making a query and put it in php.
i would like to show you my query, but its working, i just need your advice in making a stadard code. its not yet finish, and my final work of art to be done is the separation of whole number from decimal.
Code:
select
q.`date`
,q.last_name
,q.firstname
,q.tl
,q.`group`
,q.`client`
,q.service
,sum(`Total Reg Idle`) as 'Total Reg Idle'
,sum(`Total Reg Meeting`) as 'Total Reg Meeting'
,sum(`Total Reg Orientation`) as 'Total Reg Orientation'
,sum(`Reg Hrs`) as 'Total Reg Hrs'
,sum(`Total OT Idle`) as 'Total OT Idle'
,sum(`OT Meeting`) as 'Total OT Meeting'
,sum(`OT Orientation`) as 'OT Orientation'
,sum(`Over Time`) as 'Total OT Hrs'
,sum(`Total Man Hours`) as 'Total Man Hours'
from(
SELECT
coding_log.`date`,
coding_log.last_name,
`user`.tl,
`user`.firstname,
`user`.`group`,
batch_log.`client`,
batch_log.service,
coalesce(
SUM(
CASE WHEN coding_log.time_start < '16:00'
THEN floor(coding_log.break_hours) ELSE 0 END
),
0) +
coalesce(
SUM(
CASE WHEN coding_log.time_start < '17:00' && coding_log.adjustment = 'Idle (personal)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0) +
coalesce(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'Idle (work related)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0) as 'Total Reg Idle',
time_to_sec(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'meeting'
THEN coding_log.adjustment_time ELSE 0 END
)
)/3600 as 'Total Reg Meeting',
time_to_sec(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'orientation'
THEN coding_log.adjustment_time ELSE 0 END
)
)/3600
as 'Total Reg Orientation',
time_to_sec(
subtime(
SEC_TO_TIME(
SUM(
TIME_TO_SEC(
SUBTIME(
coding_log.time_end,coding_log.time_start
)
)
)
),
sec_to_time(
sum(
if(
coding_log.time_start and coding_log.time_end <= '16:00', '',
if(
coding_log.time_start <= '16:00' and coding_log.time_end >= '16:00',
TIME_TO_SEC(
subtime(
coding_log.time_end,'16:00'
)
),
if(
coding_log.time_start and coding_log.time_end > '16:00',
TIME_TO_SEC(
subtime(
coding_log.time_end, coding_log.time_start
)
),
'')
)
)
)
)
)
)/3600 -
(
(SUM(
CASE WHEN coding_log.time_start < '16:00'
THEN coding_log.break_hours ELSE 0 END
) -
SUM(
CASE WHEN coding_log.time_start < '16:00'
THEN floor(coding_log.break_hours) ELSE 0 END
)) +
coalesce(
SUM(
CASE WHEN coding_log.time_start < '17:00' && coding_log.adjustment = 'Idle (personal)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0) +
coalesce(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'Idle (work related)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0)) -
time_to_sec(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'meeting'
THEN coding_log.adjustment_time ELSE 0 END
)
)/3600 -
time_to_sec(
SUM(
CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'orientation'
THEN coding_log.adjustment_time ELSE 0 END
)
)/3600 as 'Reg Hrs',
coalesce(
SUM(
CASE WHEN coding_log.time_start >= '16:00'
THEN floor(coding_log.break_hours) ELSE 0 END
),
0) +
coalesce(
SUM(
CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'Idle (personal)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0) +
coalesce(
SUM(
CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'Idle (work related)'
THEN time_to_sec(
coding_log.adjustment_time
)/3600 ELSE 0 END
),
0)as 'Total OT Idle',
time_to_sec(
SUM(
CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'meeting'
THEN coding_log.adjustment_time ELSE 0 END
)
)/3600 as 'OT Meeting',
time_to_sec(
sorry, i cant paste the whole query cause the limit of this forum is only 10,000 characters, and my code is 38485 characters, but im on my 1/4 way from my code.
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 07:33
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
homer, have you ever defined a VIEW?
|
|
07-09-08, 07:40
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 08:23
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
yes, that's right, CREATE VIEW
have you ever? because your query is just screaming to be simplified
|
|
07-09-08, 21:32
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
ok, thanks, 
ill try it now, whew, its a long way back again in coding. 
i think view will help me a lot, but i have to figure out how to use it.
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
07-09-08, 23:42
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
thanks!
my code now is
Code:
select
q.`date`
,q.last_name
,q.firstname
,q.tl
,q.`group`
,q.`client`
,q.service
,sum(`Total Reg Idle`) as 'Total Reg Idle'
,sum(`Total Reg Meeting`) as 'Total Reg Meeting'
,sum(`Total Reg Orientation`) as 'Total Reg Orientation'
,sum(`Reg Hrs`) as 'Total Reg Hrs'
,sum(`Total OT Idle`) as 'Total OT Idle'
,sum(`OT Meeting`) as 'Total OT Meeting'
,sum(`OT Orientation`) as 'OT Orientation'
,sum(`Over Time`) as 'Total OT Hrs'
,sum(`Total Man Hours`) as 'Total Man Hours'
from(
SELECT *
FROM coding_view
union
SELECT *
FROM key_entry_view
union
SELECT *
FROM scan_view
union
SELECT *
FROM qc_view) as q
group by
month(q.`date`),
q.last_name
,q.`client`
,q.service
order by
monthname(q.`date`)
,q.last_name;
thanks guys.
however it takes 50seconds to execute.
how am i going to shorten the execution time? i already indexed my tables.
any advice?
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
explode decilmal number
