월별 합개 질문입니다.

깨비꼬비 2015.03.02 16:46 Views : 3772

데이터터는...

no	name	start_date	end_date
1	홍길동	2013-05
2	유관순	2011-03	2014-08
3	한석봉	2014-12
4	김유신	2015-02

대략 이런식으로.. 되있습니다.

입학일과 졸업일 이라고 보면 되고요.

이런 데이터에서

2014-01 ~ 2014-10 까지의 현원 수 를 구하라.... 는것이 문제인데...

mdate	now_cnt
2014-01	4
2014-02	4
2014-03	4
2014-04	4
2014-05	4
2014-06	4
2014-07	4
2014-08	4
2014-09	3
2014-10	3

이런식으로 end_date 가 존재하는 날짜 이후는 현원에서 빠지게끔의 count 를... 매기게끔...

어떻게 해야되는지;;

답변 부탁드립니다.

I want to

cmd_document_vote_user Update Log List

Comment 3

Terry

2015.03.02 19:49

일단 결과만 나오게..

쿼리 자체는 단순무식합니다 -_-;;;

----------쿼리시작

Declare @as_from_yymm Char(7)
Declare @as_to_yymm   Char(7)

Select @as_from_yymm = '2014-01'
Select @as_to_yymm   = '2014-10'
with table_a As
(
    Select '홍길동' as name, '2013-05'   As start_date, ''   As end_date Union All
    Select '유관순' as name, '2011-03'   As start_date, '2014-08'   As end_date Union All
    Select '한석봉' as name, '2014-12'   As start_date, ''   As end_date Union All
    Select '김유신' as name, '2015-02'   As start_date, ''   As end_date
)
,month_t As
(
    Select '2011-01' As mon Union All
    Select '2011-02' As mon Union All
    Select '2011-03' As mon Union All
    Select '2011-04' As mon Union All
    Select '2011-05' As mon Union All
    Select '2011-06' As mon Union All
    Select '2011-07' As mon Union All
    Select '2011-08' As mon Union All
    Select '2011-09' As mon Union All
    Select '2011-10' As mon Union All
    Select '2011-11' As mon Union All
    Select '2011-12' As mon Union All
    Select '2012-01' As mon Union All
    Select '2012-02' As mon Union All
    Select '2012-03' As mon Union All
    Select '2012-04' As mon Union All
    Select '2012-05' As mon Union All
    Select '2012-06' As mon Union All
    Select '2012-07' As mon Union All
    Select '2012-08' As mon Union All
    Select '2012-09' As mon Union All
    Select '2012-10' As mon Union All
    Select '2012-11' As mon Union All
    Select '2012-12' As mon Union All
    Select '2013-01' As mon Union All
    Select '2013-02' As mon Union All
    Select '2013-03' As mon Union All
    Select '2013-04' As mon Union All
    Select '2013-05' As mon Union All
    Select '2013-06' As mon Union All
    Select '2013-07' As mon Union All
    Select '2013-08' As mon Union All
    Select '2013-09' As mon Union All
    Select '2013-10' As mon Union All
    Select '2013-11' As mon Union All
    Select '2013-12' As mon Union All
    Select '2014-01' As mon Union All
    Select '2014-02' As mon Union All
    Select '2014-03' As mon Union All
    Select '2014-04' As mon Union All
    Select '2014-05' As mon Union All
    Select '2014-06' As mon Union All
    Select '2014-07' As mon Union All
    Select '2014-08' As mon Union All
    Select '2014-09' As mon Union All
    Select '2014-10' As mon Union All
    Select '2014-11' As mon Union All
    Select '2014-12' As mon Union All
    Select '2015-01' As mon Union All
    Select '2015-02' As mon Union All
    Select '2015-03' As mon Union All
    Select '2015-04' As mon Union All
    Select '2015-05' As mon Union All
    Select '2015-06' As mon Union All
    Select '2015-07' As mon Union All
    Select '2015-08' As mon Union All
    Select '2015-09' As mon Union All
    Select '2015-10' As mon Union All
    Select '2015-11' As mon Union All
    Select '2015-12' As mon
)
                  Select
                         a.mon
                        ,(
                            Case When ISNULL(a.end_date,'') = ''
                                    Then a.cnt
                                 Else a.cnt - 1
                            End
                         )        As cnt
                    From
                        (
                              Select
                                     a.mon   As mon
                                    ,MAX(a.cnt) As cnt
                                    ,Max(b.end_date) As end_date
                                From
                                    (
                                        Select
                                               b.mon         As mon
                                              ,(
                                                  Select COUNT(1)
                                                    From table_a b
                                                   Where b.start_date <= a.start_date
                                               )   As cnt
                                          From table_a a
                                                         Left Outer Join
                                                                         month_t b
                                                                                   On a.start_date <= b.mon
                                         Where b.mon Between @as_from_yymm And @as_to_yymm
                                    ) a
                                        Left Outer Join
                                                        table_a b
                                                                  On a.mon > b.end_date
                            Group By a.mon
                        ) a
----------쿼리 끝

결과값

|----------|--------|
|mon       |   cnt |
|----------|--------|
|2014-01   |   2    |
|2014-02   |   2    |
|2014-03   |   2    |
|2014-04   |   2    |
|2014-05   |   2    |
|2014-06   |   2    |
|2014-07   |   2    |
|2014-08   |   2    |
|2014-09   |   1    |
|2014-10   |   1    |
|----------|--------|

cmd_comment_vote_user Upvote0 Downvote0
웬리

2015.03.03 16:05

DECLARE @tbl TABLE (
no int IDENTITY(1,1),
name varchar(20),
start_date date,
end_date date NULL)

INSERT INTO @tbl values ('홍길동','2013-05-01',NULL)
INSERT INTO @tbl values ('유관순','2011-03-01','2014-08-01')
INSERT INTO @tbl values ('한석봉','2014-12-01',NULL)
INSERT INTO @tbl values ('김유신','2015-02-01',NULL)

DECLARE @datetbl TABLE(
mdate date)

DECLARE @date DATE

SET @date = '2013-01-01'

WHILE @date < '2015-03-01'
BEGIN

INSERT @datetbl
SELECT @date

SET @date = DATEADD(MONTH,1,@date)
END

SELECT mdate, COUNT(*) as cnt
FROM @tbl a
INNER JOIN @datetbl b ON b.mdate BETWEEN a.start_date AND ISNULL (end_date,'2999-12-31')
GROUP BY mdate

cmd_comment_vote_user Upvote0 Downvote0
한태

2016.04.14 16:20

CREATE TABLE mancnt(
no int IDENTITY(1,1),
name varchar(20),
start_date date,
end_date date NULL)
INSERT INTO mancnt values ('홍길동','2013-05-01',NULL)
INSERT INTO mancnt values ('유관순','2011-03-01','2014-08-01')
INSERT INTO mancnt values ('한석봉','2014-12-01','2015-08-01')
INSERT INTO mancnt values ('김유신','2015-02-01',NULL)

declare @sdate datetime = '2014-01-01'
declare @edate datetime = '2015-10-01'

;with cte as
(select @sdate sd
,start_date
,end_date
,case when start_date<=@sdate then 1 else 0 end s
,case when end_date<@sdate then -1 else 0 end e
from mancnt
union all
select dateadd(mm,1,sd) sd
,start_date
,end_date
,case when start_date<=dateadd(mm,1,sd) then 1 else 0 end s
,case when end_date<dateadd(mm,1,sd) then -1 else 0 end e
from cte
where sd<@edate
)
SELECT sd mdate, SUM(s+e) now_cnt
FROM cte
GROUP BY sd
OPTION (MAXRECURSION 0)

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	39939
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	21899
8583	프로시저 암호화 한거 못보게 할 방법 없을까요? [2]	가끔보는하늘	2015.03.06	4448
8582	설치지원 파일 설치시 에러 ㅠ_ㅠ	마모루	2015.03.06	4434
8581	프로파일러 엑셀로 복사하는 방법 [1]	akfflr	2015.03.05	3157
8580	다음의 데이터에 대하여 쿼리를 어떻게하면 효율적으로 뽑을수 있을까요 TT [2]	무념	2015.03.04	3143
8579	컬럼값 비교 질문 입니다.. [4]	고구망	2015.03.03	3422
»	월별 합개 질문입니다. [3]	깨비꼬비	2015.03.02	3772
8577	db 에러 로그 확인	zza	2015.03.02	5282
8576	개발용 데이터베이스 구축 [2]	더따뜻한	2015.03.02	3201
8575	login 비밀번호를 알수 있는 방법이 있나요? [1]	곰탱이아빠	2015.03.02	3648
8574	설치 중 오류메시지가 발생하며 진행이 안됩니다..	SQLerBeginner	2015.02.28	3614
8573	이력 테이블 저장시	aspa77	2015.02.27	3284
8572	MSSQL 2000에서 DTS 관련 문의 드립니다.	akfflr	2015.02.26	3589
8571	카테고리면 한개씩 가져오기 [2]	정명철	2015.02.26	4099
8570	뷰(view)안에서 set 옵션	공유맨	2015.02.26	3384
8569	sql db를 삭제 했다가 복원했습니다.	아싸라비아콜롬비아	2015.02.26	2426
8568	링크드서버와 트리거 [3]	발코딩	2015.02.26	5981
8567	다중 LEFT OUTER JOIN 에러가 뜹니다 [3]	park87	2015.02.26	4559
8566	select 로 출력시 데이터 타입 변환 어떻게 하나요? [2]	park87	2015.02.25	2993
8565	기간별 평균값을 구하는 쿼리 질문드립니다. [2]	비폭력무저항	2015.02.25	3110
8564	case when 에서 NULL을 제대로 처리 못하는듯 합니다 [2]	park87	2015.02.25	3057

Write Tag

First Page 115 116 117 118 119 120 121 122 123 124 Last Page

월별 합개 질문입니다.

Comment 3

Terry

웬리

한태

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발