0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

SELECT 
  (
    SELECT COUNT(p.Id)  
    FROM Posts p
    WHERE 
    p.PostTypeId = 1
    AND CONVERT(date, p.CreationDate) >= CONVERT(date, ##startDate:string?2024-04-22##, 23)
    AND CONVERT(date, p.CreationDate) <= CONVERT(date, ##endDate:string?2024-04-29##, 23)
  ) AS "Total Questions" ,(
    SELECT COUNT(p.Id)
    FROM Posts p 
      JOIN Votes v on v.PostId = p.Id AND p.PostTypeId = 1
      JOIN VoteTypes vt on v.VoteTypeId = vt.Id
    WHERE 
      -- gotta do some silly conversion to extract/use just the date part of a datetime
      -- the '23' as the third param tells T-SQL to assume the date format is ISO
      CONVERT(date, p.CreationDate) >= CONVERT(date, ##startDate:string?2024-04-22##, 23)
      --startDate: The day to start from, YYYY-MM-DD
      AND CONVERT(date, p.CreationDate) <= CONVERT(date, ##endDate:string?2024-04-29##, 23)
      --endDate: The day to stop at, YYYY-MM-DD
      AND v.CreationDate < DATEADD(##timeUnit?hour##, ##numTimeUnits:int?2##, p.CreationDate)
      --timeUnit: How granular do you want to go? day/hour/minute/second
      --numTimeUnits: The number of days/hours/minutes/seconds
      AND vt.Name IN ('DownMod')
  )  AS "Downvoted"

fork query

edited may 1 Derek.F

created may 1 anonymous