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.