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'에 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.
라는 오류가 또 뜹니다ㅠㅠ
도와주세요
|