데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 7306 게시물 읽기

No. 7306

해당월의일자별장치체크 자료를 추출하고자 합니다.
작성자	빨간운동화	작성일	2024-05-24 10:37	조회수	145

장치체크주기

y:년1회

m:월1회

w:주1회

#장치테이블

장치번호 체크주기 최근체크일자

a1 w 20240501

a2 m 20240401

a3 m 20240522

a4 m 20240524

a5 y 20230606

b1 w 20240528

b2 m 20240507

#국경일테이블

20240515

20240606

#장치체크테이블

장치번호 체크일자

a5 20240607

a1 20240605

a1 20240612

a1 20240619

a1 20240626

a2 20240603

a3 20240626

a4 20240628

b1 20240604

b1 20240611

b1 20240618

b1 20240625

b2 20240611

해당월의 주중만 체크하며 토,일,국경일을 제외하며, 다음체크일이 토,일,국경일일 경우는 다음날로 하며

최긑체크일의 같은 요일 체크일을 출력하고자 합니다. 장치체크테이블과 같이 대상월은 2024년6월로 하고 싶습니다.

기존쿼리를 보니 20만건 정도 중간테이블 생성후 해당월것만 약 2천건 정도 추출하는것으로 보여 성능이 좋지 않은 것으로 보였습니다.

부탁드립니다

감사합니다.

이 글에 대한 댓글이 총 8건 있습니다.

최신 일자의 요일을 따라간다고 하면?

만약 최신 일자가의 요일이 휴일이라 뒤로 밀린거라고 하면?

시간이 지날 수록 점점 요일이 뒤로 밀릴 텐데요?

일자 기준이라고 해도

시간이 지날 수록 점점 일자가 뒤로 밀릴 것 같습니다.

요일이나 일자가 최근 기준이 아닌 고정된 기준을 가져가야 하지 않을 런지?

요일은 W 일 때만 적용이겠죠?

M, Y 는 요일이 아닌 일자가 적용되어야 할 것 같은데.

b2 가 20240507 에서 20240611 로 바뀐 이유를 모르겠네요?

마농(manon94)님이 2024-05-28 10:36에 작성한 댓글입니다.

한가지 설명이 누락되었습니다. 양해 부탁합니다.

장치에 대한 체크가 이루어 지면 체크된 일자는 장치테이블에 최근체크일자로 기록이 되게 되어 있으며 장치테이블의 최근체크일자는 사용자가 수정할 수 있게 되어있습니다.

즉 장치담당자가 다음번(달) 체크일자를 지정할 수 있습니다.

그리고 가능한 같은 요일에 체크일자가 되어야 합니다.

즉 b2가 최근 체크일이 20240507 그다음달의 최초 요일인 20400604로 되어야 하며

이는 제가 설명을 잘못 드렸습니다.

M,Y는 요일이 아닌 일자 적용이 맞습니다.

잘 부탁드립니다.

감사합니다.

빨간운동화님이 2024-05-28 11:05에 작성한 댓글입니다. Edit Ｘ

다음 두가지 설명은 서로 상충되는 내용이네요.

- M,Y는 요일이 아닌 일자 적용이 맞습니다.

- b2가 최근 체크일이 20240507 그다음달의 최초 요일인 20400604로 되어야 하며

마농(manon94)님이 2024-05-29 14:14에 작성한 댓글입니다.

WITH calendar AS

(

SELECT '20240601' dt

 UNION ALL

SELECT CONVERT(VARCHAR(8), DATEADD(DAY, 1, dt), 112)

  FROM calendar

 WHERE dt < '20240630'

)

, holiday AS

(

SELECT '20240515' dt

UNION ALL SELECT '20240606'

)

, device AS

(

SELECT 'a1' cd, 'w' cycle, '20240501' dt

UNION ALL SELECT 'a2', 'm', '20240401'

UNION ALL SELECT 'a3', 'm', '20240522'

UNION ALL SELECT 'a4', 'm', '20240524'

UNION ALL SELECT 'a5', 'y', '20230606'

UNION ALL SELECT 'b1', 'w', '20240528'

UNION ALL SELECT 'b2', 'm', '20240507'

)

SELECT a.cd

     , a.cycle

     , a.dt

     , b.next_dt

  FROM (SELECT cd, cycle, dt

             , SUBSTRING(dt, 5, 4) md

             , (DATEPART(d, dt) - 1) / 7 + 1 ww

             , DATEPART(w, dt) wk

          FROM device

        ) a

 INNER JOIN

       (SELECT a.dt

             , SUBSTRING(a.dt, 5, 4) md

             , (DATEPART(d, a.dt) - 1) / 7 + 1 ww

             , DATEPART(w, a.dt) wk

             , b.dt hd

             , MIN(CASE WHEN b.dt IS NULL

                         AND DATEPART(w, a.dt) NOT IN (1, 7)

                   THEN a.dt END) OVER(ORDER BY a.dt DESC) next_dt

          FROM calendar a

          LEFT JOIN holiday b

            ON a.dt = b.dt

        ) b

    ON (a.cycle = 'w' AND a.wk = b.wk)

    OR (a.cycle = 'm' AND a.wk = b.wk AND a.ww = b.ww)

    OR (a.cycle = 'y' AND a.md = b.md)

 ORDER BY cd, next_dt

;

마농(manon94)님이 2024-05-29 16:03에 작성한 댓글입니다.
이 댓글은 2024-05-29 16:29에 마지막으로 수정되었습니다.

신경 써주셔서 대단히 감사합니다.

정말 죄송스러운 말씀입니다만,

저히 사용하는 DBMS가 2008이여서

OVER(ORDER BY a.dt DESC)동작 하지 않았으나

2012버전에서는 멋있게 잘 작동 하였습니다.

혹시2008버전에서 동작은 가능할런지요.

감사합니다.

빨간운동화님이 2024-05-30 16:56에 작성한 댓글입니다. Edit Ｘ

WITH calendar AS

(

SELECT '20240601' dt

 UNION ALL

SELECT CONVERT(VARCHAR(8), DATEADD(DAY, 1, dt), 112)

  FROM calendar

 WHERE dt < '20240630'

)

, holiday AS

(

SELECT '20240515' dt

UNION ALL SELECT '20240606'

)

, calendar2 AS

(

SELECT a.dt

     , SUBSTRING(a.dt, 5, 4) md

     , (DATEPART(d, a.dt) - 1) / 7 + 1 ww

     , DATEPART(w, a.dt) wk

     , CASE WHEN b.dt IS NULL AND DATEPART(w, a.dt) NOT IN (1, 7)

       THEN 1 ELSE 0 END yn

  FROM calendar a

  LEFT JOIN holiday b

    ON a.dt = b.dt

)

, device AS

(

SELECT 'a1' cd, 'w' cycle, '20240501' dt

UNION ALL SELECT 'a2', 'm', '20240401'

UNION ALL SELECT 'a3', 'm', '20240522'

UNION ALL SELECT 'a4', 'm', '20240524'

UNION ALL SELECT 'a5', 'y', '20230606'

UNION ALL SELECT 'b1', 'w', '20240528'

UNION ALL SELECT 'b2', 'm', '20240507'

)

SELECT a.cd

     , a.cycle

     , a.dt

     , b.dt

     , b.yn

     , MIN(c.dt) next_dt

  FROM (SELECT cd, cycle, dt

             , SUBSTRING(dt, 5, 4) md

             , (DATEPART(d, dt) - 1) / 7 + 1 ww

             , DATEPART(w, dt) wk

          FROM device

        ) a

 INNER JOIN calendar2 b

    ON (a.cycle = 'w' AND a.wk = b.wk)

    OR (a.cycle = 'm' AND a.wk = b.wk AND a.ww = b.ww)

    OR (a.cycle = 'y' AND a.md = b.md)

 INNER JOIN calendar2 c

    ON b.dt <= c.dt

   AND c.yn = 1

 GROUP BY a.cd, a.cycle, a.dt, b.dt, b.yn

 ORDER BY cd, next_dt

;

마농(manon94)님이 2024-05-30 17:35에 작성한 댓글입니다.

저는 국경일 테이블 보다는 달력 테이블을 관리하시는 것을 추천합니다.

달력 안에 요일 및 국경일 정보 다 가지고 있으면 됩니다.

그러면 위 쿼리가 훨씬 더 간결해 지겠지요.

마농(manon94)님이 2024-05-31 08:51에 작성한 댓글입니다.

늘 이곳에서 해결책 도움을 받거나 찾고있고,

가르침 대단히 감사드립니다.

빨간운동화님이 2024-06-03 07:53에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
7309	미러링 오류	미러링	2024-06-17	15
7308	쿼리 조언 부탁드립니다. [5]	문의드립니다.	2024-06-13	41
7307	bom 구성 상위 모품목을 가지고 오고싶습니다. [2]	박인규	2024-05-30	111
7306	해당월의일자별장치체크 자료를 추출하고자 합니다. [8]	빨간운동화	2024-05-24	145
7305	매개변수 하나로 2개의 행 나누어 차감 [2]	MS보초	2024-05-16	121
7304	두 테이블 조회값을 한 쿼리 결과값으로 조합 문의 [2]	초개	2024-03-13	237
7303	문자열 구분 [2]	초보	2024-01-05	286