Number of VanHalen concerts in past_X years prior to a point in time


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:
Chicago 3/3/1978,
Milwaukee 3/9/1978,
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!
Who is Participating?
David Johnson, CD, MVPOwnerCommented:
nothing wrong with leap years.
not knowing how your data tables are arranged makes things difficult. A Sql diagram would be nice.
pseudo code
#date = "01/01/1990 00:00:00"
select * from concerts where concetdate < #DATE# where artist = 'Van Halen" AND user = "Jim'
ttist25Author Commented:
THanks David.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.