SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    New Castle, PA
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    giant query question

    ok, I got tons of useful help on this board with my last problem. Much thanks to those who led me in the right direction

    Here's my new issue:

    I have a query that returns all total hours worked by our techs in a certain day, if that total falls below the minimum hours needed, an e-mail gets generated to the tech. I have a query below that returns everything I need, but I want to know if there is a way for me to loop through each user, regardless if they have any hours for the day or not, and pass their user ID into this query. I can do it with PHP, but I don't want to run the query X amount of times to get each result. The query is below, it works and returns everything I need, but only if a technician has time entered for the day. Is there a way to encapsulate this query inside of another that simply selects the technician ID and passes it into the query below?

    Code:
    select hours,userN,daily,tEmail
    from(
    select (totalBillable - daily) as hours
                 ,totalBillable
                 ,userN
                ,daily
                ,tEmail
    from(
    select IFNULL(sumtot,0)+IFNULL(sumgen,0) + IFNULL(sumgenNB,0)+IFNULL(sumtotNB,0) as totalBillable,
                 userN,
                 daily,
                 tEmail
     from(
    select round(sum(tot),2) as sumtot
         , round(sum(gen),2) as sumgen
         ,round(sum(genNB),2) as sumgenNB
         ,round(sum(totNB),2) as sumtotNB
         , userN
         ,daily
         ,tEmail
          from (
            select sum(tech_notes.time_spent)          as tot 
                  , null                                as gen
                  , null 			 as totNB
                  , null			 as genNB
                  , tech_notes.tech_notes_ticket_tech   as userN
                  ,users.dailyHours as daily
                  ,users.tech_email as tEmail
               from tech_notes
             
               join new_tickets 
                 on tech_notes.ticket_unique_id
                  = new_tickets.ticket_unique_id
               join users
                 on users.tech_user = tech_notes.tech_notes_ticket_tech
               where new_tickets.billable IS NULL
                and new_tickets.ticket_status <> "new"
                and tech_notes.tech_notes_real_date = '2004-12-01'
                        
             group 
                 by tech_notes.tech_notes_ticket_tech
          union
    select null as tot
                  , null                                as genNB
                  ,sum(tech_notes.time_spent)          as totNB 
                   ,null as gen
                  , tech_notes.tech_notes_ticket_tech   as userN
                  , users.dailyHours  as daily
                  , users.tech_email as tEmail
               from tech_notes
             
               join new_tickets 
                 on tech_notes.ticket_unique_id
                  = new_tickets.ticket_unique_id
               join users
                 on users.tech_user = tech_notes.tech_notes_ticket_tech
               where new_tickets.billable IS NOT NULL
                and new_tickets.ticket_status <> "new"
                and tech_notes.tech_notes_real_date = '2004-12-01'
               
           group 
                 by tech_notes.tech_notes_ticket_tech
         
           union 
            select null                                as tot 
                  , sum(generaltimesheet.hoursSpent)    as gen
                  , null 			 as totNB
                  , null			 as genNB
                  , generaltimesheet.userName           as userN
                  , users.dailyHours as daily
                  , users.tech_email as tEmail
               from generaltimesheet
                join users
                 on users.tech_user = generaltimesheet.userName
              where generaltimesheet.billable IS NOT NULL
              and generaltimesheet.generalDate = '2004-12-01'
                
           group by generaltimesheet.userName
        union 
            select null                                as totNB
                   ,null as tot
                   ,null as gen 
                  , sum(generaltimesheet.hoursSpent)    as genNB
                  , generaltimesheet.userName           as userN
                  , users.dailyHours as daily
                  ,users.tech_email as tEmail
               from generaltimesheet
               join users
                 on users.tech_user = generaltimesheet.userName
              where generaltimesheet.billable IS NULL
              and generaltimesheet.generalDate = '2004-12-01'
               group 
                 by generaltimesheet.userName
                  
        
           ) as newTable
    group
        by userN
         
    order
        by userN
            ) as brandNew
    
              ) as hoursTable
    
    ) as final
    Any help would be greatly appreciated!

    Jon

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that query is just way too complex to try to get into

    what i think you need is a LEFT OUTER JOIN such that all users are returned, whether or not they have matching rows, and then take it from there

    no idea how you would combine the LEFT OUTER JOIN with that monster, though
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    New Castle, PA
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yeah, I know it's a beast - it's *loosely* based upon one that you put together for me a while back, but I expanded on it to get one big total. The way I'm handling it now is just having this query in a function, and throwing each user into the function individually - not as graceful, but still works. It's not for a live page, it's just a batch job that runs at night. Thanks again for taking the time to look at it though!

    Jon


Bookmarks

Posting Permissions

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