소통정보통계조회.sql 1.3 KB

12345678910111213141516171819202122232425262728293031323334353637
  1. SELECT A.LINK_ID ID,
  2. (NVL(TRIM(C.NODE_NAME), '값없음')||' - '||NVL(TRIM(D.NODE_NAME), '값없음')) SECT_NAME,
  3. B.LINK_LENG SECT_LNGT,
  4. A.DATES, A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
  5. FROM (SELECT LINK_ID,
  6. SUBSTR(STAT_DT, 1, 4) DATES, --dateformat
  7. TFVL, SPED, OCPY_RATE, TRVL_HH
  8. FROM TB_LINK_YY_STAT A
  9. WHERE LINK_ID = '3850428200'
  10. AND STAT_DT BETWEEN '20140101000000' AND '20141231235959'
  11. ) A,
  12. TB_LINK B, TB_NODE C, TB_NODE D
  13. WHERE B.DEL_YN = 'N'
  14. AND A.LINK_ID = B.LINK_ID
  15. AND B.F_NODE = C.NODE_ID(+)
  16. AND B.T_NODE = D.NODE_ID(+)
  17. ORDER BY DATES
  18. SELECT A.IFSC_ID ID,
  19. (NVL(TRIM(C.NODE_NAME), '값없음')||' - '||NVL(TRIM(D.NODE_NAME), '값없음')) SECT_NAME,
  20. B.SECT_LNGT SECT_LNGT,
  21. A.DATES, A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
  22. FROM (SELECT IFSC_ID,
  23. SUBSTR(STAT_DT, 1, 6) DATES,
  24. TFVL, SPED, OCPY_RATE, TRVL_HH
  25. FROM TB_IFSC_YY_STAT A
  26. WHERE IFSC_ID = '3850428200'
  27. AND STAT_DT BETWEEN '20140101000000' AND '20141231235959'
  28. ) A,
  29. TB_IFSC B, TB_NODE C, TB_NODE D
  30. WHERE B.DEL_YN = 'N'
  31. AND A.IFSC_ID = B.IFSC_ID
  32. AND B.F_NODE_ID = C.NODE_ID(+)
  33. AND B.T_NODE_ID = D.NODE_ID(+)
  34. ORDER BY DATES