0

I have the following table:

+---------+---------+------------------+----------------------+----------------------+
| shop_id | card_id |      event       |   event_timestamp    |     card_expires     |
+---------+---------+------------------+----------------------+----------------------+
|       1 |       1 | some event       | 2019-10-01T00:00:00z | 2019-10-10T00:00:00Z |
|       1 |       1 | some other event | 219-10-02T00:00:00Z  | 2019-10-10T00:00:00Z |
|       1 |       2 | an event         | 2019-10-03T00:00:00Z | 2019-10-29T00:00:00Z |
|       1 |       2 | another event    | 2019-10-04T00:00:00Z | 2019-10-29T00:00:00Z |
|       1 |       3 | foo event        | 2019-10-03T00:00:00Z | 2019-10-10T00:00:00Z |
|       1 |       3 | bar event        | 2019-10-04T00:00:00Z | 2019-10-10T00:00:00Z |
+---------+---------+------------------+----------------------+----------------------+

I'm trying to find the total number of expired cards for a shop_id. If we say that current timestamp is 2019-10-20T00:00:00Z then card_id 1 and 2 will be expired.

Expected Result:

+---------+-------------------------+
| shop_id | number_of_expired_cards |
+---------+-------------------------+
|       1 |                       2 |
+---------+-------------------------+

Ive gone around in circles with INNER JOIN, CASE and GROUP BY. I can identify the cards that have expired but then when I try to sum them into the results table, I get bigger values than I need. Totally stuck ...

2
SELECT shop_id, COUNT(DISTINCT card_id) number_of_expired_cards 
FROM sourcetable
WHERE card_expires < GETDATE()
GROUP BY shop_id
| improve this answer | |
  • ah thanks a lot! I knew i'd made a total mess of it and forgot some simple way to do it – Gaz_Edge Oct 29 '19 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.