I'm extremely new to SQL, in fact, I just really started playing around with it. So I'm not exactly sure how to search for appropriate terms related to the question I have. I'll do my best.
I have a table that I'd like to query using SQL Server. That table has behavioral data for hundreds of thousands of animals over multiple days (I know! Lots of animals! Rats, actually, and it is data that has been compiled over many many years by many many people). Here are the columns:
- AnimalID (an identifying ID unique to that animal)
- DateID (Date of observation-- for example, 20151111)
- Behavior1 (# of times observed eating on that DateID-- could be 0, max is 337!)
- Behavior2 (# of times observed grooming on that DateID-- could be 0, max is 36)
- Behavior3 (# of times observed mating/attempted mating on that DateID-- could be 0, max is 77!)
There are other columns like "gender," "AgeInDays," and several more behavior types. I don't think it is worth listing them all out.
I'd like to be able to use WHERE and GROUP BY in a query to summarize this dataset.
So far I have been able to write queries that give me the total number of days that each animal does a behavior or the total number of times that an animal has done a behavior across all records for each animal. For example, I've done this by making WHERE [Behavior1] > 1 and GROUP BY [AnimalID].
This is actually pretty cool! And I'm stunned at how quickly these queries run! But I'm stuck on a different type of query.
I want to write a query that will tell me the number of days (DateID) between the first date an animal (each unique AnimalID) tried to mate (Behavior3) and the second date the same animal tried to mate. In the same output, I'd like to have the number of days between the SECOND date they tried and the THIRD date they tried. All while having WHERE [Behavior1] > 1 and GROUP BY [AnimalID].
Ideally the output would have columns like this:
NumDays1 (number of days between first and second attempts for that AnimalID to mate)
NumDays2 (number of days between the second and third attempts for that AnimalID to mate)
Is this even possible to do in a single query? I know I need to use DATEDIFF... Any help would be greatly appreciated!
Apologies if I've messed up the way I was supposed to ask this question on this forum.