daily pastebin goal
53%
SHARE
TWEET

Untitled

a guest Feb 11th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT COUNT(customers.hash) FROM customers
  2.  LEFT JOIN persons ON persons.agent_code
  3.  WHERE customers.agent_code = persons.agent_code
  4.  GROUP BY customers.agent_code
  5.    
  6. CREATE TABLE IF NOT EXISTS `customers` (
  7.   `hash` varchar(32) NOT NULL,
  8.   `date_joined` date NOT NULL,
  9.   `agent_code` int(5) NOT NULL DEFAULT '0',
  10.   PRIMARY KEY (`hash`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  12.  
  13. INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
  14. ('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
  15. ('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
  16. ('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
  17. ('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
  18. ('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
  19.  
  20.  
  21.  
  22.  
  23.  CREATE TABLE IF NOT EXISTS `persons` (
  24.   `agent_code` int(11) NOT NULL DEFAULT '0',
  25.   PRIMARY KEY (`agent_code`)
  26.  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  27.  
  28.  
  29.  
  30.   INSERT INTO `persons` (`agent_code`) VALUES
  31.   (20003),(20004);
  32.    
  33. CREATE TABLE IF NOT EXISTS `customers` (
  34.   `hash` varchar(32) NOT NULL,
  35.   `date_joined` date NOT NULL,
  36.   `agent_code` int(5) NOT NULL DEFAULT '0',
  37.   PRIMARY KEY (`hash`)
  38. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  39.  
  40. INSERT INTO `customers` (`hash`, `date_joined`, `agent_code`) VALUES
  41. ('0323619e9dd37726ad9aede6b8941022', '2012-09-17', 20004),
  42. ('0a5a74acc39773c191b87b759799b0c0', '2012-08-02', 22109),
  43. ('1aa4d97ba79dce047d519efe3832b5e5', '2012-07-19', 22109),
  44. ('2605578b2e35f01f473591d8f3ed3c51', '2012-08-06', 20003),
  45. ('26ce0904a6ea30da9b181a301937664e', '2012-07-30', 20003);
  46.  
  47.  
  48.  
  49.  
  50.  CREATE TABLE IF NOT EXISTS `persons` (
  51.   `agent_code` int(11) NOT NULL DEFAULT '0',
  52.   PRIMARY KEY (`agent_code`)
  53.  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  54.  
  55.  
  56.  
  57.   INSERT INTO `persons` (`agent_code`) VALUES
  58.   (20003),(20004);
  59.    
  60. SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
  61. FROM customers
  62. INNER JOIN persons ON persons.agent_code = customers.agent_code
  63. WHERE persons.agent_code = 20003
  64. GROUP BY customers.date_joined, persons.agent_code
  65.    
  66. SELECT DATE_FORMAT(customers.date_joined, '%W %e') AS 'dt', persons.agent_code AS 'agent', COUNT(*) AS 'customers'
  67. FROM customers
  68. INNER JOIN persons ON persons.agent_code = customers.agent_code
  69. WHERE WEEK(customers.date_joined) = WEEK(NOW())
  70. GROUP BY customers.date_joined, persons.agent_code
  71.    
  72. select c.agent_code, c.date_joined, count(c.hash)
  73. from
  74.     customers c
  75.     left join
  76.     persons p on p.agent_code = c.agent_code
  77. where weekofyear(date_joined) = weekofyear(current_date)
  78. group by c.agent_code, c.date_joined
  79. ;
  80. +------------+-------------+---------------+
  81. | agent_code | date_joined | count(c.hash) |
  82. +------------+-------------+---------------+
  83. |      20004 | 2012-09-17  |             1 |
  84. +------------+-------------+---------------+
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