0

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.

5
  • 4
    Have you looked into window functions ? Is this what you are looking for select UserName, EventDate, row_number over (partition by UserName order by EventDate asc) as EventNumber from AllEvents
    – kevinnwhat
    Sep 6, 2019 at 16:20
  • My take on this is that NumberOfEvents is a count - yes? All you appear to want is a row_num by date per user? I think that @kevinnwhat 's answer is what you require - maybe SELECT *, ROW_NUMBER...FROM AllEvents? p.s. welcome to the forum! :-)
    – Vérace
    Sep 6, 2019 at 16:35
  • @kevinnwhat Thank you both so much! This is exactly what I wanted - I didn't realize that the ROW_NUMBER() worked that way with window functions. Such a simple solution too, I feel silly. Please post the answer so I can accept it.
    – Daniel
    Sep 6, 2019 at 17:44
  • @Vérace Yes, it worked. Thank you both, and thank you for the welcome.
    – Daniel
    Sep 6, 2019 at 17:45
  • Maybe EventNumber should be EventOrder?
    – Vérace
    Sep 6, 2019 at 18:19

1 Answer 1

4

This will do it

select UserName, 
       EventDate, 
       row_number over (partition by UserName order by EventDate asc) as EventNumber 
  from AllEvents

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.