|
요금을 통계내는 쿼리문을 만들고있습니다.
많은 도움 주세요
테이블 구조를 설명해드리겠습니다.
charge1 ,charge2 테이블이있습니다.
두개의 테이블은 요금내역 테이블입니다.
연도별 분기별로 전체 요금을 통계내려고합니다.
그래서 먼저 charge1테이블에서 연도별 분기별로 요금을 통계냈습니다.
쿼리문입니다.
charge1테이블 조회:
select year(wdate)as year,
isnull(case datepart(qq,wdate) when 1 then cast(sum(amount) as int) end,0) as '1/4',
isnull(case datepart(qq,wdate) when 2 then cast(sum(amount) as int) end,0) as '2/4',
isnull(case datepart(qq,wdate) when 3 then cast(sum(amount) as int) end,0) as '3/4',
isnull(case datepart(qq,wdate) when 4 then cast(sum(amount) as int) end,0) as '4/4',
cast(sum(amount)as int)as total
from charge1
group by year(wdate),datepart(qq,wdate) order by year desc
☆ Field - wdate :결내일자
- amount :결재금액
쿼리 실행결과
| year |
1/4 |
2/4 |
3/4 |
4/4 |
total |
| 2003 |
0 |
0 |
2000 |
0 |
2000 |
| 2004 |
200 |
0 |
0 |
0 |
200 |
charge2테이블 조회:
select year(wdate)as year,
isnull(case datepart(qq,wdate) when 1 then cast(sum(amount) as int) end,0) as '1/4',
isnull(case datepart(qq,wdate) when 2 then cast(sum(amount) as int) end,0) as '2/4',
isnull(case datepart(qq,wdate) when 3 then cast(sum(amount) as int) end,0) as '3/4',
isnull(case datepart(qq,wdate) when 4 then cast(sum(amount) as int) end,0) as '4/4',
cast(sum(amount)as int) as total
from charge2
group by year(wdate),datepart(qq,wdate) order by year desc
쿼리실행결과
| year |
1/4 |
2/4 |
3/4 |
4/4 |
total |
| 2003 |
0 |
0 |
10000 |
0 |
10000 |
| 2004 |
|
5000 |
0 |
0 |
5000 |
이렇게 나오는데요
이결과물 2개를 합계를 내서
| year |
1/4 |
2/4 |
3/4 |
4/4 |
total |
| 2003 |
0 |
0 |
12000 |
0 |
12000 |
| 2004 |
200 |
5000 |
0 |
0 |
5200 |
이런 결과물을 얻고싶습니다
어떻게 하면 될까요?
조언좀 부탁드립니다.
|
