통계용 쿼리 샘플

select year as '년', month as '월', count(*) as count
from (
	select
		YEAR(us.fd_dtcreate) as 'year',
		MONTH(us.fd_dtcreate)  as 'month'
	from tb_ykfile as us
	where us.fd_dtCreate > '2020-08-01' and us.fd_dtCreate <= '2022-01-01'
) tb
group by year, month
order by year, month
;

select year as '년', month as '월', count(*) as count

from (

select

YEAR(us.fd_dtcreate) as 'year',

MONTH(us.fd_dtcreate) as 'month'

from tb_ykfile as us

where us.fd_dtCreate > '2020-08-01' and us.fd_dtCreate <= '2022-01-01'

) tb

group by year, month

order by year, month

;

SELECT    CONVERT(varCHAR(10),Dateadd(d, a.number,'2020-08-01 00:00:00'),120) AS dt
          ,Isnull(b.count, 0)                            AS count
FROM      master..spt_values a 
LEFT JOIN 
          ( 
                   SELECT   count(*)                    AS count, 
                            CONVERT(CHAR(10), b1.fd_dtcreate,120) AS fd_dtcreate
                   FROM     tb_ykfile b1 WITH(nolock) 
                   GROUP BY CONVERT(CHAR(10), b1.fd_dtcreate, 120)
		 ) b 
ON        CONVERT(CHAR(10), Dateadd(d, a.number,'2020-08-01 00:00:00'), 120) = b.fd_dtcreate 
WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, '2020-08-01'), 120) <= '2022-01-01'
order by dt
;

SELECT CONVERT(varCHAR(10),Dateadd(d, a.number,'2020-08-01 00:00:00'),120) AS dt

,Isnull(b.count, 0) AS count

FROM master..spt_values a

LEFT JOIN

(

SELECT count(*) AS count,

CONVERT(CHAR(10), b1.fd_dtcreate,120) AS fd_dtcreate

FROM tb_ykfile b1 WITH(nolock)

GROUP BY CONVERT(CHAR(10), b1.fd_dtcreate, 120)

) b

ON CONVERT(CHAR(10), Dateadd(d, a.number,'2020-08-01 00:00:00'), 120) = b.fd_dtcreate

WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, '2020-08-01'), 120) <= '2022-01-01'

order by dt

;

anydragon

참고자료

최신 글

보관함

통계용 쿼리 샘플

관련

Leave a Reply Cancel reply

태그

링크

메타