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.