database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6891 게시물 읽기
No. 6891
밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요.
작성자
ㅁㅁ
작성일
2015-03-12 15:19
조회수
6,655

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 )
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM sys.all_columns
)
, conDays ( aDay )
AS
(
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 )
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM sys.all_columns
)
, conDays ( aDay )
AS
(
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

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

 

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

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

상기 Select 절에서

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

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

(큰 의미만 놓고 본다면)

 

세부적인 내용은 exists

not exists 로 검색하시면

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

최한영(terry0515)님이 2015-03-13 16:43에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
6894MS SQL 프로시저 실행시 오류 현상
신상우
2015-03-17
7136
6893Procedure 실행시 속도 저하 해결법? [2]
박창수
2015-03-17
11672
6892sql 쿼리 질의 드립니다~ [1]
하메스
2015-03-15
5485
6891밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요. [10]
ㅁㅁ
2015-03-12
6655
6890일자별 호텔방문 손님 수 [5]
양현지
2015-03-10
7444
6889mssql DB 질문드립니다. [1]
듀기파파
2015-03-09
5173
6888select count 문의 [1]
신상우
2015-03-03
5378
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.016초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다