카페활동 트렌드

이름 : cafeTrend
기능 : 카페활동 트렌드 출력시에 사용된다.
설명 : 카페활동 트렌드를 출력할 때 필요한 데이터를 가져온다.
         최근 7일 동안 매일의 회원 가입수 및 접속수의 총합을 막대그래프 및 숫자형태로 표현하여 보여준다.
반환 : 오늘을 기준으로 -7일 까지의 날짜데이터 (char값), 회원수
         dataGroup : 날짜 (2008-04-10), char(10)
         num : 해당 일자별 접속수 총합
         cnt : 해당 일자별 회원가입수 총합

create proc cafeTrend
(
 @cafeNum int
)
as
begin

 declare @dateGroup table
 (
  date char(10)
 )

 declare @i int
 set @i = 7
 while(@i >= 0)
 begin
 insert into @dateGroup
 select convert(char(10), getdate() - @i, 120) as date
 set @i = @i - 1
 end

 select date, isnull(sum(visit_count_day), 0) as tVisit, isnull(cnt, 0)
 from @dateGroup as dg
 left join
 (
 select visit_count_day, visit_date_day as vDay from member_visit
 where fk_cafe = @cafeNum
 ) as mv
 on dg.date = mv.vDay
 left join
 (
 select count(*) as cnt, convert(char(10), join_date, 120) as mjDate
 from member_join
 where fk_cafe = @cafeNum
 group by convert(char(10), join_date, 120)
 ) as mj
 on dg.date = convert(char(10), mj.mjDate, 120)
 group by date, cnt
 order by date desc

end
-------------------------------------------------------------------------------


create proc cafeTrend(@cafeNum int)
as
begin
declare @tempTable table
(
 dataGroup char(10)
)

declare @tempTable2 table
(
 num int,
 date char(10)
)

declare @tempTable3 table
(
 cnt int,
 date char(10)
)

declare @i int
set @i = 7
while(@i >= 0)
begin
insert into @tempTable
select convert(char(10), getdate() - @i, 120)
set @i = @i - 1
end

insert into @tempTable2
select sum(visit_count_day) as cnt, visit_date_day from member_visit
where fk_cafe = @cafeNum
group by visit_date_day

insert into @tempTable3
select count(*) as cnt, convert(char(10), join_date, 120) as jd from member_join
where fk_cafe = @cafeNum
group by convert(char(10), join_date, 120)

select dataGroup, num, cafe_manage.dbo.nullNum(cnt) as cnt from
(
select dataGroup, cafe_manage.dbo.nullNum(num) as num from @tempTable as tmp1
left outer join @tempTable2 as tmp2
on tmp1.dataGroup = tmp2.date
) tmp
left outer join @tempTable3 as tmp3
on tmp.dataGroup = tmp3.date
end

----------------------------------------------------------------------------------------------
select count(fk_member), cnt, visit_date_day from
(
SELECT SUM(visit_count_day) AS cnt, visit_date_day as visit_date_day FROM member_visit
WHERE fk_cafe = 1 and
cast(visit_date_day as datetime) between getdate()-7 and getdate()
GROUP BY visit_date_day
) as jj
inner join member_join as mj
on jj.visit_date_day = convert(char(10), mj.join_date, 120)
where fk_cafe = 1
group by visit_date_day, cnt
----------------------------------------------------------------------------------------------
declare @cafeNum int
set @cafeNum = 3
declare @tempTable table
(
 dataGroup char(10)
)

declare @i int
set @i = 7
while(@i >= 0)
begin
insert into @tempTable
select convert(char(10), getdate() - @i, 120)
set @i = @i - 1
end

--select * from @tempTable

select dataGroup, num, cnt from
(
 select count(fk_member) as num, cnt, visit_date_day from
 (
  SELECT SUM(visit_count_day) AS cnt, visit_date_day as visit_date_day FROM member_visit
  WHERE fk_cafe = 1 and
  cast(visit_date_day as datetime) between getdate()-7 and getdate()
  GROUP BY visit_date_day
 ) as jj
 inner join member_join as mj
 on jj.visit_date_day = convert(char(10), mj.join_date, 120)
 where fk_cafe = 1
 group by visit_date_day, cnt
 ) as jj
right outer join @tempTable as tmp
on jj.visit_date_day = tmp.dataGroup
----------------------------------------------------------------------------------------------