--------------------------------------------------------------------------------------------------------------------------------
--SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks
--use recursive common table expression (CTE) and nested CTEs to accumulate "data dates" over the latest "n" weeks
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-- weekly sums/counts/averages
--------------------------------------------------------------------------------------------------------------------------------
declare
 @parameterDate asdatetime;
set
 @parameterDate ='2/2/2009';--@parameterDate;
declare
 @ProjectID asint;
set
 @ProjectID = 42;
WITH
 DateCTE(WeekStart)AS
)
(SELECTDATEADD(DD, 1 -DATEPART(DW,convert(varchar,@parameterDate,111)),convert(varchar,@parameterDate,111))AS WeekStartUNIONallSELECTDATEADD(DD,-7, WeekStart)AS WeekStart FROM DateCTE WHERE WeekStart <DATEADD(DD, 60, @parameterDate)
--select top 6 WeekStart from DateCTE
,
,
Date6CTE as(SELECTtop 12 WeekStart from DateCTE)Date7CTE as (Select Date6CTE.WeekStart,DatePart(wk, Date6CTE.WeekStart)as Week,DatePart(year, Date6CTE.WeekStart)as WeekYearFROM Date6CTE)
--select * from Date7CTE
,
Date7CTE
DatesSumCTE(WeekStart, Week, WeekYear, ProjectName, ProjectID)as (Selectdistinct .WeekStart,
Date7CTE
.Week,
Date7CTE
.WeekYear,
p
p
.ProjectName, .ProjectIDfrom Date7CTE, Projects AS p where ProjectID = @ProjectID)
--select * from DatesSumCTE
select
 CONVERT(VARCHAR(20), DatesSumCTE.WeekStart, 101)as WeekStartXX,CONVERT(VARCHAR,DATEPART(MM, DatesSumCTE.WeekStart))+'/'+
CONVERT(VARCHAR,DATEPART(d, DatesSumCTE.WeekStart))+'/'+
right(CONVERT(VARCHAR,DATEPART(YY, DatesSumCTE.WeekStart)), 2)as WeekStart
,
 DatesSumCTE.ProjectName
--, DatesSumCTE.ProjectID
,
COUNT(DISTINCT s.ChildLNumber)ASCount
--, DatesSumCTE.WeekYear
--, DatesSumCTE.Week
--, 'Childs Per Week' as SeriesField
FROM
 DatesSumCTE
left
JOIN ParentTable AS r ON DatesSumCTE.ProjectID = r.ProjectID
left
JOIN ChildTable AS s ON s.ParentID = r.ParentID
and
and
and
 s.DateProcessed ISNOTNULL s.DateData ISNOTNULLDATEADD(DD, 1 -DATEPART(DW,convert(varchar,s.DateData,111)),convert(varchar,s.DateData,111))= DatesSumCTE.WeekStart
GROUP
BY CONVERT(VARCHAR(20), DatesSumCTE.WeekStart, 101 )
,CONVERT(VARCHAR,DATEPART(MM, DatesSumCTE.WeekStart))+'/'+
CONVERT(VARCHAR,DATEPART(d, DatesSumCTE.WeekStart))+'/'+
right(CONVERT(VARCHAR,DATEPART(YY, DatesSumCTE.WeekStart)), 2)
,
 DatesSumCTE.ProjectName
--, DatesSumCTE.ProjectID
--, DatesSumCTE.WeekYear
--, DatesSumCTE.Week
--ORDER BY DatesSumCTE.ProjectName, DatesSumCTE.WeekStart
--------------------------------------------------------------------------------------------------------------------------------
-- daily sums/counts/averages
--------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
--days of week for today's date
-----------------------------------------------------------------------
USE
GO
 [dbFAST]
WITH
 DateCTE(WeekStart)AS
)
,
(SELECTDATEADD(DD, 1 -DATEPART(DW,convert(varchar,GetDate(),111)),convert(varchar,GetDate(),111))AS WeekStartUNIONallSELECTDATEADD(DD, 1, WeekStart)AS WeekStart FROM DateCTE WHERE WeekStart <DATEADD(DD, 7,GetDate())Date7CTE as(SELECTtop 7 WeekStart from DateCTE)
Select
WeekStart
 Date7CTE.WeekStart FROM Date7CTE
-----------------------
2009
2009
2009
2009
2009
2009
2009
-09-13 00:00:00.000-09-14 00:00:00.000-09-15 00:00:00.000-09-16 00:00:00.000-09-17 00:00:00.000-09-18 00:00:00.000-09-19 00:00:00.000
-----------------------------------------------------------------------
--results - daily count by project
-----------------------------------------------------------------------
declare
 @parameterDate asdatetime;
set
 @parameterDate ='1/3/2009';--getdate();
WITH
 DateCTE(DayOfWeek)AS
)
,
,
Date7CTE
(SELECTDATEADD(DD, 1 -DATEPART(DW,convert(varchar,@parameterDate,111)),convert(varchar,@parameterDate,111))AS DayOfWeekUNIONallSELECTDATEADD(DD, 1, DayOfWeek)AS DayOfWeek FROM DateCTE WHERE DayOfWeek <DATEADD(DD, 7, @parameterDate)Date7CTE as (SELECTtop 7 DayOfWeek from DateCTE)DatesDataCTE(DayOfWeek, ProjectName, ProjectID)as (Selectdistinct .DayOfWeek,
p
,
.ProjectName, p.ProjectIDfrom Date7CTE, Projects AS p )EmptyProjectWeekCTE(ProjectID,Count)as
(
DatesDataCTE
select .ProjectID,COUNT(DISTINCT s.DataPoint)ASCount
FROM DatesDataCTEleftJOIN Parents AS r ON DatesDataCTE.ProjectID = r.ProjectID leftJOIN Data AS s ON s.ParentID = r.ParentID and s.DateProcessed ISNOTNULL
andconvert(varchar,s.DateDroppedOff,111)=convert(varchar, DatesDataCTE.DayOfWeek,111)
DatesDataCTE
GROUPBY .ProjectID)
select
CASE
DATEPART(weekday,DatesDataCTE.DayOfWeek)
WHEN 1 THEN'Sunday, '
WHEN 2 THEN'Monday, '
WHEN 3 THEN'Tuesday, '
WHEN 4 THEN'Wednesday, '
WHEN 5 THEN'Thursday, '
WHEN 6 THEN'Friday, '
WHEN 7 THEN'Saturday, '
END
+CONVERT(VARCHAR(10), DatesDataCTE.DayOfWeek, 7)AS [Day Date]
--DatesDataCTE.DayOfWeek,
,
 DatesDataCTE.ProjectName
--DatesDataCTE.ProjectID
,
COUNT(DISTINCT s.DataPoint)ASCount
FROM
 DatesDataCTE
left
JOIN Parents AS r ON DatesDataCTE.ProjectID = r.ProjectID
left
JOIN Data AS s ON s.ParentID = r.ParentID
and
and
 s.DateProcessed ISNOTNULLconvert(varchar,s.DateDroppedOff,111)=convert(varchar, DatesDataCTE.DayOfWeek,111)
where
 DatesDataCTE.ProjectID in(select ProjectID from EmptyProjectWeekCTE whereCount> 0)
GROUP
DatesDataCTE
DatesDataCTE
DatesDataCTE
BY .DayOfWeek, .ProjectName, .ProjectID
ORDER
 
BY DatesDataCTE.ProjectName, DatesDataCTE.DayOfWeek