declare @StartDate datetime,@EndDate datetime
set @StartDate='20140101'
set @EndDate='20140131'
select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
dateadd(day, Number.number, @StartDate) <= @EndDate and
datepart(dw, dateadd(day, Number.number, @StartDate)) in ( 1,7)
SELECT dATE , COUNT(dATE) FROM (
SELECT TOP (datediff(DAY,@StartDate,@EndDate) + 1 ) [Date] = DATENAME(dw , dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name),
DATEADD(DD,-1,@StartDate)))
FROM [master].[dbo].[spt_values] c1 ) X
GROUP BY Date
-- mondays count with replication
DECLARE
@StartDate date = '2014-01-01',
@EndDate date = '2014-01-31';
WITH A AS (SELECT DayCount = DateDiff(day, @StartDate, @EndDate) + 1),
B AS (
SELECT
DayCount,
WeekCount = DayCount + 6 / 7,
Dow = DateDiff(day, '18991230', @StartDate) % 7 FROM A
)
SELECT
MondayCount =
Len(Replace(Substring(
Replicate('0100000', WeekCount),
Dow, DayCount
), '0', ''))
FROM B;
set @StartDate='20140101'
set @EndDate='20140131'
select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
dateadd(day, Number.number, @StartDate) <= @EndDate and
datepart(dw, dateadd(day, Number.number, @StartDate)) in ( 1,7)
SELECT dATE , COUNT(dATE) FROM (
SELECT TOP (datediff(DAY,@StartDate,@EndDate) + 1 ) [Date] = DATENAME(dw , dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name),
DATEADD(DD,-1,@StartDate)))
FROM [master].[dbo].[spt_values] c1 ) X
GROUP BY Date
-- mondays count with replication
DECLARE
@StartDate date = '2014-01-01',
@EndDate date = '2014-01-31';
WITH A AS (SELECT DayCount = DateDiff(day, @StartDate, @EndDate) + 1),
B AS (
SELECT
DayCount,
WeekCount = DayCount + 6 / 7,
Dow = DateDiff(day, '18991230', @StartDate) % 7 FROM A
)
SELECT
MondayCount =
Len(Replace(Substring(
Replicate('0100000', WeekCount),
Dow, DayCount
), '0', ''))
FROM B;
No comments:
Post a Comment