/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Sex and Monthly
*/
ALTER Procedure dbo.sp_Statictics_Logon_Sex_monthly_Get
@SearchDate Varchar(10) = ''
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #intchk ( i int not null)
Declare @cnt INT
SET @cnt = 0
WHILE 30 >= @cnt
BEGIN
INSERT #intchk
SELECT @cnt SET @cnt = @cnt + 1
END
SELECT AA.sex,AA.rd, sum(AA.sexdaycnt) sexdaycnt
INTO #A
FROM ( SELECT a.sex,convert(char(10), DATEADD(day, i, convert(datetime ,@SearchDate)),121) rd ,
CASE WHEN convert(char(10), DATEADD(day, i, convert(datetime ,@SearchDate)),121) = a.regdate THEN sexdaycnt
ELSE 0 end sexdaycnt
FROM DayhhWeblogin a ,
#intchk b
-- where a.sex = 1
) AA
GROUP BY AA.sex ,AA.rd
CREATE TABLE #sqltbl (sql Varchar(1000))
INSERT #sqltbl
SELECT 'select sex'
INSERT #sqltbl
SELECT ' ,sum(case rd when '''+rd+''' then sexdaycnt else 0 end ) as ''dd'+substring(rd,9,2)+''''
FROM #A
WHERE sex = 1
INSERT #sqltbl
SELECT ' from #A group by sex'
SELECT identity (INT ,1,1) idx ,sql INTO #sql1 FROM #sqltbl
Declare @max INT
Declare @strsql Varchar(1000)
Declare @strsqltt Varchar(8000)
SET @cnt = 1
SELECT @max =max (idx) FROM #sql1
SET @strsqltt =''
WHILE @cnt <= @max
BEGIN
SELECT @strsql = sql FROM #sql1 WHERE idx = @cnt
SET @cnt = @cnt + 1
SET @strsqltt = @strsqltt + @strsql
END
CREATE TABLE [#temp] (
[sex] [bit] NULL ,
[dd01] [int] NULL ,
[dd02] [int] NULL ,
[dd03] [int] NULL ,
[dd04] [int] NULL ,
[dd05] [int] NULL ,
[dd06] [int] NULL ,
[dd07] [int] NULL ,
[dd08] [int] NULL ,
[dd09] [int] NULL ,
[dd10] [int] NULL ,
[dd11] [int] NULL ,
[dd12] [int] NULL ,
[dd13] [int] NULL ,
[dd14] [int] NULL ,
[dd15] [int] NULL ,
[dd16] [int] NULL ,
[dd17] [int] NULL ,
[dd18] [int] NULL ,
[dd19] [int] NULL ,
[dd20] [int] NULL ,
[dd21] [int] NULL ,
[dd22] [int] NULL ,
[dd23] [int] NULL ,
[dd24] [int] NULL ,
[dd25] [int] NULL ,
[dd26] [int] NULL ,
[dd27] [int] NULL ,
[dd28] [int] NULL ,
[dd29] [int] NULL ,
[dd30] [int] NULL ,
[dd31] [int] NULL
)
INSERT #temp EXEC ( @strsqltt)
Select '分類', '1 日', '2 日', '3 日', '4 日', '5 日', '6 日', '7 日', '8 日', ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(dd01)) , Convert(Varchar(15),Sum(dd02)) , Convert(Varchar(15),Sum(dd03)) ,
Convert(Varchar(15),Sum(dd04)) , Convert(Varchar(15),Sum(dd05)) , Convert(Varchar(15),Sum(dd06)) ,
Convert(Varchar(15),Sum(dd07)) , Convert(Varchar(15),Sum(dd08)) , ''
FROM #temp
Group by Sex With RollUP
Union All
Select '分類', '9 日', '10 日', '11 日', '12 日', '13 日', '14 日', '15 日', '16 日' , ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(dd09)) , Convert(Varchar(15),Sum(dd10)) , Convert(Varchar(15),Sum(dd11)) ,
Convert(Varchar(15),Sum(dd11)) , Convert(Varchar(15),Sum(dd13)) , Convert(Varchar(15),Sum(dd14)) ,
Convert(Varchar(15),Sum(dd15)) , Convert(Varchar(15),Sum(dd16)) , ''
FROM #temp
Group by Sex With RollUP
Union All
Select '分類', '17 日', '18 日', '19 日', '20 日', '21 日', '22 日', '23 日', '24 日' , ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(dd17)) , Convert(Varchar(15),Sum(dd18)) , Convert(Varchar(15),Sum(dd19)) ,
Convert(Varchar(15),Sum(dd20)) , Convert(Varchar(15),Sum(dd21)) , Convert(Varchar(15),Sum(dd22)) ,
Convert(Varchar(15),Sum(dd23)) , Convert(Varchar(15),Sum(dd24)) , ''
FROM #temp
Group by Sex With RollUP
Union All
Select '分類', '25 日', '26 日', '27 日', '28 日', '29 日', '30 日', '31 日', '合計' , ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(dd25)) , Convert(Varchar(15),Sum(dd26)) , Convert(Varchar(15),Sum(dd27)) ,
Convert(Varchar(15),Sum(dd28)) , Convert(Varchar(15),Sum(dd29)) , Convert(Varchar(15),Sum(dd30)) ,
Convert(Varchar(15),Sum(dd31)) ,
Convert(Varchar(15),Sum(dd01)+Sum(dd02)+Sum(dd03)+Sum(dd04)+Sum(dd05)+Sum(dd06)+Sum(dd07)+Sum(dd08)+Sum(dd09)+Sum(dd10)+Sum(dd11)+Sum(dd11)+Sum(dd13)+Sum(dd14)+Sum(dd15)+Sum(dd16)+Sum(dd17)+Sum(dd18)+Sum(dd19)+Sum(dd20)+Sum(dd21)+Sum(dd22)+Sum(dd23)+Sum(dd24)+Sum(dd25)+Sum(dd26)+Sum(dd27)+Sum(dd28)+Sum(dd29)+Sum(dd30)+Sum(dd31)), ''
FROM #temp
Group by Sex With RollUP
DROP TABLE #sqltbl
DROP TABLE #A
DROP TABLE #sql1
DROP TABLE #intchk
DROP TABLE #temp
END
