I'm working in SQL Server 2012 and need to create counts of events that occurred prior to a specific point in time.
Let me use an analogy to represent what I'm trying to do because what I'm really doing is so incredibly top secret I would have to violate my own moral underpinnings were I to reveal it here (i.e., I'd tell ya but I'd hafta kill ya). :P
Let's say I have a table of people and the date that each person attended a VanHalen concert in the past 50 years.
I want to count the number of prior VanHalen concerts each person had attended prior to the concert date under examination.
So - let's say a guy named Jim attended a VanHalen concert on 3/27/1986 (to see what this whole Hagar thing was all about).
Prior to that he had been to shows in:
San Diego 12/3/1978,
Seattle 4/5/1980 (A leap year), and
Omaha 8/8/1982 (the Hide Your Sheep tour - cuz that's just funny!)
So Jim attended a total of 5 VH concerts prior to the show on 3/27/1986 (including one during a leap year because - well leap years are just a PITA).
Given the above information - what would be the best way to determine how many concerts each person had attended prior to each concert date while accounting for leap years?
I also want to know how many concerts he attended in the past 1,2,3,4, etc. years.
TIA and well, Rock on!