CODEWITHSUNDEEP
sql-server
Kramb
Kramb

Reputation: 1092

Select all rows within a date range, with at least one row occurring in the last given month

I've run out of ideas for this one and I'm not sure how to do this whatsoever. I'll give you my current query that gives me the rows I need for the date range;

WITH CTE AS
(SELECT FER.*, COUNT(*) OVER (PARTITION BY FER.Report_Subject, FER.Event_Category) AS Event_Count
FROM FacilityEventReport FER INNER JOIN
FacilityEventReport_RMReview RMR ON FER.ID = RMR.FER_ID
WHERE RMR.Review_Status = 'Active' AND FER.Report_About = 'Resident' AND @BeginDate <= FER.Event_Date AND @EndDate >= FER.Event_Date AND (LEN(ISNULL(@Category,'')) = 0) OR @Category = FER.Event_Category)
SELECT *
FROM CTE
WHERE Event_Count > 1
ORDER BY Report_Subject

The above query returns all events that have occurred within the date parameters @BeginDate and @EndDate. @Category is an optional parameter, only used to filter the query by Event_Category. This query returns all of the requested rows, but I need at least ONE of the rows returned to be in the MONTH of the second date parameter (@EndDate).

This query is used for a report.

The parameters @BeginDate and @EndDate are both of the type DateTime. As is the field Event_Date.

Does anyone have any ideas?

Thanks, Kramb

CLARIFICATION:

Condensed Data:

  Event_Date  |  Report_Subject  |  Event_Category
----------------------------------------------------
2016-01-01    |  Patient 1       |  Aggressive Act
2016-01-02    |  Patient 1       |  Aggressive Act
2016-02-01    |  Patient 1       |  Aggressive Act
2016-01-01    |  Patient 2       |  Fall
2016-01-02    |  Patient 2       |  Fall
2016-03-01    |  Patient 3       |  Fall

If I run the query with the following parameters:

@BeginDate = '2016-01-01';
@EndDate   = '2016-02-01';

I want the following data returned:

  Event_Date  |  Report_Subject  |  Event_Category
----------------------------------------------------
2016-01-01    |  Patient 1       |  Aggressive Act
2016-01-02    |  Patient 1       |  Aggressive Act
2016-02-01    |  Patient 1       |  Aggressive Act

Notice that Patient 1 was returned because they had more than one event of the same type. Also, An event occurred within the month of the second parameter.

Patient 2 did have more than one event, but because those events did not occur in the month of the second parameter the rows were not returned.

Upvotes: 1

Views: 94

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

There are lots of ways. One is to add this to the end of your current query:

...
AND EXISTS(
  SELECT * 
  FROM CTE t2 WHERE t2.Report_Subject=CTE.Report_Subject
  AND DATEDIFF(Month, Event_Date, @EndDate)=0
)

Upvotes: 1

Related Questions