Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts

    Sales by hour per day

    EDIT:
    NEVERMIND I FIGURED IT OUT. It keeps pulling the day for that month not that day for the year. I got it now.

    Okay so I have managed to do sales per month in a year, per week in a year, per day per week in a year. Now I am trying to figure out how I can do this by sales by hour per day for this year. This is what I have. But when I run the query it only returns 31 rows.
    select
    <code>
    IfNull(Day,'Totals') Day,
    Format( Hour1,2)as Hour1,
    Format( Hour2,2)as Hour2,
    Format( Hour3,2)as Hour3,
    Format( Hour4,2)as Hour4,
    Format( Hour5,2)as Hour5,
    Format( Hour6,2)as Hour6,
    Format( Hour7,2)as Hour7,
    Format( Hour8,2)as Hour8,
    Format( Hour9,2)as Hour9,
    Format( Hour10,2)as Hour10,
    Format( Hour11,2)as Hour11,
    Format( Hour12,2)as Hour12,
    Format( Hour13,2)as Hour13,
    Format( Hour14,2)as Hour14,
    Format( Hour15,2)as Hour15,
    Format( Hour16,2)as Hour16,
    Format( Hour17,2)as Hour17,
    Format( Hour18,2)as Hour18,
    Format( Hour19,2)as Hour19,
    Format( Hour20,2)as Hour20,
    Format( Hour21,2)as Hour21,
    Format( Hour22,2)as Hour22,
    Format( Hour23,2)as Hour23,
    Format( Hour24,2)as Hour24,
    Format(Daily,2) as 'Daily Total'
    from (
    select
    DAY(FILL_DATE) AS 'Day',
    sum(if(HOUR(FILL_TIME)= '1' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour1,
    sum(if(HOUR(FILL_TIME)= '2' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour2,
    sum(if(HOUR(FILL_TIME)= '3' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour3,
    sum(if(HOUR(FILL_TIME)= '4' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour4,
    sum(if(HOUR(FILL_TIME)= '5' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour5,
    sum(if(HOUR(FILL_TIME)= '6' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour6,
    sum(if(HOUR(FILL_TIME)= '7' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour7,
    sum(if(HOUR(FILL_TIME)= '8' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour8,
    sum(if(HOUR(FILL_TIME)= '9' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour9,
    sum(if(HOUR(FILL_TIME)= '10' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour10,
    sum(if(HOUR(FILL_TIME)= '11' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour11,
    sum(if(HOUR(FILL_TIME)= '12' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour12,
    sum(if(HOUR(FILL_TIME)= '13' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour13,
    sum(if(HOUR(FILL_TIME)= '14' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour14,
    sum(if(HOUR(FILL_TIME)= '15' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour15,
    sum(if(HOUR(FILL_TIME)= '16' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour16,
    sum(if(HOUR(FILL_TIME)= '17' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour17,
    sum(if(HOUR(FILL_TIME)= '18' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour18,
    sum(if(HOUR(FILL_TIME)= '19' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour19,
    sum(if(HOUR(FILL_TIME)= '20' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour20,
    sum(if(HOUR(FILL_TIME)= '21' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour21,
    sum(if(HOUR(FILL_TIME)= '22' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour22,
    sum(if(HOUR(FILL_TIME)= '23' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour23,
    sum(if(HOUR(FILL_TIME)= '24' , IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`), 0)) as Hour24,
    ROUND(SUM(IF(`formula`.`name` LIKE 'MELOXICAM%',0, `Rxfill`.`INSURANCE_PAY`)),2) AS Daily
    FROM `pharma_version1`.`doctor` AS `Doctor` inner JOIN `pharma_version1`.`sales_person` AS `salesperson` ON (`Doctor`.`SALES_PERSON_ID` = `salesperson`.`id`) inner JOIN `pharma_version1`.`rxmain` AS `main` ON (`Doctor`.`id` = `main`.`DOCTOR_ID`) LEFT OUTER JOIN `pharma_version1`.`rxfill` AS `Rxfill` ON (`main`.`id` = `Rxfill`.`RXMAIN_ID`) inner JOIN `pharma_version1`.`formula` AS `formula` ON (`formula`.`id` = `main`.`FORMULA_ID`) inner JOIN `pharma_version1`.`insurance` AS `insurance` ON (`insurance`.`id` = `Rxfill`.`INSURANCE_ID`) WHERE FILL_DATE IS NOT NULL AND main.NOT_FILLED = 'F'
    group by Day
    with rollup
    ) sums;
    </code>
    Last edited by Chris Hick; 11-06-2013 at 05:10 AM. Reason: I figured it out.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •