Ayende @ Rahien

Casey Duplantis commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 21:30:55 GMT

Here's my stab at it. If I "interpret" what I think your business case is, I think the sample result is off. Here's the total of all open bugs at the end of a month (excludes bugs that are closed in the same month) which is what Ken is deriving: WITH cal AS (SELECT CAST('2007-4-01' AS datetime) AS DateValue UNION ALL SELECT DateValue + 1 AS Expr1 FROM cal AS cal_2 WHERE (DateValue + 1 < '2007-12-31')) SELECT DISTINCT MONTH(cal_1.DateValue) AS M, YEAR(cal_1.DateValue) AS Y, COUNT(DISTINCT Bugs.ID) AS OpenedBugs FROM Bugs INNER JOIN cal AS cal_1 ON Bugs.OpenedAt < cal_1.DateValue WHERE (Bugs.ClosedAt IS NULL) OR (Bugs.ClosedAt > cal_1.DateValue) AND (MONTH(Bugs.ClosedAt) <> MONTH(cal_1.DateValue)) GROUP BY MONTH(cal_1.DateValue), YEAR(cal_1.DateValue) OPTION (MAXRECURSION 0) Results Month Year OpenedBugs 4 2007 1 5 2007 1 6 2007 1 7 2007 2 8 2007 2 9 2007 1 10 2007 1 11 2007 1 12 2007 1 This shows all number of bugs that were open at some time during the month: WITH cal AS (SELECT CAST('2007-04-01' AS datetime) AS DateValue UNION ALL SELECT DateValue + 1 AS Expr1 FROM cal AS cal_2 WHERE (DateValue + 1 < '2007-12-31')) SELECT DISTINCT MONTH(cal_1.DateValue) AS M, YEAR(cal_1.DateValue) AS Y, COUNT(DISTINCT Bugs.ID) AS OpenedBugs FROM Bugs CROSS JOIN cal AS cal_1 WHERE (cal_1.DateValue BETWEEN Bugs.OpenedAt AND Bugs.ClosedAt) OR (Bugs.ClosedAt IS NULL) AND (cal_1.DateValue > Bugs.OpenedAt) GROUP BY MONTH(cal_1.DateValue), YEAR(cal_1.DateValue) OPTION (MAXRECURSION 0) Results Month Year OpenedBugs 4 2007 1 5 2007 1 6 2007 1 7 2007 3 8 2007 2 9 2007 2 10 2007 1 11 2007 1 12 2007 1 Isn't SQL cool?

Ayende Rahien commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:33:42 GMT

Ken, Good catch, I want all bugs opened and closed in a month, so my example data is actually having a bug :-)

Kiliman commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:24:56 GMT

declare @StartDate datetime declare @EndDate datetime declare @MaxDate datetime set @StartDate = '2007-04-01' set @EndDate = '2007-10-01' set @MaxDate = '2999-12-31' declare @t table (Year int, Month int) -- build table for reporting date range declare @d datetime set @d = @StartDate while @d <= @EndDate begin insert @t (Year, Month) values (Year(@d), Month(@d)) set @d = dateadd(month, 1, @d) end -- create aggreate of open bugs per month/year select x.Month, x.Year, OpenedBugs = count(1) from ( -- use date range table to find all bugs that are opened on that month/year -- definition of open is bug that is opened on a month and has not been closed -- in that specific month select t.Year, t.Month, b.Id from @t t inner join Bugs b on (t.Year between Year(b.OpenedAt) and Year(isnull(b.ClosedAt, @MaxDate))) and (t.Month between Month(b.OpenedAt) and Month(isnull(b.ClosedAt, @MaxDate)) - 1) ) x group by x.Month, x.Year

John Hurrell commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:24:15 GMT

Assuming you're okay with NOT seeing months where there are no bugs: SELECT DATEPART(mm, [OpenedAt]) AS [Month], DATEPART(yy, [OpenedAt]) AS [Year], COUNT([Id]) AS [Opened Bugs] FROM [Bugs] GROUP BY DATEPART(mm, [OpenedAt]), DATEPART(yy, [OpenedAt]) ORDER BY DATEPART(mm, [OpenedAt]), DATEPART(yy, [OpenedAt])

Luke Breuer commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:20:32 GMT

The following is a bit of a hack, as you really need a UDF that'll generate the first day of each month that needs reporting. However, the idea is the same. That being said, I suspect that a cursor might well be faster. with Data as ( select 1 Id, '2007-04-02' OpenedAt, '2007-09-01' ClosedAt union select 2, '2007-07-01', NULL union select 3, '2007-07-01', '2007-07-23' ) select seq month, ( select count(*) from Data where month(OpenedAt) <= seq and seq < coalesce(month(ClosedAt), 13) ) open_bugs from ( select 4 seq union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 ) t

Mark Monster commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:12:24 GMT

@Ken. That's exactly right. But I read: "the number of opened bugs". So I was thinking about how many were opened during a month. Not the bugs that are still open.

Ken Egozi commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 13:09:17 GMT

@Ayende: By the example you gave, it seams you want to count bugs that were open at the end of the month (so the third bug isn't calculated into July) Here's a crude stored proc to create a yearly report with that logic. CREATE FUNCTION dbo.GetMonthStart (@Year AS INT, @Month AS INT) RETURNS DATETIME AS BEGIN DECLARE @Date DATETIME SET @Date = CONVERT(DATETIME, '1/' + CONVERT(VARCHAR(2), @Month) + '/' + CONVERT(VARCHAR(4), @Year), 103) RETURN @Date END CREATE FUNCTION dbo.GetNextMonthStart (@Year AS INT, @Month AS INT) RETURNS DATETIME AS BEGIN DECLARE @Date DATETIME SET @Date = DATEADD(MM, 1, dbo.GetMonthStart(@Year, @Month)) RETURN @Date END CREATE PROCEDURE dbo.YearlyBugStatus (@Year AS INT) AS BEGIN SELECT 1 AS [Month], dbo.GetMonthStart(@Year, 1) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 1) AS NextMonthStartAt INTO #Months UNION SELECT 2 AS [Month], dbo.GetMonthStart(@Year, 2) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 2) AS NextMonthStartAt UNION SELECT 3 AS [Month], dbo.GetMonthStart(@Year, 3) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 3) AS NextMonthStartAt UNION SELECT 4 AS [Month], dbo.GetMonthStart(@Year, 4) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 4) AS NextMonthStartAt UNION SELECT 5 AS [Month], dbo.GetMonthStart(@Year, 5) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 5) AS NextMonthStartAt UNION SELECT 6 AS [Month], dbo.GetMonthStart(@Year, 6) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 6) AS NextMonthStartAt UNION SELECT 7 AS [Month], dbo.GetMonthStart(@Year, 7) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 7) AS NextMonthStartAt UNION SELECT 8 AS [Month], dbo.GetMonthStart(@Year, 8) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 8) AS NextMonthStartAt UNION SELECT 9 AS [Month], dbo.GetMonthStart(@Year, 9) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 9) AS NextMonthStartAt UNION SELECT 10 AS [Month], dbo.GetMonthStart(@Year, 10) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 10) AS NextMonthStartAt UNION SELECT 11 AS [Month], dbo.GetMonthStart(@Year, 11) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 11) AS NextMonthStartAt UNION SELECT 12 AS [Month], dbo.GetMonthStart(@Year, 12) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 12) AS NextMonthStartAt SELECT m.Month AS [Month], @Year AS [Year], ( SELECT COUNT (b.Id) FROM Bugs b WHERE b.OpenedAt < m.NextMonthStartAt AND ( b.ClosedAt >= m.NextMonthStartAt OR b.ClosedAt IS NULL) ) AS [Bugs] from #Months m DROP TABLE #Months END EXEC dbo.YearlyBugStatus 2007 As I said, it's crude. But works The temp table is needed for "empty" months, and for keeping the NextMonthStart computation for once per month.

Ken Egozi commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 12:34:25 GMT

@Mark: I think yours is calculation how many were OPENED during a month, not how many were OPEN.

Mark Monster commented on SQL Challenge, getting historical data

Fri, 10 Aug 2007 12:17:46 GMT

Is this what you want? SELECT MONTH(OpenedAt) AS 'Month', Year(OpenedAt) AS 'Year', COUNT([Id]) AS 'Opened Bugs' FROM Bugs GROUP BY MONTH(OpenedAt),Year(OpenedAt)