Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
--------------------------------------------------------------------------------------------------------------------------------
--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 Posted on Tuesday, September 29, 2009 10:32 AM SQL Server 2005 Tricks , javascript - ajax | Back to top


Comments on this post: SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks

# re: SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks
Requesting Gravatar...
I think I am not good enough for this soft. I can not understand itprom dresses
Left by GG on Mar 29, 2010 12:23 AM

# re: SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks
Requesting Gravatar...
This is a really good read for me, Must admit that you are one of the best bloggers I ever saw.Thanks for posting this informative article
Left by paper writing on Oct 19, 2010 2:10 AM

# re: SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks
Requesting Gravatar...
thanks for the post hope it would help others also
free Addiction Books
free Romance Books
Left by Interview Questions on Nov 22, 2010 12:47 PM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net