[MSSQL] 통계(DATEPART) 구하기 (년,분기,월,주간,일 단위)

-- 년 단위통계

SELECT TOP 10 MAX(DATEPART(YYYY, SUBL_DATE)) AS YYYY, MAX(DATEPART(mm, SUBL_DATE)) AS mm, MAX(DATEPART(DD, SUBL_DATE)) AS DD, MECUST_NM, COUNT(*) AS COUNT FROM CZ_KIDV_TAKEBACK
group by DATEPART(YYYY, SUBL_DATE), MECUST_NM
ORDER BY MAX(DATEPART(YYYY, SUBL_DATE)) ASC, MAX(DATEPART(mm, SUBL_DATE)) ASC, MAX(DATEPART(DD, SUBL_DATE)) ASC, MECUST_NM asc

-- 분기별 통계

SELECT TOP 10 MAX(DATEPART(YYYY, SUBL_DATE)) AS YYYY, DATEPART(QUARTER, SUBL_DATE) AS QUARTER , MECUST_NM, COUNT(*) AS COUNT FROM CZ_KIDV_TAKEBACK
group by DATEPART(QUARTER, SUBL_DATE), MECUST_NM
ORDER BY MAX(DATEPART(YYYY, SUBL_DATE)) ASC, MAX(DATEPART(QUARTER, SUBL_DATE)) ASC, MECUST_NM asc

-- 월 단위통계

SELECT TOP 10 MAX(DATEPART(YYYY, SUBL_DATE)) AS YYYY, DATEPART(mm, SUBL_DATE) AS mm, MECUST_NM, COUNT(*) AS COUNT FROM CZ_KIDV_TAKEBACK
group by DATEPART(mm, SUBL_DATE), MECUST_NM
ORDER BY MAX(DATEPART(YYYY, SUBL_DATE)) ASC, MAX(DATEPART(mm, SUBL_DATE)) ASC, MECUST_NM asc

-- 주간별 통계

SELECT TOP 10 MAX(DATEPART(YYYY, SUBL_DATE)) AS YYYY, MAX(DATEPART(mm, SUBL_DATE)) AS mm, DATEPART(ww, SUBL_DATE) AS ww, MECUST_NM, COUNT(*) AS COUNT FROM CZ_KIDV_TAKEBACK
group by DATEPART(ww, SUBL_DATE), MECUST_NM
ORDER BY MAX(DATEPART(YYYY, SUBL_DATE)) ASC, MAX(DATEPART(mm, SUBL_DATE)) ASC, DATEPART(ww, SUBL_DATE) ASC, MECUST_NM asc

-- 일 단위통계

SELECT TOP 10 MAX(DATEPART(YYYY, SUBL_DATE)) AS YYYY, MAX(DATEPART(mm, SUBL_DATE)) AS mm, DATEPART(DD, SUBL_DATE) AS DD, MECUST_NM, COUNT(*) AS COUNT FROM CZ_KIDV_TAKEBACK
group by DATEPART(DD, SUBL_DATE), MECUST_NM
ORDER BY MAX(DATEPART(YYYY, SUBL_DATE)) ASC, MAX(DATEPART(mm, SUBL_DATE)) ASC, DATEPART(DD, SUBL_DATE) ASC, MECUST_NM asc

---

 

끝~