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.
create a variable called month_var that loops from 1-12 then do a query like below:
select * from cats where month(ts_birth) = month_var;
and store the results into your other table
I'm not sure I understand the question, but if you are saying that “current cats” means the “not adopted” field is not populated, AND, that the number of cats is static (that is, no new cats are arriving), then wouldn't this be:
select nvl(to_char(ts_adoption, 'YYYY-MM'), to_char(sysdate, 'YYYY-MM')) the_month, count(decode(ts_adoption, null, 1, not null, 1)) num_cats
from cats
group by ts_adoption
I'm not sure I understand the question, but if you are saying that “current cats” means the “not adopted” field is not populated, AND, that the number of cats is static (that is, no new cats are arriving), then wouldn't this be:
select nvl(to_char(ts_adoption, 'YYYY-MM'), to_char(sysdate, 'YYYY-MM')) the_month, count(decode(ts_adoption, null, 1, not null, 1)) num_cats
from cats
group by ts_adoption
Hi Don, Thanks for that – you are partially correct – current cats means not addopted, so the TS_adoption field is blank. However, the number of cats changes over time, as more cats arrive.
I have temporarily solved this by creating a script that writes the current cats every day at midnight, and then using that to generate charts and whatnot. It feels less elegent, but is super-fast and likely vastly more scalable.