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.

4 Replies to “SQL Data conundrum – please help!”

  1. 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

  2. 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

  3. 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

  4. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.