HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Doing a select based on a count(), a date range, and string match

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rangeselectdoingmatchdatebasedandcountstring

Problem

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

Solution

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

Code Snippets

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 ;

Context

StackExchange Database Administrators Q#103840, answer score: 3

Revisions (0)

No revisions yet.