API tools faq
paste
Advertisement
Guest User

Unique borrowers by day

a guest
May 3rd, 2022
76
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 28.18 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. branchess.branchname,
  3. aa.Count_borrowernumber AS `01`,
  4. ab.Count_borrowernumber AS `02`,
  5. ac.Count_borrowernumber AS `03`,
  6. ad.Count_borrowernumber AS `04`,
  7. ae.Count_borrowernumber AS `05`,
  8. af.Count_borrowernumber AS `06`,
  9. ag.Count_borrowernumber AS `07`,
  10. ah.Count_borrowernumber AS `08`,
  11. ai.Count_borrowernumber AS `09`,
  12. aj.Count_borrowernumber AS `10`,
  13. ba.Count_borrowernumber AS `11`,
  14. bb.Count_borrowernumber AS `12`,
  15. bc.Count_borrowernumber AS `13`,
  16. bd.Count_borrowernumber AS `14`,
  17. be.Count_borrowernumber AS `15`,
  18. bf.Count_borrowernumber AS `16`,
  19. bg.Count_borrowernumber AS `17`,
  20. bh.Count_borrowernumber AS `18`,
  21. bi.Count_borrowernumber AS `19`,
  22. bj.Count_borrowernumber AS `20`,
  23. ca.Count_borrowernumber AS `21`,
  24. cb.Count_borrowernumber AS `22`,
  25. cc.Count_borrowernumber AS `23`,
  26. cd.Count_borrowernumber AS `24`,
  27. ce.Count_borrowernumber AS `25`,
  28. cf.Count_borrowernumber AS `26`,
  29. cg.Count_borrowernumber AS `27`,
  30. ch.Count_borrowernumber AS `28`,
  31. ci.Count_borrowernumber AS `29`,
  32. da.Count_borrowernumber AS `30`,
  33. db.Count_borrowernumber AS `31`,
  34. zz.Count_borrowernumber AS MONTHLY_TOTAL
  35. FROM
  36. (SELECT
  37. branches.branchcode,
  38. branches.branchname
  39. FROM
  40. branches) branchess LEFT JOIN
  41. (SELECT
  42. Year(statistics.datetime) AS YEAR,
  43. Month(statistics.datetime) AS MONTH,
  44. Day(statistics.datetime) AS DAY,
  45. statistics.branch,
  46. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  47. FROM
  48. statistics
  49. WHERE
  50. (statistics.type = 'issue' OR
  51. statistics.type = 'renew') AND
  52. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  53. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  54. Day(statistics.datetime) = 1
  55. GROUP BY
  56. statistics.branch) aa ON aa.branch = branchess.branchcode LEFT JOIN
  57. (SELECT
  58. Year(statistics.datetime) AS YEAR,
  59. Month(statistics.datetime) AS MONTH,
  60. Day(statistics.datetime) AS DAY,
  61. statistics.branch,
  62. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  63. FROM
  64. statistics
  65. WHERE
  66. (statistics.type = 'issue' OR
  67. statistics.type = 'renew') AND
  68. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  69. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  70. Day(statistics.datetime) = 2
  71. GROUP BY
  72. statistics.branch) ab ON ab.branch = branchess.branchcode LEFT JOIN
  73. (SELECT
  74. Year(statistics.datetime) AS YEAR,
  75. Month(statistics.datetime) AS MONTH,
  76. Day(statistics.datetime) AS DAY,
  77. statistics.branch,
  78. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  79. FROM
  80. statistics
  81. WHERE
  82. (statistics.type = 'issue' OR
  83. statistics.type = 'renew') AND
  84. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  85. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  86. Day(statistics.datetime) = 3
  87. GROUP BY
  88. statistics.branch) ac ON ac.branch = branchess.branchcode LEFT JOIN
  89. (SELECT
  90. Year(statistics.datetime) AS YEAR,
  91. Month(statistics.datetime) AS MONTH,
  92. Day(statistics.datetime) AS DAY,
  93. statistics.branch,
  94. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  95. FROM
  96. statistics
  97. WHERE
  98. (statistics.type = 'issue' OR
  99. statistics.type = 'renew') AND
  100. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  101. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  102. Day(statistics.datetime) = 4
  103. GROUP BY
  104. statistics.branch) ad ON ad.branch = branchess.branchcode LEFT JOIN
  105. (SELECT
  106. Year(statistics.datetime) AS YEAR,
  107. Month(statistics.datetime) AS MONTH,
  108. Day(statistics.datetime) AS DAY,
  109. statistics.branch,
  110. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  111. FROM
  112. statistics
  113. WHERE
  114. (statistics.type = 'issue' OR
  115. statistics.type = 'renew') AND
  116. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  117. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  118. Day(statistics.datetime) = 5
  119. GROUP BY
  120. statistics.branch) ae ON ae.branch = branchess.branchcode LEFT JOIN
  121. (SELECT
  122. Year(statistics.datetime) AS YEAR,
  123. Month(statistics.datetime) AS MONTH,
  124. Day(statistics.datetime) AS DAY,
  125. statistics.branch,
  126. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  127. FROM
  128. statistics
  129. WHERE
  130. (statistics.type = 'issue' OR
  131. statistics.type = 'renew') AND
  132. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  133. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  134. Day(statistics.datetime) = 6
  135. GROUP BY
  136. statistics.branch) af ON af.branch = branchess.branchcode LEFT JOIN
  137. (SELECT
  138. Year(statistics.datetime) AS YEAR,
  139. Month(statistics.datetime) AS MONTH,
  140. Day(statistics.datetime) AS DAY,
  141. statistics.branch,
  142. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  143. FROM
  144. statistics
  145. WHERE
  146. (statistics.type = 'issue' OR
  147. statistics.type = 'renew') AND
  148. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  149. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  150. Day(statistics.datetime) = 7
  151. GROUP BY
  152. statistics.branch) ag ON ag.branch = branchess.branchcode LEFT JOIN
  153. (SELECT
  154. Year(statistics.datetime) AS YEAR,
  155. Month(statistics.datetime) AS MONTH,
  156. Day(statistics.datetime) AS DAY,
  157. statistics.branch,
  158. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  159. FROM
  160. statistics
  161. WHERE
  162. (statistics.type = 'issue' OR
  163. statistics.type = 'renew') AND
  164. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  165. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  166. Day(statistics.datetime) = 8
  167. GROUP BY
  168. statistics.branch) ah ON ah.branch = branchess.branchcode LEFT JOIN
  169. (SELECT
  170. Year(statistics.datetime) AS YEAR,
  171. Month(statistics.datetime) AS MONTH,
  172. Day(statistics.datetime) AS DAY,
  173. statistics.branch,
  174. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  175. FROM
  176. statistics
  177. WHERE
  178. (statistics.type = 'issue' OR
  179. statistics.type = 'renew') AND
  180. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  181. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  182. Day(statistics.datetime) = 9
  183. GROUP BY
  184. statistics.branch) ai ON ai.branch = branchess.branchcode LEFT JOIN
  185. (SELECT
  186. Year(statistics.datetime) AS YEAR,
  187. Month(statistics.datetime) AS MONTH,
  188. Day(statistics.datetime) AS DAY,
  189. statistics.branch,
  190. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  191. FROM
  192. statistics
  193. WHERE
  194. (statistics.type = 'issue' OR
  195. statistics.type = 'renew') AND
  196. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  197. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  198. Day(statistics.datetime) = 10
  199. GROUP BY
  200. statistics.branch) aj ON aj.branch = branchess.branchcode LEFT JOIN
  201. (SELECT
  202. Year(statistics.datetime) AS YEAR,
  203. Month(statistics.datetime) AS MONTH,
  204. Day(statistics.datetime) AS DAY,
  205. statistics.branch,
  206. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  207. FROM
  208. statistics
  209. WHERE
  210. (statistics.type = 'issue' OR
  211. statistics.type = 'renew') AND
  212. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  213. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  214. Day(statistics.datetime) = 11
  215. GROUP BY
  216. statistics.branch) ba ON ba.branch = branchess.branchcode LEFT JOIN
  217. (SELECT
  218. Year(statistics.datetime) AS YEAR,
  219. Month(statistics.datetime) AS MONTH,
  220. Day(statistics.datetime) AS DAY,
  221. statistics.branch,
  222. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  223. FROM
  224. statistics
  225. WHERE
  226. (statistics.type = 'issue' OR
  227. statistics.type = 'renew') AND
  228. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  229. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  230. Day(statistics.datetime) = 12
  231. GROUP BY
  232. statistics.branch) bb ON bb.branch = branchess.branchcode LEFT JOIN
  233. (SELECT
  234. Year(statistics.datetime) AS YEAR,
  235. Month(statistics.datetime) AS MONTH,
  236. Day(statistics.datetime) AS DAY,
  237. statistics.branch,
  238. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  239. FROM
  240. statistics
  241. WHERE
  242. (statistics.type = 'issue' OR
  243. statistics.type = 'renew') AND
  244. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  245. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  246. Day(statistics.datetime) = 13
  247. GROUP BY
  248. statistics.branch) bc ON bc.branch = branchess.branchcode LEFT JOIN
  249. (SELECT
  250. Year(statistics.datetime) AS YEAR,
  251. Month(statistics.datetime) AS MONTH,
  252. Day(statistics.datetime) AS DAY,
  253. statistics.branch,
  254. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  255. FROM
  256. statistics
  257. WHERE
  258. (statistics.type = 'issue' OR
  259. statistics.type = 'renew') AND
  260. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  261. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  262. Day(statistics.datetime) = 14
  263. GROUP BY
  264. statistics.branch) bd ON bd.branch = branchess.branchcode LEFT JOIN
  265. (SELECT
  266. Year(statistics.datetime) AS YEAR,
  267. Month(statistics.datetime) AS MONTH,
  268. Day(statistics.datetime) AS DAY,
  269. statistics.branch,
  270. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  271. FROM
  272. statistics
  273. WHERE
  274. (statistics.type = 'issue' OR
  275. statistics.type = 'renew') AND
  276. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  277. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  278. Day(statistics.datetime) = 15
  279. GROUP BY
  280. statistics.branch) be ON be.branch = branchess.branchcode LEFT JOIN
  281. (SELECT
  282. Year(statistics.datetime) AS YEAR,
  283. Month(statistics.datetime) AS MONTH,
  284. Day(statistics.datetime) AS DAY,
  285. statistics.branch,
  286. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  287. FROM
  288. statistics
  289. WHERE
  290. (statistics.type = 'issue' OR
  291. statistics.type = 'renew') AND
  292. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  293. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  294. Day(statistics.datetime) = 16
  295. GROUP BY
  296. statistics.branch) bf ON bf.branch = branchess.branchcode LEFT JOIN
  297. (SELECT
  298. Year(statistics.datetime) AS YEAR,
  299. Month(statistics.datetime) AS MONTH,
  300. Day(statistics.datetime) AS DAY,
  301. statistics.branch,
  302. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  303. FROM
  304. statistics
  305. WHERE
  306. (statistics.type = 'issue' OR
  307. statistics.type = 'renew') AND
  308. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  309. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  310. Day(statistics.datetime) = 17
  311. GROUP BY
  312. statistics.branch) bg ON bg.branch = branchess.branchcode LEFT JOIN
  313. (SELECT
  314. Year(statistics.datetime) AS YEAR,
  315. Month(statistics.datetime) AS MONTH,
  316. Day(statistics.datetime) AS DAY,
  317. statistics.branch,
  318. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  319. FROM
  320. statistics
  321. WHERE
  322. (statistics.type = 'issue' OR
  323. statistics.type = 'renew') AND
  324. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  325. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  326. Day(statistics.datetime) = 18
  327. GROUP BY
  328. statistics.branch) bh ON bh.branch = branchess.branchcode LEFT JOIN
  329. (SELECT
  330. Year(statistics.datetime) AS YEAR,
  331. Month(statistics.datetime) AS MONTH,
  332. Day(statistics.datetime) AS DAY,
  333. statistics.branch,
  334. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  335. FROM
  336. statistics
  337. WHERE
  338. (statistics.type = 'issue' OR
  339. statistics.type = 'renew') AND
  340. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  341. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  342. Day(statistics.datetime) = 19
  343. GROUP BY
  344. statistics.branch) bi ON bi.branch = branchess.branchcode LEFT JOIN
  345. (SELECT
  346. Year(statistics.datetime) AS YEAR,
  347. Month(statistics.datetime) AS MONTH,
  348. Day(statistics.datetime) AS DAY,
  349. statistics.branch,
  350. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  351. FROM
  352. statistics
  353. WHERE
  354. (statistics.type = 'issue' OR
  355. statistics.type = 'renew') AND
  356. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  357. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  358. Day(statistics.datetime) = 20
  359. GROUP BY
  360. statistics.branch) bj ON bj.branch = branchess.branchcode LEFT JOIN
  361. (SELECT
  362. Year(statistics.datetime) AS YEAR,
  363. Month(statistics.datetime) AS MONTH,
  364. Day(statistics.datetime) AS DAY,
  365. statistics.branch,
  366. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  367. FROM
  368. statistics
  369. WHERE
  370. (statistics.type = 'issue' OR
  371. statistics.type = 'renew') AND
  372. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  373. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  374. Day(statistics.datetime) = 21
  375. GROUP BY
  376. statistics.branch) ca ON ca.branch = branchess.branchcode LEFT JOIN
  377. (SELECT
  378. Year(statistics.datetime) AS YEAR,
  379. Month(statistics.datetime) AS MONTH,
  380. Day(statistics.datetime) AS DAY,
  381. statistics.branch,
  382. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  383. FROM
  384. statistics
  385. WHERE
  386. (statistics.type = 'issue' OR
  387. statistics.type = 'renew') AND
  388. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  389. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  390. Day(statistics.datetime) = 22
  391. GROUP BY
  392. statistics.branch) cb ON cb.branch = branchess.branchcode LEFT JOIN
  393. (SELECT
  394. Year(statistics.datetime) AS YEAR,
  395. Month(statistics.datetime) AS MONTH,
  396. Day(statistics.datetime) AS DAY,
  397. statistics.branch,
  398. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  399. FROM
  400. statistics
  401. WHERE
  402. (statistics.type = 'issue' OR
  403. statistics.type = 'renew') AND
  404. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  405. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  406. Day(statistics.datetime) = 23
  407. GROUP BY
  408. statistics.branch) cc ON cc.branch = branchess.branchcode LEFT JOIN
  409. (SELECT
  410. Year(statistics.datetime) AS YEAR,
  411. Month(statistics.datetime) AS MONTH,
  412. Day(statistics.datetime) AS DAY,
  413. statistics.branch,
  414. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  415. FROM
  416. statistics
  417. WHERE
  418. (statistics.type = 'issue' OR
  419. statistics.type = 'renew') AND
  420. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  421. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  422. Day(statistics.datetime) = 24
  423. GROUP BY
  424. statistics.branch) cd ON cd.branch = branchess.branchcode LEFT JOIN
  425. (SELECT
  426. Year(statistics.datetime) AS YEAR,
  427. Month(statistics.datetime) AS MONTH,
  428. Day(statistics.datetime) AS DAY,
  429. statistics.branch,
  430. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  431. FROM
  432. statistics
  433. WHERE
  434. (statistics.type = 'issue' OR
  435. statistics.type = 'renew') AND
  436. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  437. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  438. Day(statistics.datetime) = 25
  439. GROUP BY
  440. statistics.branch) ce ON ce.branch = branchess.branchcode LEFT JOIN
  441. (SELECT
  442. Year(statistics.datetime) AS YEAR,
  443. Month(statistics.datetime) AS MONTH,
  444. Day(statistics.datetime) AS DAY,
  445. statistics.branch,
  446. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  447. FROM
  448. statistics
  449. WHERE
  450. (statistics.type = 'issue' OR
  451. statistics.type = 'renew') AND
  452. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  453. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  454. Day(statistics.datetime) = 26
  455. GROUP BY
  456. statistics.branch) cf ON cf.branch = branchess.branchcode LEFT JOIN
  457. (SELECT
  458. Year(statistics.datetime) AS YEAR,
  459. Month(statistics.datetime) AS MONTH,
  460. Day(statistics.datetime) AS DAY,
  461. statistics.branch,
  462. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  463. FROM
  464. statistics
  465. WHERE
  466. (statistics.type = 'issue' OR
  467. statistics.type = 'renew') AND
  468. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  469. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  470. Day(statistics.datetime) = 27
  471. GROUP BY
  472. statistics.branch) cg ON cg.branch = branchess.branchcode LEFT JOIN
  473. (SELECT
  474. Year(statistics.datetime) AS YEAR,
  475. Month(statistics.datetime) AS MONTH,
  476. Day(statistics.datetime) AS DAY,
  477. statistics.branch,
  478. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  479. FROM
  480. statistics
  481. WHERE
  482. (statistics.type = 'issue' OR
  483. statistics.type = 'renew') AND
  484. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  485. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  486. Day(statistics.datetime) = 28
  487. GROUP BY
  488. statistics.branch) ch ON ch.branch = branchess.branchcode LEFT JOIN
  489. (SELECT
  490. Year(statistics.datetime) AS YEAR,
  491. Month(statistics.datetime) AS MONTH,
  492. Day(statistics.datetime) AS DAY,
  493. statistics.branch,
  494. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  495. FROM
  496. statistics
  497. WHERE
  498. (statistics.type = 'issue' OR
  499. statistics.type = 'renew') AND
  500. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  501. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  502. Day(statistics.datetime) = 29
  503. GROUP BY
  504. statistics.branch) ci ON ci.branch = branchess.branchcode LEFT JOIN
  505. (SELECT
  506. Year(statistics.datetime) AS YEAR,
  507. Month(statistics.datetime) AS MONTH,
  508. Day(statistics.datetime) AS DAY,
  509. statistics.branch,
  510. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  511. FROM
  512. statistics
  513. WHERE
  514. (statistics.type = 'issue' OR
  515. statistics.type = 'renew') AND
  516. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  517. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  518. Day(statistics.datetime) = 30
  519. GROUP BY
  520. statistics.branch) da ON da.branch = branchess.branchcode LEFT JOIN
  521. (SELECT
  522. Year(statistics.datetime) AS YEAR,
  523. Month(statistics.datetime) AS MONTH,
  524. Day(statistics.datetime) AS DAY,
  525. statistics.branch,
  526. Count(DISTINCT statistics.borrowernumber) AS Count_borrowernumber
  527. FROM
  528. statistics
  529. WHERE
  530. (statistics.type = 'issue' OR
  531. statistics.type = 'renew') AND
  532. Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) AND
  533. Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND
  534. Day(statistics.datetime) = 31
  535. GROUP BY
  536. statistics.branch) db ON db.branch = branchess.branchcode LEFT JOIN
  537. (SELECT
  538. statisticss.branch,
  539. Count(DISTINCT statisticss.borrowernumber) AS Count_borrowernumber
  540. FROM
  541. statistics statisticss
  542. WHERE
  543. (statisticss.type = 'issue' OR
  544. statisticss.type = 'renew') AND
  545. statisticss.datetime BETWEEN (AddDate(Last_Day(SubDate(Now(), INTERVAL 2
  546. MONTH)), 1)) AND (AddDate(Last_Day(SubDate(Now(), INTERVAL 1 MONTH)), 1))
  547. GROUP BY
  548. statisticss.branch) zz ON zz.branch = branchess.branchcode
  549. UNION
  550. SELECT
  551. Concat(" DATE") AS branchname,
  552. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) AS `01`,
  553. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 1 DAY AS `02`,
  554. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 2 DAY AS `03`,
  555. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 3 DAY AS `04`,
  556. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 4 DAY AS `05`,
  557. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 5 DAY AS `06`,
  558. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 6 DAY AS `07`,
  559. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 7 DAY AS `08`,
  560. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 8 DAY AS `09`,
  561. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 9 DAY AS `10`,
  562. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 10 DAY AS `11`,
  563. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 11 DAY AS `12`,
  564. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 12 DAY AS `13`,
  565. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 13 DAY AS `14`,
  566. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 14 DAY AS `15`,
  567. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 15 DAY AS `16`,
  568. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 16 DAY AS `17`,
  569. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 17 DAY AS `18`,
  570. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 18 DAY AS `19`,
  571. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 19 DAY AS `20`,
  572. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 20 DAY AS `21`,
  573. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 21 DAY AS `22`,
  574. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 22 DAY AS `23`,
  575. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 23 DAY AS `24`,
  576. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 24 DAY AS `25`,
  577. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 25 DAY AS `26`,
  578. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 26 DAY AS `27`,
  579. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 27 DAY AS `28`,
  580. If(
  581. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 28 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  582. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 28 DAY,
  583. "NA"
  584. ) AS `29`,
  585. If(
  586. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 29 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  587. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 29 DAY,
  588. "NA"
  589. ) AS `30`,
  590. If(
  591. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 30 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  592. AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 30 DAY,
  593. "NA"
  594. ) AS `31`,
  595. Concat(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1), ' - ', Last_Day(CURRENT_DATE()) - INTERVAL 1 MONTH) AS MONTHLY_TOTAL
  596. UNION
  597. SELECT
  598. Concat(" DAY") AS branchname,
  599. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1), "%W") AS `01`,
  600. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 1 DAY, "%W") AS `02`,
  601. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 2 DAY, "%W") AS `03`,
  602. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 3 DAY, "%W") AS `04`,
  603. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 4 DAY, "%W") AS `05`,
  604. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 5 DAY, "%W") AS `06`,
  605. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 6 DAY, "%W") AS `07`,
  606. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 7 DAY, "%W") AS `08`,
  607. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 8 DAY, "%W") AS `09`,
  608. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 9 DAY, "%W") AS `10`,
  609. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 10 DAY, "%W") AS `11`,
  610. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 11 DAY, "%W") AS `12`,
  611. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 12 DAY, "%W") AS `13`,
  612. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 13 DAY, "%W") AS `14`,
  613. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 14 DAY, "%W") AS `15`,
  614. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 15 DAY, "%W") AS `16`,
  615. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 16 DAY, "%W") AS `17`,
  616. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 17 DAY, "%W") AS `18`,
  617. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 18 DAY, "%W") AS `19`,
  618. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 19 DAY, "%W") AS `20`,
  619. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 20 DAY, "%W") AS `21`,
  620. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 21 DAY, "%W") AS `22`,
  621. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 22 DAY, "%W") AS `23`,
  622. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 23 DAY, "%W") AS `24`,
  623. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 24 DAY, "%W") AS `25`,
  624. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 25 DAY, "%W") AS `26`,
  625. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 26 DAY, "%W") AS `27`,
  626. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 27 DAY, "%W") AS `28`,
  627. If(
  628. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 28 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  629. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 28 DAY, "%W"),
  630. "NA"
  631. ) AS `29`,
  632. If(
  633. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 29 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  634. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 29 DAY, "%W"),
  635. "NA"
  636. ) AS `30`,
  637. If(
  638. Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 30 DAY) = Month(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1)),
  639. Date_Format(AddDate(Last_Day(SubDate(Now(), INTERVAL 2 MONTH)), 1) + INTERVAL 30 DAY, "%W"),
  640. "NA"
  641. ) AS `31`,
  642. Concat('-') AS MONTHLY_TOTAL
  643. ORDER BY
  644. branchname
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!