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

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

No. 6891

밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요.
작성자	ㅁㅁ	작성일	2015-03-12 15:19	조회수	6,680

visit 테이블이구여

방문번호	이름	체크인날짜	체크아웃 날짜	회원등급
1	a	20150301	20150302	gold
2	b	20150303	20150304	silver
3	c	20150304	20150304	gold
4	d	20150301	20150303	silver
5	e	20150303	20150303	vip

원하는 결과는

날짜	silver	gold	vip
20150301	1	1	0
20150302	1	1	0
20150303	2	0	1
20150304	1	1	0

이건데 매일매일 현재 머물고 잇는 사람수입니다.

이렇게 하면 방법이 아예 틀려지는거죠?

Select '2015-01-01' As Dte, isnull(Sum(a.silver),0) As silver, isnull(Sum(a.gold),0) As gold, isnull(Sum(a.vip),0) As vip
From ( Select *,
(Case when a.grade='silver' Then 1 Else 0 End) As silver
,(Case when a.grade='silver' Then 1 Else 0 End) As silver
,(Case when a.grade='silver' Then 1 Else 0 End) As silver
where '2015-01-01' between ckIn and ckOut
union all
Select '2015-01-02' As Dte, isnull(Sum(a.silver),0) As silver, isnull(Sum(a.gold),0) As gold, isnull(Sum(a.vip),0) As vip
From ( Select *,
(Case when a.grade='silver' Then 1 Else 0 End) As silver
,(Case when a.grade='silver' Then 1 Else 0 End) As silver
,(Case when a.grade='silver' Then 1 Else 0 End) As silver
where '2015-01-02' between ckIn and ckOut
union all

......

이런식으로 날짜 증가해가면서 union all 햇거든요
그러니까 1년치하려니까
리소스 풀 'internal'에 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.
라는 오류가 또 뜹니다ㅠㅠ

도와주세요

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

With visit As

(

Select 1 As no,'a' As name ,'2015-03-01' As chkin_ymd, '2015-03-02' As chkout_ymd, 'gold ' As level Union All

Select 2 As no,'b' As name ,'2015-03-03' As chkin_ymd, '2015-03-04' As chkout_ymd, 'silver' As level Union All

Select 3 As no,'c' As name ,'2015-03-04' As chkin_ymd, '2015-03-04' As chkout_ymd, 'gold ' As level Union All

Select 4 As no,'d' As name ,'2015-03-01' As chkin_ymd, '2015-03-03' As chkout_ymd, 'silver' As level Union All

Select 5 As no,'e' As name ,'2015-03-03' As chkin_ymd, '2015-03-03' As chkout_ymd, 'vip ' As level

)

, nums ( aNum )

(

SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )

FROM sys.all_columns

)

, conDays ( aDay )

(

SELECT TOP (35) DATEADD( DAY, DATEDIFF(DAY, 0, '2015-03-01' ), aNum - 1 )

FROM nums

)

SELECT aDay, [silver], [gold ], [vip ]

FROM(

SELECT aDay, [level], COUNT(*) AS cnt

FROM visit

INNER JOIN conDays

ON visit.chkin_ymd <= conDays.aDay AND conDays.aDay <= visit.chkout_ymd

GROUP BY aDay, [level]

) AS tt

PIVOT(

SUM( cnt ) FOR [level] IN ([gold ], [silver], [vip ])

) AS pvt

ORDER BY aDay
;

우욱님이 2015-03-12 15:41에 작성한 댓글입니다. Edit Ｘ

'PIVOT' 근처의 구문이 잘못되었습니다. 이 기능을 사용하려면 현재 데이터베이스의 호환성 수준 값을 더 높게 설정해야 합니다. ALTER DATABASE의 SET COMPATIBILITY_LEVEL 옵션에 대해서는 도움말을 참조하십시오.

이렇게 뜨는데 일반적인 형식으로 안될까요ㅠㅠ?

작성자님이 2015-03-12 15:46에 작성한 댓글입니다. Edit Ｘ

-- 워워.. 전설의 2000을 아직도 쓰고 계신 곳이.. ㄷㄷㄷ

With visit As
(

Select 1 As no,'a' As name ,'2015-03-01' As chkin_ymd, '2015-03-02' As chkout_ymd, 'gold ' As level Union All

Select 2 As no,'b' As name ,'2015-03-03' As chkin_ymd, '2015-03-04' As chkout_ymd, 'silver' As level Union All

Select 3 As no,'c' As name ,'2015-03-04' As chkin_ymd, '2015-03-04' As chkout_ymd, 'gold ' As level Union All

Select 4 As no,'d' As name ,'2015-03-01' As chkin_ymd, '2015-03-03' As chkout_ymd, 'silver' As level Union All

Select 5 As no,'e' As name ,'2015-03-03' As chkin_ymd, '2015-03-03' As chkout_ymd, 'vip ' As level

)

, nums ( aNum )

(

SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )

FROM sys.all_columns

)

, conDays ( aDay )

(

SELECT TOP (35) DATEADD( DAY, DATEDIFF(DAY, 0, '2015-03-01' ), aNum - 1 )

FROM nums

)

SELECT aDay

, SUM( CASE WHEN [level] = 'silver' THEN cnt ELSE 0 END ) AS silver

, SUM( CASE WHEN [level] = 'gold ' THEN cnt ELSE 0 END ) AS gold

, SUM( CASE WHEN [level] = 'vip ' THEN cnt ELSE 0 END ) AS vip

FROM(

SELECT aDay, [level], COUNT(*) AS cnt

FROM visit

INNER JOIN conDays

ON visit.chkin_ymd <= conDays.aDay AND conDays.aDay <= visit.chkout_ymd

GROUP BY aDay, [level]

) AS prpv

GROUP BY aDay

ORDER BY aDay
;

우욱님이 2015-03-12 16:44에 작성한 댓글입니다. Edit Ｘ

잠깐.. WITH가 먹으면 2000은 아닌데.. 흠.. 뭘까.. -_-;;

우욱님이 2015-03-12 16:50에 작성한 댓글입니다. Edit Ｘ

2008이에영

흐엉 마지막으로 죄송한데여

이게 제가 기간을 정해야하거든여

20150101부터 20151231까지 데이터를 뽑을 때

각 날에 머무르고 잇는 손님이 없으면 0으로 나와야하고,

기간을 지정하면 그 기간은 매일매일 행으로 나와야해여ㅠㅠ

제가 초보라ㅠㅠ 기간정해서 나오는게 어렵네요

작성자님이 2015-03-12 17:22에 작성한 댓글입니다. Edit Ｘ

-- 우욱님이 다 마무리지어두신거..

-- 조금만 수정함 -_-;;

--쿼리 시작

Declare @as_from_ymd Char(10)
Declare @as_to_ymd Char(10)

Select @as_from_ymd = '2015-02-01'
Select @as_to_ymd = '2015-03-04'

With visit As
(

Select 1 As no,'a' As name ,'2015-03-01' As chkin_ymd, '2015-03-02' As chkout_ymd, 'gold '   As level Union All
Select 2 As no,'b' As name ,'2015-03-03' As chkin_ymd, '2015-03-04' As chkout_ymd, 'silver'   As level Union All
Select 3 As no,'c' As name ,'2015-03-04' As chkin_ymd, '2015-03-04' As chkout_ymd, 'gold '   As level Union All
Select 4 As no,'d' As name ,'2015-03-01' As chkin_ymd, '2015-03-03' As chkout_ymd, 'silver'   As level Union All
Select 5 As no,'e' As name ,'2015-03-03' As chkin_ymd, '2015-03-03' As chkout_ymd, 'vip   '   As level
)
, nums ( aNum )
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM sys.all_columns
)
, conDays ( aDay )
AS
(
SELECT TOP (100) DATEADD( DAY, DATEDIFF(DAY, 0, '2015-03-01' ), aNum - 70 )
FROM nums
)

Select a.aDay
      ,Sum(a.silver)   As silver
      ,Sum(a.gold )   As gold
      ,Sum(a.vip   )   As vip
      ,CONVERT(VARCHAR(10), a.aDay, 112)
From
     (

                SELECT aDay
                , SUM( CASE WHEN [level] = 'silver' THEN cnt ELSE 0 END ) AS silver
                , SUM( CASE WHEN [level] = 'gold ' THEN cnt ELSE 0 END ) AS gold
                , SUM( CASE WHEN [level] = 'vip   ' THEN cnt ELSE 0 END ) AS vip
                FROM(
                SELECT aDay, [level], COUNT(*) AS cnt
                FROM visit
                Inner Join conDays
                ON visit.chkin_ymd <= conDays.aDay AND conDays.aDay <= visit.chkout_ymd
                GROUP BY aDay, [level]
                ) AS prpv
        GROUP BY aDay

       Union All

        Select a.aDay
              ,0        As silver
              ,0        As gold
              ,0        As vip
          From conDays a
         Where Not Exists (
                             Select 'x'
                               From visit
                              Where a.aDay Between chkin_ymd And chkout_ymd
                          )
     ) a
Where (
           SubString( CONVERT(VARCHAR(8), a.aDay, 112),1,4)
         + '-'
         + SubString( CONVERT(VARCHAR(8), a.aDay, 112),5,2)
         + '-'
         + SubString( CONVERT(VARCHAR(8), a.aDay, 112),7,2)
       ) Between @as_from_ymd And @as_to_ymd
GROUP BY aDay
ORDER BY aDay

;

--쿼리 끝

최한영(terry0515)님이 2015-03-12 18:41에 작성한 댓글입니다.

흐엉..지정한 날짜만큼 안나오고 계속 12월 22일부터 나와요ㅠㅠ

작성자님이 2015-03-12 19:36에 작성한 댓글입니다. Edit Ｘ

--요청하신 조건에 부합되게..

--20150101 ~ 20151231

--까지 조회하는걸로 만들었습니다~~

--쿼리 시작

Declare @as_from_ymd Char(10)
Declare @as_to_ymd Char(10)

Select @as_from_ymd = '2015-01-01'
Select @as_to_ymd = '2015-12-31'

With visit As
(

Select 1 As no,'a' As name ,'2015-03-01' As chkin_ymd, '2015-03-02' As chkout_ymd, 'gold '   As level Union All
Select 2 As no,'b' As name ,'2015-03-03' As chkin_ymd, '2015-03-04' As chkout_ymd, 'silver'   As level Union All
Select 3 As no,'c' As name ,'2015-03-04' As chkin_ymd, '2015-03-04' As chkout_ymd, 'gold '   As level Union All
Select 4 As no,'d' As name ,'2015-03-01' As chkin_ymd, '2015-03-03' As chkout_ymd, 'silver'   As level Union All
Select 5 As no,'e' As name ,'2015-03-03' As chkin_ymd, '2015-03-03' As chkout_ymd, 'vip   '   As level
)
, nums ( aNum )
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM sys.all_columns
)
, conDays ( aDay )
AS
(
SELECT TOP (365) DATEADD( DAY, DATEDIFF(DAY, 0, '2015-12-31' ), aNum - 365 )
FROM nums
)

Select a.aDay
      ,Sum(a.silver)   As silver
      ,Sum(a.gold )   As gold
      ,Sum(a.vip   )   As vip
      ,CONVERT(VARCHAR(10), a.aDay, 112)
From
     (

                SELECT aDay
                 , SUM( CASE WHEN [level] = 'silver' THEN cnt ELSE 0 END ) AS silver
                 , SUM( CASE WHEN [level] = 'gold ' THEN cnt ELSE 0 END ) AS gold
                 , SUM( CASE WHEN [level] = 'vip   ' THEN cnt ELSE 0 END ) AS vip
                FROM(
                 SELECT aDay, [level], COUNT(*) AS cnt
                 FROM visit
                 Inner Join conDays
                 ON visit.chkin_ymd <= conDays.aDay AND conDays.aDay <= visit.chkout_ymd
                 GROUP BY aDay, [level]
                ) AS prpv
        GROUP BY aDay

       Union All

        Select a.aDay
              ,0        As silver
              ,0        As gold
              ,0        As vip
          From conDays a
         Where Not Exists (
                             Select 'x'
                               From visit
                              Where a.aDay Between chkin_ymd And chkout_ymd
                          )
     ) a
Where (
           SubString( CONVERT(VARCHAR(8), a.aDay, 112),1,4)
         + '-'
         + SubString( CONVERT(VARCHAR(8), a.aDay, 112),5,2)
         + '-'
         + SubString( CONVERT(VARCHAR(8), a.aDay, 112),7,2)
       ) Between @as_from_ymd And @as_to_ymd
GROUP BY aDay
ORDER BY aDay

;

--쿼리 끝

최한영(terry0515)님이 2015-03-12 20:24에 작성한 댓글입니다.

감사합니다

Where Not Exists (
                             Select 'x'
                               From visit
                              Where a.aDay Between chkin_ymd And chkout_ymd
                          )

근데 이 부분은 무슨 뜻인지 이해가 잘 안가요ㅠㅠ

작성자님이 2015-03-13 16:00에 작성한 댓글입니다. Edit Ｘ

상기 부분은 빼셔도 무방합니다.

문자 그대로..존재하는..존재하지 않는.. 다는 뜻임

상기 Select 절에서

Not Exists 의 조건에 부합되는 건들은

빼고 Select 하겠다는 의미구요..

(큰 의미만 놓고 본다면)

세부적인 내용은 exists

not exists 로 검색하시면

자세히 설명되어 있는 글들이 많이 있을겁니다^^

최한영(terry0515)님이 2015-03-13 16:43에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
6894	MS SQL 프로시저 실행시 오류 현상	신상우	2015-03-17	7160
6893	Procedure 실행시 속도 저하 해결법? [2]	박창수	2015-03-17	11694
6892	sql 쿼리 질의 드립니다~ [1]	하메스	2015-03-15	5497
6891	밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요. [10]	ㅁㅁ	2015-03-12	6680
6890	일자별 호텔방문 손님 수 [5]	양현지	2015-03-10	7460
6889	mssql DB 질문드립니다. [1]	듀기파파	2015-03-09	5187
6888	select count 문의 [1]	신상우	2015-03-03	5400