/*
USE [RohanLog]
GO
/****** オブジェクト: View [dbo].[vComWeekList] スクリプト日付: 12/15/2008 12:53:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
// #### INNO ####
// ObjectName : vComWeekList
// Description : 주간 정보 검색
// Author Info : 2008-06-17 by inno
// Modified Info :
// Usage, EX) : vComWeekList
Select * From vComWeekList Where WeekStDay >= DateAdd(dd, -6, '2007-12-25') and WeekEndDay <= DateAdd(dd, 6, '2008-03-20')
Order by WeekStDay
Return :
*/
create view [dbo].[vComWeekList]
as
Select Min(TempDay ) WeekStDay, Max( TempDay) WeekEndDay ,WeekNo From
(
Select A.TempDay, DateDiff(dd, '1995-01-01' ,TempDay)/7 WeekNo
-- 검색주간별 시작일자와 종료일자를 구하기 위한 검색(WeekNo가 같으면 같은주)
-- NotExistWeekNo가 0이아니면 검색대상제외 주
From dbo.ComDayList A,
(
Select Min( DateDiff(dd, '1995-01-01' ,TempDay)/7) MinWeekNo, Max( DateDiff(dd, '1995-01-01' ,TempDay)/7) MaxWeekNo
--1995년 1월1일 기준으로 검색기간내 시작주와 마지막주 검색 및 검색 마지막일자( 만약 마지막일자가 마지막주 마지막이 아닐경우 마지막주는 검색대상에서 제외하기위해 필요) 검색
From dbo.ComDayList A
Where A.TempDay Between '2000-01-01' and '2029-12-31'
) B
Where DateDiff(dd, '1995-01-01' ,TempDay)/7 between MinWeekNo and MaxWeekNo
) A
Group By WeekNo
*/
DECLARE @StDay Smalldatetime
DECLARE @EnDay Smalldatetime
DECLARE @Member Varchar(16)
DECLARE @UseTime int
SET @StDay = '2008-12-05'
SET @EnDay = '2009-01-10'
SET @Member = '214745'
SET @UseTime = 300
SELECT * FROM
(
SELECT 1 Code, WeekStDay, RIGHT(CONVERT(VARCHAR(10), D0, 111),5) D0
, RIGHT(CONVERT(VARCHAR(10), D1, 111),5) D1
, RIGHT(CONVERT(VARCHAR(10), D2, 111),5) D2
, RIGHT(CONVERT(VARCHAR(10), D3, 111),5) D3
, RIGHT(CONVERT(VARCHAR(10), D4, 111),5) D4
, RIGHT(CONVERT(VARCHAR(10), D5, 111),5) D5
, RIGHT(CONVERT(VARCHAR(10), D6, 111),5) D6 FROM
(
SELECT WeekStDay, WeekStDay D0, DATEADD(DD,1,WeekStDay) D1, DATEADD(DD,2,WeekStDay) D2, DATEADD(DD,3,WeekStDay) D3, DATEADD(DD,4,WeekStDay) D4, DATEADD(DD,5,WeekStDay) D5, DATEADD(DD,6,WeekStDay) D6 FROM
(
SELECT TOP 5 DATEADD(day, - 4, WeekStDay) AS WeekStDay, DATEADD(day, - 4, WeekEndDay) AS WeekEndDay, WeekNo FROM dbo.vComWeekList WHERE WeekEndDay >= @StDay AND WeekStDay <= @EnDay ORDER BY WeekStDay
) A
) A
union all
SELECT 2 Code, WeekStDay
, CASE WHEN D0 > 0 THEN '出席' ELSE '-' END D0
, CASE WHEN D1 > 0 THEN '出席' ELSE '-' END D1
, CASE WHEN D2 > 0 THEN '出席' ELSE '-' END D2
, CASE WHEN D3 > 0 THEN '出席' ELSE '-' END D3
, CASE WHEN D4 > 0 THEN '出席' ELSE '-' END D4
, CASE WHEN D5 > 0 THEN '出席' ELSE '-' END D5
, CASE WHEN D6 > 0 THEN '出席' ELSE '-' END D6
FROM
(
SELECT
A.WeekStDay
,SUM(CASE WHEN LogDate = A.WeekStDay THEN UseTime ELSE 0 END) D0
,SUM(CASE WHEN LogDate = DATEADD(DD, 1, A.WeekStDay) THEN UseTime ELSE 0 END) D1
,SUM(CASE WHEN LogDate = DATEADD(DD, 2, A.WeekStDay) THEN UseTime ELSE 0 END) D2
,SUM(CASE WHEN LogDate = DATEADD(DD, 3, A.WeekStDay) THEN UseTime ELSE 0 END) D3
,SUM(CASE WHEN LogDate = DATEADD(DD, 4, A.WeekStDay) THEN UseTime ELSE 0 END) D4
,SUM(CASE WHEN LogDate = DATEADD(DD, 5, A.WeekStDay) THEN UseTime ELSE 0 END) D5
,SUM(CASE WHEN LogDate = DATEADD(DD, 6, A.WeekStDay) THEN UseTime ELSE 0 END) D6
FROM
(
SELECT A.WeekStDay, B.TempDay LogDate, C.MemberID, C.UseTime
FROM
(
SELECT TOP 5 DATEADD(day, - 4, WeekStDay) AS WeekStDay, DATEADD(day, - 4, WeekEndDay) AS WeekEndDay, WeekNo FROM dbo.vComWeekList WHERE WeekEndDay >= @StDay AND WeekStDay <= @EnDay ORDER BY WeekStDay
) A JOIN dbo.ComDayList B
ON A.WeekStDay <= B.TempDay AND A.WeekEndDay >= B.TempDay
LEFT OUTER JOIN dbo.AttendLog C
ON B.TempDay = C.LogDate AND C.MemberID = @Member AND C.UseTime >= @UseTime
) A
GROUP BY A.WeekStDay
) A
) A
order by WeekStDay, Code
