I had trouble phrasing the question, but I think the following example will clear things up.
Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in one of the tables.
I have a SQL Table (let's call it AllEvents) in the database with a format like this:
UserName | Grouping | EventDate | NumberOfEvents ________________________________________________________ Alice | Red Grouping | 1/1/2000 | 1 Alice | Red Grouping | 1/2/2000 | 2 Alice | Red Grouping | 1/3/2000 | 3 Alice | Blue Grouping | 1/4/2000 | 1 Alice | Blue Grouping | 1/5/2000 | 2 Anderson | Red Grouping | 1/3/2000 | 1 Anderson | Blue Grouping | 1/5/2000 | 1 Anderson | Green Grouping | 1/6/2000 | 1 Anderson | Green Grouping | 1/7/2000 | 2
And I want to select the results such that I get an output of the following form, where it counts the number of events for the UserName before and up to the row's EventDate.
UserName | EventDate | EventNumber _______________________________________ Alice | 1/1/2000 | 1 Alice | 1/2/2000 | 2 Alice | 1/3/2000 | 3 Alice | 1/4/2000 | 4 Alice | 1/5/2000 | 5 Anderson | 1/3/2000 | 1 Anderson | 1/5/2000 | 2 Anderson | 1/6/2000 | 3 Anderson | 1/7/2000 | 4
We can ignore the grouping column, since we're only interested in the rows with the same UserName.
What I have been trying to do is use a subquery like below:
SELECT SUM ( CASE WHEN AE.EventDate> AllEvents.EventDate THEN 1 ELSE 0 END ) OVER (PARTITION BY UserName, Grouping) FROM AllEvents AS AF WHERE AE.UserName= AllEvents.PatientId AND AE.EventDate= AllEvents.EventDate
In the following query:
SELECT UserName, EventDate, [The subquery above] AS EventNumber FROM AllEvents
However, the subquery is returning more than one value and I'm struggling to determine how to troubleshoot.
I have also been toying with the COUNT function, but have failed to have it count conditionally based upon the current row's EventDate value.
Any advice or suggestions would be appreciated. I have been trying to be creative in my approach, but I don't know if I've hit the best method.