/*
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
ALTER Procedure dbo.up_LogonStatictics_SPM_Get
@SelMode TinyINT = 0
, @DayStyleMode TinyINT = 0
, @SearchDate Varchar(10) = ''
AS
IF @SelMode = 1 Goto ByGender
IF @SelMode = 2 Goto ByAgeRate
ByGender:
IF @DayStyleMode = 1 Goto ByGender_Day
IF @DayStyleMode = 2 Goto ByGender_Week
IF @DayStyleMode = 3 Goto ByGender_Month
IF @DayStyleMode = 4 Goto ByGender_Year
Return
ByAgeRate:
IF @DayStyleMode = 1 Goto ByAgeRate_Day
IF @DayStyleMode = 2 Goto ByAgeRate_Week
IF @DayStyleMode = 3 Goto ByAgeRate_Month
IF @DayStyleMode = 4 Goto ByAgeRate_Year
Return
ByGender_Day:
Execute dbo.sp_Statictics_Logon_Sex_Daily_Get @SearchDate
Return
ByGender_Week:
Execute dbo.sp_Statictics_Logon_Sex_Weekend_Get @SearchDate
Return
ByGender_Month:
Execute sp_Statictics_Logon_Sex_monthly_Get @SearchDate
Return
ByGender_Year:
Select 'sex_year'
Return
ByAgeRate_Day:
Execute sp_Statictics_Logon_AgeRate_Daily_Get @SearchDate
Return
ByAgeRate_Week:
Execute sp_Statictics_Logon_AgeRate_WeekEnd_Get @SearchDate
Return
ByAgeRate_Month:
Execute sp_Statictics_Logon_AgeRate_Monthly_Get @SearchDate
Return
ByAgeRate_Year:
Select 'age_year'
Return
Go
--By Sex And Daily
Execute up_LogonStatictics_SPM_Get 1, 1, '2004-08-11'
--By Sex And WeekDay
Execute up_LogonStatictics_SPM_Get 1, 2, '2004-08-11'
--By Sex And Month
Execute up_LogonStatictics_SPM_Get 1, 3, '2004-08-11'
--By Sex And Year
Execute up_LogonStatictics_SPM_Get 1, 4, '2004-08-11'
--By AgeRate And Daily
Execute up_LogonStatictics_SPM_Get 2, 1, '2004-08-11'
--By AgeRate And WeekDay
Execute up_LogonStatictics_SPM_Get 2, 2, '2004-08-11'
--By AgeRate And Month
Execute up_LogonStatictics_SPM_Get 2, 3, '2004-08-11'
--By AgeRate And Year
Execute up_LogonStatictics_SPM_Get 2, 4, '2004-08-11'
----------------------------------------------------------------------------
/*
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
----------------------------------------------------------------------------
/*
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
CREATE Procedure dbo.sp_Statictics_Logon_Sex_Daily_Get
@SearchDate Varchar(10) = ''
AS
Select '分類', '0 時', '1 時', '2 時', '3 時', '4 時', '5 時', '6 時', '7 時', ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh00)) , Convert(Varchar(15),Sum(hh01)) , Convert(Varchar(15),Sum(hh02)) , Convert(Varchar(15),Sum(hh03)) , Convert(Varchar(15),Sum(hh04)) , Convert(Varchar(15),Sum(hh05)) , Convert(Varchar(15),Sum(hh06)) , Convert(Varchar(15),Sum(hh07)) , ''
from dbo.DayhhWeblogin
Where regdate = @SearchDate
Group by Sex With RollUP
Union All
Select '分類', '8 時', '9 時', '10 時', '11 時', '12 時', '13 時', '14 時', '15 時', ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh08)) , Convert(Varchar(15),Sum(hh09)) , Convert(Varchar(15),Sum(hh10)) , Convert(Varchar(15),Sum(hh11)) , Convert(Varchar(15),Sum(hh12)) , Convert(Varchar(15),Sum(hh13)) , Convert(Varchar(15),Sum(hh14)) , Convert(Varchar(15),Sum(hh15)) , ''
from dbo.DayhhWeblogin
Where regdate = @SearchDate
Group by Sex With RollUP
Union All
Select '分類', '16 時', '17 時', '18 時', '19 時', '20 時', '21 時', '22 時', '23 時', '合計'
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh16)) , Convert(Varchar(15),Sum(hh17)) , Convert(Varchar(15),Sum(hh18)) , Convert(Varchar(15),Sum(hh19)) , Convert(Varchar(15),Sum(hh20)) , Convert(Varchar(15),Sum(hh21)) , Convert(Varchar(15),Sum(hh22)) , Convert(Varchar(15),Sum(hh23)) ,
Convert(Varchar(15),Sum(SexDaycnt))
from dbo.DayhhWeblogin
Where regdate = @SearchDate
Group by Sex With RollUP
Go
----------------------------------------------------------------------------
/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Sex and Weekend
*/
ALTER Procedure dbo.sp_Statictics_Logon_Sex_Weekend_Get
@SearchDate Varchar(10) = ''
AS
Select
'分類'
, Convert(Varchar(10), DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(日)'
, Convert(Varchar(10), DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(月)'
, Convert(Varchar(10), DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(火)'
, Convert(Varchar(10), DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(水)'
, Convert(Varchar(10), DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(木)'
, Convert(Varchar(10), DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(金)'
, Convert(Varchar(10), DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(土)'
, '','合計'
Union All
SELECT
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE SEX
END AS Gender
, Convert(Varchar(15), SUM(Sun))
, Convert(Varchar(15), SUM(Mon))
, Convert(Varchar(15), SUM(Tue))
, Convert(Varchar(15), SUM(Wed))
, Convert(Varchar(15), SUM(Thu))
, Convert(Varchar(15), SUM(Fri))
, Convert(Varchar(15), SUM(Sat))
, ''
, Convert(Varchar(15), SUM(TotalBySex))
FROM
(
SELECT
CASE SEX
WHEN 0 Then '女性'
Else '男性'
End SEX ,
CASE
WHEN regdate = DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Sun ,
CASE
WHEN regdate = DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Mon ,
CASE
WHEN regdate = DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Tue ,
CASE
WHEN regdate = DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Wed ,
CASE
WHEN regdate = DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Thu ,
CASE
WHEN regdate = DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Fri ,
CASE
WHEN regdate = DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Sat ,
SexDayCnt As TotalBySex
From
(
Select Regdate, sex, SexDaycnt
from dbo.DayhhWeblogin
Where Regdate >= Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)
And Regdate <= Convert(DateTime, @SearchDate) + (Convert(Int, DatePart(dw, @SearchDate))-1)
) AS TempTable
) AA
GROUP BY SEX With RollUP
Go
sp_Statictics_Logon_Sex_Weekend_Get '2004-08-11'
----------------------------------------------------------------------------
sp_Statictics_Logon_AgeRate_monthly_Get '2004-08-11'
/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Age and Monthly
*/
ALTER Procedure dbo.sp_Statictics_Logon_AgeRate_monthly_Get
@SearchDate Varchar(10) = ''
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #intchk2 ( i int not null)
Declare @cnt INT
SET @cnt = 0
WHILE 30 >= @cnt
BEGIN
INSERT #intchk2
SELECT @cnt SET @cnt = @cnt + 1
END
SELECT AA.Age,AA.rd, sum(AA.Agedaycnt) Agedaycnt
INTO #A2
FROM ( SELECT a.Age,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 agedaycnt
ELSE 0 end Agedaycnt
FROM AgeDayhhWeblogin a ,
#intchk2 b
) AA
GROUP BY AA.Age ,AA.rd
CREATE TABLE #sqltbl2 (sql Varchar(1000))
INSERT #sqltbl2
SELECT 'select Age'
INSERT #sqltbl2
SELECT distinct ' ,sum(case rd when '''+rd+''' then Agedaycnt else 0 end ) as ''dd'+substring(rd,9,2)+''''
FROM #A2
INSERT #sqltbl2
SELECT ' from #A2 group by Age'
SELECT identity (INT ,1,1) idx ,sql INTO #sql12 FROM #sqltbl2
Declare @max INT
Declare @strsql Varchar(1000)
Declare @strsqltt Varchar(8000)
SET @cnt = 1
SELECT @max =max (idx) FROM #sql12
SET @strsqltt =''
WHILE @cnt <= @max
BEGIN
SELECT @strsql = sql FROM #sql12 WHERE idx = @cnt
SET @cnt = @cnt + 1
SET @strsqltt = @strsqltt + @strsql
END
CREATE TABLE [#temp2] (
[Age] VARCHAR(10) 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 #temp2 EXEC ( @strsqltt)
Select '分類', '1 日', '2 日', '3 日', '4 日', '5 日', '6 日', '7 日', '8 日', ''
Union All
Select
CASE
WHEN (GROUPING(Age) = 1) THEN '合計'
ELSE Age
END AS AgeRate,
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 #temp2
Group by Age With RollUP
Union All
Select '分類', '9 日', '10 日', '11 日', '12 日', '13 日', '14 日', '15 日', '16 日' , ''
Union All
Select
CASE
WHEN (GROUPING(Age) = 1) THEN '合計'
ELSE Age
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 #temp2
Group by Age With RollUP
Union All
Select '分類', '17 日', '18 日', '19 日', '20 日', '21 日', '22 日', '23 日', '24 日' , ''
Union All
Select
CASE
WHEN (GROUPING(Age) = 1) THEN '合計'
ELSE Age
END AS AgeRate,
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 #temp2
Group by Age With RollUP
Union All
Select '分類', '25 日', '26 日', '27 日', '28 日', '29 日', '30 日', '31 日', '合計' , ''
Union All
Select
CASE
WHEN (GROUPING(Age) = 1) THEN '合計'
ELSE Age
END AS AgeRate,
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 #temp2
Group by Age With RollUP
DROP TABLE #sqltbl2
DROP TABLE #A2
DROP TABLE #sql12
DROP TABLE #intchk2
DROP TABLE #temp2
END
----------------------------------------------------------------------------
Select * from AgeDayhhWeblogin
sp_Statictics_Logon_AgeRate_Daily_Get '2004-08-11'
/*
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
ALTER Procedure dbo.sp_Statictics_Logon_AgeRate_Daily_Get
@SearchDate Varchar(10) = ''
AS
Select '分類', '0 時', '1 時', '2 時', '3 時', '4 時', '5 時', '6 時', '7 時', ''
Union All
Select
CASE
WHEN (GROUPING(AGE) = 1) THEN '合計'
ELSE AGE
END AS Gender,
Convert(Varchar(15),Sum(hh00)) , Convert(Varchar(15),Sum(hh01)) , Convert(Varchar(15),Sum(hh02)) , Convert(Varchar(15),Sum(hh03)) , Convert(Varchar(15),Sum(hh04)) , Convert(Varchar(15),Sum(hh05)) , Convert(Varchar(15),Sum(hh06)) , Convert(Varchar(15),Sum(hh07)) , ''
from dbo.AgeDayhhWeblogin
Where regdate = @SearchDate
Group by AGE With RollUP
Union All
Select '分類', '8 時', '9 時', '10 時', '11 時', '12 時', '13 時', '14 時', '15 時', ''
Union All
Select
CASE
WHEN (GROUPING(AGE) = 1) THEN '合計'
ELSE AGE
END AS Gender,
Convert(Varchar(15),Sum(hh08)) , Convert(Varchar(15),Sum(hh09)) , Convert(Varchar(15),Sum(hh10)) , Convert(Varchar(15),Sum(hh11)) , Convert(Varchar(15),Sum(hh12)) , Convert(Varchar(15),Sum(hh13)) , Convert(Varchar(15),Sum(hh14)) , Convert(Varchar(15),Sum(hh15)) , ''
from dbo.AgeDayhhWeblogin
Where regdate = @SearchDate
Group by AGE With RollUP
Union All
Select '分類', '16 時', '17 時', '18 時', '19 時', '20 時', '21 時', '22 時', '23 時', '合計'
Union All
Select
CASE
WHEN (GROUPING(AGE) = 1) THEN '合計'
ELSE AGE
END AS Gender,
Convert(Varchar(15),Sum(hh16)) , Convert(Varchar(15),Sum(hh17)) , Convert(Varchar(15),Sum(hh18)) , Convert(Varchar(15),Sum(hh19)) , Convert(Varchar(15),Sum(hh20)) , Convert(Varchar(15),Sum(hh21)) , Convert(Varchar(15),Sum(hh22)) , Convert(Varchar(15),Sum(hh23)) ,
Convert(Varchar(15),Sum(AgeDaycnt))
from dbo.AgeDayhhWeblogin
Where regdate = @SearchDate
Group by AGE With RollUP
Go
----------------------------------------------------------------------------
/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Age and Weekend
*/
CREATE Procedure dbo.sp_Statictics_Logon_AgeRate_Weekend_Get
@SearchDate Varchar(10) = ''
AS
Select
'分類'
, Convert(Varchar(10), DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(日)'
, Convert(Varchar(10), DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(月)'
, Convert(Varchar(10), DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(火)'
, Convert(Varchar(10), DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(水)'
, Convert(Varchar(10), DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(木)'
, Convert(Varchar(10), DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(金)'
, Convert(Varchar(10), DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(土)'
, '','合計'
Union All
SELECT
CASE
WHEN (GROUPING(Age) = 1) THEN '合計'
ELSE Age
END AS Gender
, Convert(Varchar(15), SUM(Sun))
, Convert(Varchar(15), SUM(Mon))
, Convert(Varchar(15), SUM(Tue))
, Convert(Varchar(15), SUM(Wed))
, Convert(Varchar(15), SUM(Thu))
, Convert(Varchar(15), SUM(Fri))
, Convert(Varchar(15), SUM(Sat))
, ''
, Convert(Varchar(15), SUM(TotalByAge))
FROM
(
SELECT
AGE ,
CASE
WHEN regdate = DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Sun ,
CASE
WHEN regdate = DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Mon ,
CASE
WHEN regdate = DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Tue ,
CASE
WHEN regdate = DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Wed ,
CASE
WHEN regdate = DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Thu ,
CASE
WHEN regdate = DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Fri ,
CASE
WHEN regdate = DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then Agedaycnt
ELSE 0
END Sat ,
AGEDayCnt As TotalByAge
From
(
Select Regdate, Age, Agedaycnt
from dbo.AgeDayhhWeblogin
Where Regdate >= Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)
And Regdate <= Convert(DateTime, @SearchDate) + (Convert(Int, DatePart(dw, @SearchDate))-1)
) AS TempTable
) AA
GROUP BY AGE With RollUP
Go
sp_Statictics_Logon_AgeRate_Weekend_Get '2004-08-11'
----------------------------------------------------------------------------
sp_Statictics_Unique_Sex_monthly_Get '2004-08-01'
/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Sex and Monthly
*/
ALTER Procedure dbo.sp_Statictics_Unique_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 DayhhWebloginUnique 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
----------------------------------------------------------------------------
/*
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
ALTER Procedure dbo.sp_Statictics_Unique_Sex_Daily_Get
@SearchDate Varchar(10) = ''
AS
Select '分類', '0 時', '1 時', '2 時', '3 時', '4 時', '5 時', '6 時', '7 時', ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh00)) , Convert(Varchar(15),Sum(hh01)) , Convert(Varchar(15),Sum(hh02)) , Convert(Varchar(15),Sum(hh03)) , Convert(Varchar(15),Sum(hh04)) , Convert(Varchar(15),Sum(hh05)) , Convert(Varchar(15),Sum(hh06)) , Convert(Varchar(15),Sum(hh07)) , ''
from dbo.DayhhWebloginUnique
Where regdate = @SearchDate
Group by Sex With RollUP
Union All
Select '分類', '8 時', '9 時', '10 時', '11 時', '12 時', '13 時', '14 時', '15 時', ''
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh08)) , Convert(Varchar(15),Sum(hh09)) , Convert(Varchar(15),Sum(hh10)) , Convert(Varchar(15),Sum(hh11)) , Convert(Varchar(15),Sum(hh12)) , Convert(Varchar(15),Sum(hh13)) , Convert(Varchar(15),Sum(hh14)) , Convert(Varchar(15),Sum(hh15)) , ''
from dbo.DayhhWebloginUnique
Where regdate = @SearchDate
Group by Sex With RollUP
Union All
Select '分類', '16 時', '17 時', '18 時', '19 時', '20 時', '21 時', '22 時', '23 時', '合計'
Union All
Select
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE (CASE sex WHEN 0 THEN '女性' ELSE '男性' END)
END AS Gender,
Convert(Varchar(15),Sum(hh16)) , Convert(Varchar(15),Sum(hh17)) , Convert(Varchar(15),Sum(hh18)) , Convert(Varchar(15),Sum(hh19)) , Convert(Varchar(15),Sum(hh20)) , Convert(Varchar(15),Sum(hh21)) , Convert(Varchar(15),Sum(hh22)) , Convert(Varchar(15),Sum(hh23)) ,
Convert(Varchar(15),Sum(SexDaycnt))
from dbo.DayhhWebloginUnique
Where regdate = @SearchDate
Group by Sex With RollUP
Go
----------------------------------------------------------------------------
/*
Writer : Han Yeong min
RegDate : 2004-08-26
Desc : Logon Statictics By Sex and Weekend
*/
ALTER Procedure dbo.sp_Statictics_Unique_Sex_Weekend_Get
@SearchDate Varchar(10) = ''
AS
Select
'分類'
, Convert(Varchar(10), DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(日)'
, Convert(Varchar(10), DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(月)'
, Convert(Varchar(10), DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(火)'
, Convert(Varchar(10), DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(水)'
, Convert(Varchar(10), DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(木)'
, Convert(Varchar(10), DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(金)'
, Convert(Varchar(10), DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)),121) + '(土)'
, '','合計'
Union All
SELECT
CASE
WHEN (GROUPING(SEX) = 1) THEN '合計'
ELSE SEX
END AS Gender
, Convert(Varchar(15), SUM(Sun))
, Convert(Varchar(15), SUM(Mon))
, Convert(Varchar(15), SUM(Tue))
, Convert(Varchar(15), SUM(Wed))
, Convert(Varchar(15), SUM(Thu))
, Convert(Varchar(15), SUM(Fri))
, Convert(Varchar(15), SUM(Sat))
, ''
, Convert(Varchar(15), SUM(TotalBySex))
FROM
(
SELECT
CASE SEX
WHEN 0 Then '女性'
Else '男性'
End SEX ,
CASE
WHEN regdate = DateAdd(day, 0, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Sun ,
CASE
WHEN regdate = DateAdd(day, 1, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Mon ,
CASE
WHEN regdate = DateAdd(day, 2, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Tue ,
CASE
WHEN regdate = DateAdd(day, 3, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Wed ,
CASE
WHEN regdate = DateAdd(day, 4, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Thu ,
CASE
WHEN regdate = DateAdd(day, 5, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Fri ,
CASE
WHEN regdate = DateAdd(day, 6, Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)) then sexdaycnt
ELSE 0
END Sat ,
SexDayCnt As TotalBySex
From
(
Select Regdate, sex, SexDaycnt
from dbo.DayhhWebloginUnique
Where Regdate >= Convert(DateTime, @SearchDate) - (Convert(Int, DatePart(dw, @SearchDate))-1)
And Regdate <= Convert(DateTime, @SearchDate) + (Convert(Int, DatePart(dw, @SearchDate))-1)
) AS TempTable
) AA
GROUP BY SEX With RollUP
Go
sp_Statictics_Unique_Sex_Weekend_Get '2004-08-11'
----------------------------------------------------------------------------
