SQL Server – Select Based on count(), Date Range, and String Match

sql server

I'm going to do my best here.

I Have a list of citations that were written and a and some information about those citations. I need to select the citations that have been voided in a given date range and where the person who wrote it also wrote over 5 citation in that same date range.

I want a result that shows all the citations that person 'x' wrote
citation 1 … info,
citation 2 … info,
citation 3 … info,
citation 4 … info,
citation 5 … info,… etc 

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorUserId

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum AND (ds.StatusName LIKE 'void%')

WHERE DateModified BETWEEN @BeginDate AND @EndDate 
--DateModified is when the document was voided--

GROUP BY d.CreatorUserId
HAVING COUNT(d.CreatorUserId) >= @MinVoids

This gives me the right guids but not all of them. I cannot figure out for the life of me why it's not grabbing all of them. (Should be 16 and I only get 11). I have a feeling that COUNT isn't working how I want it to.

And just because im paranoid about sounding ignorant around SQL gurus, I just started learning SQL about two weeks ago.

SOLUTION
There was some information that I left out that could have helped but the answer still helped a lot for getting it solved.

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorFirstName + ' ' + d.CreatorLastName as name
    ,ds.StatusName

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum 

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND (ds.StatusName LIKE 'void%')
    AND ((ds.StatusNum BETWEEN 1 AND 20) OR (ds.StatusNum BETWEEN 100 AND 120))
    AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
            JOIN [ARK].[dbo].[DocStatus] AS dst 
            ON  dc.DocStatus = dst.StatusNum
        WHERE dc.CreatorUserId = d.CreatorUserId
            AND (dst.StatusName LIKE 'void%')
            AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
            AND ((dst.StatusNum BETWEEN 1 AND 20) OR (dst.StatusNum BETWEEN 100 AND 120))
      ) >= @MinVoids
ORDER BY name

Best Answer

It's not a trivial query. You need one more join or a subquery:

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum AND ds.StatusName LIKE 'void%'

WHERE ds.DateModified BETWEEN @BeginDate AND @EndDate 
--DateModified is when the document was voided--

  AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
        WHERE dc.CreatorUserId = d.CreatorUserId
          AND dc.DateCreated BETWEEN @BeginDate AND @EndDate 
      ) >= @MinVoids ;

Not sure about the name of the column (used DateCreated) in table [tblDocument]. Modify accordingly

Related Question