SQL Data conundrum – please help!

So, I need to chart this “change over time” graph. And I feel like it should be really easy, but it’s totally breaking my brain how to write the query to do this right now, so I’m hoping someone out there might be able to help. So here’s the issue. I have a table, “cats”. Cats have a birthday and & adoption date. If they’re not adopted, there’s no date. And I want a chart of “current cats” over time. Which seems like it should be easy, but I can’t figure it out right now. Here’s the sample data:

Cat TS_Birth TS_Adoption
Henry 2009-4-4
Fluffy 2009-6-4 2009-7-29
Spots 2009-7-29 2009-10-1
Boots 2009-8-1

So now how could I write a query that’ll give me something like (we’ll assume we counting the number of cats available at 11:59:59pm on the last day of the month):

Num Cats Month
1 May
2 June
2 July
3 August
3 September
2 October

Oh brain, why hast thou abandoned me? If anyone could help me with the SQL, I would be very very grateful.

%d bloggers like this: