SHARE
TWEET

Untitled

a guest Jan 12th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Joining two tables with a sum( ) table
  2. SELECT firstName + ' ' + lastName as Name,
  3.        RTS_ROLE.roleDescription as `Current Role`,
  4. FROM RTS_STAFF, RTS_ROLE
  5. WHERE RTS_STAFF.staffRole = RTS_ROLE.roleID
  6. AND staffDeleted <> true
  7. ORDER BY roleID;
  8.    
  9. SELECT RTS_STAFF.staffId, Sum(IIf(Isnull(Monday),0,Monday)
  10. +IIf(Isnull(Tuesday),0,Tuesday)
  11. +IIf(Isnull(Wednesday),0,Wednesday)
  12. +IIf(Isnull(Thursday),0,Thursday)
  13. +IIf(Isnull(Friday),0,Friday)) AS `Weekly Hours`
  14. FROM WORKING_HOURS, RTS_STAFF
  15. WHERE RTS_STAFF.staffId = WORKING_HOURS.staffID
  16. AND Date() > WORKING_HOURS.EffectiveFrom
  17. GROUP BY RTS_STAFF.staffId;
  18.    
  19. Table structure:
  20. RTS_ROLE: (lookup table)
  21. (pk)RoleID: number
  22. roleDescription: string
  23.  
  24. RTS_STAFF
  25. (pk)staffId: number
  26. firstName: string
  27. lastName: string
  28. staffRole: number (fk to RTS_ROLE.roleID)
  29. staffDeleted: boolean
  30.  
  31. WORKING_HOURS:
  32. (pk)workingWeekID: number
  33. staffID: number (fk to RTS_STAFF.staffId)
  34. Monday: number
  35. Tuesday: number
  36. ...
  37. Friday:number
  38. EffectiveFrom: date
  39.    
  40. SELECT staff.staffid,
  41.        staff.firstname + ' ' + staff.lastname AS Name,
  42.        role.roledescription                   AS `Current Role`,
  43.        Sum(IF(Isnull(monday), 0, monday)
  44.            + IF(Isnull(tuesday), 0, tuesday)
  45.            + IF(Isnull(wednesday), 0, wednesday)
  46.            + IF(Isnull(thursday), 0, thursday)
  47.            + IF(Isnull(friday), 0, friday))   AS `Weekly Hours`
  48. FROM   working_hours,
  49.        rts_staff staff,
  50.        rts_role role
  51. WHERE  staff.staffrole = role.roleid
  52.        AND staff.staffdeleted <> true
  53.        AND staff.staffid = working_hours.staffid
  54.        AND Now() > working_hours.effectivefrom
  55. GROUP  BY staff.staffid,
  56.           staff.firstname + ' ' + staff.lastname,
  57.           role.roledescription
  58.    
  59. CREATE TABLE `rts_staff` (
  60.   `staffid` int(11) NOT NULL,
  61.   `staffrole` int(11) DEFAULT NULL,
  62.   `firstname` varchar(45) DEFAULT NULL,
  63.   `lastname` varchar(45) DEFAULT NULL,
  64.   `staffdeleted` varchar(45) DEFAULT NULL,
  65.   PRIMARY KEY (`staffid`)
  66. ) ENGINE=InnoDB
  67.  
  68.  
  69. CREATE TABLE `rts_role` (
  70.   `roleid` int(11) NOT NULL,
  71.   `roledescription` varchar(45) DEFAULT NULL,
  72.   PRIMARY KEY (`roleid`)
  73. ) ENGINE=InnoDB
  74.  
  75.  
  76. CREATE TABLE `working_hours` (
  77.   `staffid` int(11) NOT NULL,
  78.   `effectivefrom` date DEFAULT NULL,
  79.   `monday` int(11) DEFAULT NULL,
  80.   `tuesday` int(11) DEFAULT NULL,
  81.   `wednesday` int(11) DEFAULT NULL,
  82.   `thursday` int(11) DEFAULT NULL,
  83.   `friday` int(11) DEFAULT NULL,
  84.   PRIMARY KEY (`staffid`)
  85. ) ENGINE=InnoDB
  86.    
  87. SELECT firstName + ' ' + lastName as Name,
  88.    RTS_ROLE.roleDescription as `Current Role`, x.weeklyhours
  89.    FROM RTS_STAFF s INNER JOIN
  90.    RTS_ROLE r ON s.staffRole = r.roleID INNER JOIN
  91.    (
  92.      SELECT RTS_STAFF.staffId, Sum(IIf(Isnull(Monday),0,Monday)
  93.      +IIf(Isnull(Tuesday),0,Tuesday)
  94.      +IIf(Isnull(Wednesday),0,Wednesday)
  95.      +IIf(Isnull(Thursday),0,Thursday)
  96.      +IIf(Isnull(Friday),0,Friday)) AS weeklyhours
  97.      FROM WORKING_HOURS, RTS_STAFF
  98.      WHERE RTS_STAFF.staffId = WORKING_HOURS.staffID
  99.      AND Date() > WORKING_HOURS.EffectiveFrom
  100.      GROUP BY RTS_STAFF.staffId
  101.    ) x ON x.staffID = s.staffID
  102. WHERE  s.staffDeleted <> true
  103. ORDER BY r.roleID;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top