CODEWITHSUNDEEP
mysqlsql
user2179615
user2179615

Reputation: 127

SQL DML SCRIPTS

For a given period (bounded by two given dates inclusive), say 01 December 2002 to 31 July 2003, find the set of the people who have at least one EZ link card issued within the specified period. For EACH person in the set, list the (i) Owner NRIC, (ii) the total number of his/her cards issued during the period, and (iii) total number of card replacements (till current date) that have been made by the owner for only those cards that were issued during the given period. List your results in ascending order of Owner NRIC. 

create table card
(
CardID  int not null primary key,
OwnerNRIC   char(9),
IssuedDcardate  date,
StoredValue decimal (5,2),
OldCardID   int,
constraint card_fk foreign key (OldCardID) references card(CardID)
);

As above is my card table.

This is my attempt at it but i was pretty lost.

 Select distinct(ownerNRIC) as NRIC,count(*) as Total Crads Issued during Period
From card
Where issuedDate between ‘2002-12-01’ and ‘2003-7-31’ group by ownerNRIC;

Upvotes: 2

Views: 299

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

I'm assuming a card replacement results in a non-null OldCardId. If OldCardId is NULL, then this was not a card replacement. Take advantage of the way COUNT(expr) ignores rows where expr is NULL.

SELECT ownerNRIC AS NRIC, 
  COUNT(*) AS `Total Cards Issued During Period`,
  COUNT(OldCardID) AS `Card Replacements During Period`
FROM card
WHERE issuedDate BETWEEN '2002-12-01' AND '2003-7-31' 
GROUP BY ownerNRIC;

You were using DISTINCT incorrectly. It's not a function, and it's unnecessary anyway because ownerNRIC was your GROUP BY column.

total number of card replacements (till current date) that have been made by the owner for only those cards that were issued during the given period

This requirement is not clear. Do you mean the old card was issued during the given period, and the new card was issued anytime up to the current date? If so, the query above won't give you that.

Upvotes: 1

Related Questions