CREATE TABLE [dbo].[tComDay](
[Idx] [int] NOT NULL,
[ComDay] [datetime] NOT NULL,
CONSTRAINT [PK_tComDay] PRIMARY KEY CLUSTERED
(
[Idx] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tComDay_ComDay] ON [dbo].[tComDay]
(
[ComDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--######## データの登録は2999年１２月３１日まで登録する。 ##############################################
DECLARE @i int
SET @i = 0
WHILE @i <= 365242
BEGIN
INSERT INTO [dbo].[tComDay]
SELECT @i+1, DATEADD(DD, @i, '2000-01-01')
SET @i = @i + 1
END
--######## データを確認 ##############################################
SELECT TOP 10 * FROM [dbo].[tComDay] ORDER BY Idx
SELECT
DATEADD(MI, B.StartMin, A.ComDay) StartDate
, DATEADD(MI, B.EndMin, A.ComDay) EndDate
FROM dbo.tComDay A WITH (NOLOCK)
, (
SELECT (Idx-1)*10 AS StartMin ,Idx*10 AS EndMin FROM dbo.tComDay WITH (NOLOCK) WHERE Idx < ((60/10)*24)
) B
WHERE ComDay >= '2013-07-01' AND ComDay < '2013-08-01'
ORDER BY DATEADD(MI, B.StartMin, A.ComDay)
SELECT
DATEADD(MI, B.StartMin, A.ComDay) StartDate
, DATEADD(MI, B.EndMin, A.ComDay) EndDate
FROM dbo.tComDay A WITH (NOLOCK)
, (
SELECT (Idx-1)*20 AS StartMin ,Idx*20 AS EndMin FROM dbo.tComDay WITH (NOLOCK) WHERE Idx < ((60/20)*24)
) B
WHERE ComDay >= '2013-07-01' AND ComDay < '2013-08-01'
ORDER BY DATEADD(MI, B.StartMin, A.ComDay)
DECLARE @TempMonth char(7)
SET @TempMonth = '2013-05'
DECLARE @TempStartDate char(10), @TempEndDate char(10), @TempWeekStartDate char(10)
SET @TempStartDate = @TempMonth+'-01'
SET @TempEndDate = CONVERT(CHAR(10), DATEADD(MM,1,@TempStartDate), 120)
SET @TempWeekStartDate = CONVERT(CHAR(10),DATEADD(DD, ((DATEPART(DW,@TempStartDate))-1)*(-1), @TempStartDate), 120)
SELECT
Weekend, MAX(D1) D1, MAX(D2) D2, MAX(D3) D3, MAX(D4) D4, MAX(D5) D5, MAX(D6) D6, MAX(D7) D7
FROM
(
SELECT (DATEDIFF(dd, @TempWeekStartDate ,ComDay)/7)+1 Weekend
, CASE WHEN DATEPART(DW,ComDay) = 1 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D1
, CASE WHEN DATEPART(DW,ComDay) = 2 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D2
, CASE WHEN DATEPART(DW,ComDay) = 3 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D3
, CASE WHEN DATEPART(DW,ComDay) = 4 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D4
, CASE WHEN DATEPART(DW,ComDay) = 5 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D5
, CASE WHEN DATEPART(DW,ComDay) = 6 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D6
, CASE WHEN DATEPART(DW,ComDay) = 7 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D7
FROM dbo.tComDay WHERE ComDay >= @TempStartDate AND ComDay < @TempEndDate
) A
GROUP BY Weekend
ORDER BY Weekend