Solved

morning daily and night calculation

Posted on 2012-03-15
4
239 Views
Last Modified: 2012-03-18
Hi
please have a look to my query
$result=mysql_query( "SELECT a.hr, 
      SUM(case when b.pulse_channel = 1 then b.unit_qty else 0 end) as name1,
      SUM(case when b.pulse_channel = 2 then b.unit_qty else 0 end) as name2,
      SUM(case when b.pulse_channel = 3 then b.unit_qty else 0 end) as name3,
      SUM(case when b.pulse_channel = 4 then b.unit_qty else 0 end) as name4,
      SUM(b.unit_qty) as Total
from (
	select 0  as hr 
	union select 1
	union select 2 
	union select 3
	union select 4 
	union select 5 
	union select 6
	union select 7
	union select 8
	union select 9
	union select 10
	union select 11
	union select 12
	union select 13
	union select 14
	union select 15
	union select 16
	union select 17
	union select 18
	union select 19
	union select 20
	union select 21
	union select 22
	union select 23) a 
left join count_transactions b on a.hr = Hour(b.tran_date)
WHERE DATE(b.tran_date) BETWEEN '$dateb' AND '$dateb' AND unit_serial=$netw
GROUP BY a.hr" ) or die("SELECT Error: ".mysql_error());

Open in new window

how i can calculate unit_qty for morning (from 0 to 6) then daily from 7pm to 19:59 then night from 20 to 23:59pm
i hope you inderstand my query
0
Comment
Question by:asaidi
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37727621
change the "a.hr" in the select to:

case when a.hr between 0 and 6 then 'morning'
        when a.hr between 7 and 19 then 'daily'
        when a.hr between 20 and 23 then 'night'
end as part_of_day

Also change it in the group by, except without the "as part_of_day" of course.
0
 

Author Comment

by:asaidi
ID: 37728944
Hi
thanks for your answer
is this correct:i cannot get in my html table rowe from 0 to 23 please check a picture of my report
$result=mysql_query( "SELECT 
                    case when a.hr between 0 and 6 then 'morning'
                         when a.hr between 6 and 19 then 'daily'
                         when a.hr between 19 and 23 then 'night'
                         end as part_of_day,
              
      SUM(case when b.pulse_channel = 1 then b.unit_qty else 0 end) as name1,
      SUM(case when b.pulse_channel = 2 then b.unit_qty else 0 end) as name2,
      SUM(case when b.pulse_channel = 3 then b.unit_qty else 0 end) as name3,
      SUM(case when b.pulse_channel = 4 then b.unit_qty else 0 end) as name4,
      SUM(b.unit_qty) as Total
from (
	select 0  as hr 
	union select 1
	union select 2 
	union select 3
	union select 4 
	union select 5 
	union select 6
	union select 7
	union select 8
	union select 9
	union select 10
	union select 11
	union select 12
	union select 13
	union select 14
	union select 15
	union select 16
	union select 17
	union select 18
	union select 19
	union select 20
	union select 21
	union select 22
	union select 23) a 
 
left join count_transactions b on a.hr = Hour(b.tran_date)
WHERE DATE(b.tran_date) BETWEEN '$dateb' AND '$dateb' AND unit_serial=$netw
GROUP BY part_of_day" ) or die("SELECT Error: ".mysql_error());

Open in new window

0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37729069
it has to be group by case ..... end not group by part_of_day
0
 

Author Comment

by:asaidi
ID: 37729788
Hi
i did that
GROUP BY case when a.hr between 0 and 6 then 'morning'
                         when a.hr between 6 and 19 then 'daily'
                         when a.hr between 19 and 23 then 'night'
                         end" ) or die("SELECT Error: ".mysql_error());
still show me
0:00
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

679 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