0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

;With QuestionDays As (
    Select
        OwnerUserId AS UserId,
        Count(*) AS Total,
        Sum(Case When Score < 0 Then 1 Else 0 End) AS Negative,
        Sum(Case When Score > 0 Then 1 Else 0 End) AS Positive,
        Sum(Case When ClosedDate Is Not Null and datediff(day, CreationDate, ClosedDate) < 60 Then 1 Else 0 End) AS Closed,
        Sum(Case When DeletionDate Is Not Null and datediff(day, CreationDate, DeletionDate) < 60 Then 1 Else 0 End) AS Deleted
    From Posts
    Where PostTypeId=1
    And CreationDate < cast(getutcdate() as date) -- only look at full days
    Group By OwnerUserId, Cast(CreationDate As Date)
)
Select UserId,
		SUM(Total) AS Total,
		SUM(Negative) AS Negative,
		SUM(Positive) AS Positive,
		SUM(Closed) AS Closed,
		SUM(Deleted) AS Deleted,
		SUM(Case When Negative=0 And Deleted=0 And Closed=0 And Positive>0 Then 1 Else 0 End) AS GoodDays,
		SUM(Total - Negative - Closed - Deleted) AS GoodRecordDays,
		FORMAT(CAST(Sum(Total - Negative - Closed - Deleted) as Float) / Sum(Total), 'P2') AS GoodRecordPercent,
		Cast((Case When Cast(Sum(Total - Negative - Closed - Deleted) as Float) / Sum(Total) >= .5 Then 1 Else 0 End) as Bit) AS HasGoodRecord
From QuestionDays qd
Where qd.UserId = ##UserId##
Group By UserId

fork query

edited may 24 22

created may 24 22 KyleMit