Start Free Trial

asked on

Function in Query

Dear sir
i have this query which it will give the status of my cards
SELECT TC70.[Account], TC70.[CUSTOMER NAME], status.[CARD STATUS]
FROM TC70 INNER JOIN cards_status_04122006 ON TC70.[Shadow Account] = cards_status_04122006.CLIENT
WHERE (((cards_status_04122006.[CARD STATUS])="NORMAL" Or (cards_status_04122006.[CARD STATUS])="STOP" Or (cards_status_04122006.[CARD STATUS])="CANCELLED") AND ((cards_status_04122006.[EXP DATE])=(select MAX([EXP DATE]) FROM cards_status_04122006 Where [TC70.Shadow Account]=[cards_status_04122006.CLIENT] )))
GROUP BY TC70.[Shadow Account], TC70.[CUSTOMER NAME], cards_status_04122006.[CARD STATUS];
My Problem is that there is account numbers that have 2 cards one with status Normal that will expired on 2007 and and another with status cancelled that will expired on 2008 what if there like and if cluse i can use so if the same account number have 2 cards one them is normal to give the normal and ignore the cancelled even if the expiry dat for the cancelled is bigger

SELECT
TC70.[Account],
TC70.[CUSTOMER NAME],
status.[CARD STATUS]
FROM TC70 INNER JOIN cards_status_04122006 ON TC70.[Shadow Account] = cards_status_04122006.CLIENT
WHERE (((cards_status_04122006.[CARD STATUS])="NORMAL" Or (cards_status_04122006.[CARD STATUS])="STOP" Or (cards_status_04122006.[CARD STATUS])="CANCELLED") AND ((cards_status_04122006.[EXP DATE])=(select MAX([EXP DATE]) FROM cards_status_04122006 Where [TC70.Shadow Account]=[cards_status_04122006.CLIENT] AND cards_status_04122006.[CARD STATUS])<>"CANCELLED" )))
GROUP BY TC70.[Shadow Account], TC70.[CUSTOMER NAME], cards_status_04122006.[CARD STATUS];

ASKER

this will make dullicate values for me what i need is the last status for the account number and if there is normal status between then make that one the last status

ASKER

sorry but in the query u give me above if the account number have only cancelled status it will show it

ASKER

i mean it will not show it

ASKER

is there an function to solve this problem

What in your data identifies the card?

ASKER

THE A\C NUMBER HAVE MANY CARDS AND THE CARD HAVE MANY STATUS

ASKER

SORY I MEAN EVRY CARD HAVE ONE STATUS SO WHAT I NEED IS THE LAST STAUS FOR THE A/C NUMBER SO FOR EXAMPLE
A/C CARD_NO EXIPRYDATE STATUS

123456 12345612 10/10/2007 NORMAL

123456 45678912 11/10/2008 CANCELLD

222555 7894556 10/10/2007 CACELLED

SO WHEN I MAKE THE QUERY IF A/C NUMBER HAVE STATUS NORMAL AND
CAELLED SHOW ME THE NORMAL STATUS EVEN IF ITS EXPDATE LESS THAN EXPDATE OF CANCELLED WHITH QUERY ABOVE IT WILL SHOW THE STATUS FOR THE FIRST A/C BUT IT WILL NOT SHOW THE OTHER ONE

osama120: I'm still working on it. Please be patient. Maybe tomorrow.

osama120: I'm still working on it. Please be patient. Maybe tomorrow.

Ignore that last post - finger problem.

ASKER

ok no problem

Can there ever be more than two different cards per account?

If an account with two cards one of which had an expiry date befoe the expiry date of the second card, I presume you would not want to see that expired card either?

ASKER

yes some account have 2 different cards

ASKER

it ok what i need is the normale status

It looks like we are not communicating. Are there any accounts with more than two cards? If a second card had an expiry date befoe the expiry date of the second card, I presume you would not want to see that expired card either? Remembeing, you do not want to see the Cancelled card of the Account with two cards where the 'Cancelled' card had an ExpiryDate later than the valid card.

Cancel previous

It looks like we are not communicating. Are there any accounts with more than two cards? If a second card had an expiry date befoe the expiry date of the first card, I presume you would not want to see that expired card either? Remembeing, you do not want to see the Cancelled card of the Account with two cards where the 'Cancelled' card had an ExpiryDate later than the valid card.

Well, finally. What a stinker of a problem. Try this:

Select a.* from mytable as a where a.status in ("Normal","Stop")
UNION
SELECT b.* FROM myTable AS b WHERE b.Status="Cancelled" AND b.AcctNo IN (Select c.AcctNo FROM myTable AS c GROUP BY c.AcctNo HAVING Count(c.AcctNo)=1)

I made up a small table with the fields:

AcctNo, CardNo, ExpDate, and Status with the three records you showed.

AcctNo      CardNo      ExpDate      Status
222555      234589      2007-06-12      CANCELLED
123456      456123      2008-11-11      CANCELLED
123456      456789      2007-11-10      NORMAL

The query returned this:

AcctNo      CardNo      ExpDate      Status
123456      456789      2007-11-10      NORMAL
222555      234589      2007-06-12      CANCELLED

Hope that is what you wanted.

ASKER

i have to put this query with to cards_status_04122006 only

ASKER

about your question there is some a/c like this
123456 111111 2008-11-11 CANCELLED
123456 222222 2007-11-10 NORMAL
123456 333333 2007-11-10 STOP
123456 444444 2008-11-10 Replaced

You don't need the join on table TC70?

Please detail which fields you want to see from which tables. Having got as far as you did with the query you used in your question, I thought you would have been able to transpose my query to your situation easier than me.

ASKER

see what i will make i will use your query with cards_status_04122006 then i will make the JOIN between it and between the Tc70 tbl

OK, I'm not certain I should be doing anything now. I'll stand by to help if you have any trouble.

Because you have added "Replaced" make sure you include it in the WHERE clause:

WHERE a.status IN ("Normal","Stop","Replaced")

ASKER

SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE], Cards_Status.[CARD STATUS] FROM Cards_Status where Cards_Status.[CARD STATUS] in ( "NORMAL","STOP","REPLACED");
UNION SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE],Cards_Status.[CARD STATUS] FROM Cards_Status WHERE Cards_Status.[CARD STATUS]="CANCELLED" AND Cards_Status.CLIENT IN (Select Cards_Status.CLIENT FROM Cards_Status GROUP BY Cards_Status.CLIENT HAVING Count(Cards_Status.CLIENT)=1);

ASKER

the problem with query it take too match time

What is too much time? How many records in the table?

ASKER

12000

ASKER

210585      111111111       30-Apr-06      EXPIRED
210585      222222222       30-Apr-08      REPLACED
210585      333333333       30-Apr-08      REPLACED
210585      444444444       30-Apr-08      REPLACED
210585      555555555 30-Apr-08      CANCELLED
The Only A/c i have that not apper with your query after i make change for it the new query is

SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE], Cards_Status.[CARD STATUS] FROM Cards_Status where Cards_Status.[CARD STATUS] in ( "NORMAL");
UNION
SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE],Cards_Status.[CARD STATUS] FROM Cards_Status WHERE (((Cards_Status.[CARD STATUS])="NORMAL" Or (Cards_Status.[CARD STATUS])="CANCELLED" Or (Cards_Status.[CARD STATUS])="STOP" OR (Cards_Status.[CARD STATUS])="REPLACED" OR (Cards_Status.[CARD STATUS])="EXPIRED")) AND Cards_Status.CLIENT IN (Select Cards_Status.CLIENT FROM Cards_Status GROUP BY Cards_Status.CLIENT HAVING Count(Cards_Status.CLIENT)=1);

ASKER

sorry this one
SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE], Cards_Status.[CARD STATUS] FROM Cards_Status where Cards_Status.[CARD STATUS] in ( "NORMAL","REPLACED");
UNION SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE],Cards_Status.[CARD STATUS] FROM Cards_Status
WHERE ((( Cards_Status.[CARD STATUS])="CANCELLED" Or (Cards_Status.[CARD STATUS])="STOP" OR (Cards_Status.[CARD STATUS])="REPLACED" OR (Cards_Status.[CARD STATUS])="EXPIRED")) AND Cards_Status.CLIENT IN (Select Cards_Status.CLIENT FROM Cards_Status GROUP BY Cards_Status.CLIENT HAVING Count(Cards_Status.CLIENT)=1);

ASKER

a gain sorry this one
SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE], Cards_Status.[CARD STATUS] FROM Cards_Status where Cards_Status.[CARD STATUS] in ( "NORMAL");
UNION SELECT Cards_Status.CLIENT,Cards_Status.CARD,Cards_Status.[EXP DATE],Cards_Status.[CARD STATUS] FROM Cards_Status
WHERE ((( Cards_Status.[CARD STATUS])="CANCELLED" Or (Cards_Status.[CARD STATUS])="STOP" OR (Cards_Status.[CARD STATUS])="REPLACED" OR (Cards_Status.[CARD STATUS])="EXPIRED")) AND Cards_Status.CLIENT IN (Select Cards_Status.CLIENT FROM Cards_Status GROUP BY Cards_Status.CLIENT HAVING Count(Cards_Status.CLIENT)=1);

I repeat, I am having a difficult time trying to figure out what you want. When you opened the question, for a customer with mutiple cards, you said you only wanted to see the normal status when the cancelled status on another card was further in the future. Then you added another status 'Replaced'. Please give me a sample of 10-20 records and detail which of the records you want the query to return. Ensure the sample includes a Client with the most complex situation.

ASKER

CLIENT      CARD        EXP DATE CARD STATUS
200002      5111111146      30-Apr-07      Replaced
200002      4111111136      29-Feb-08      NORMAL
200017      5111111178      31-May-07      NORMAL
200032      5111111149      30-Nov-03      EXPIRED
200032      4111111165      31-Oct-03      EXPIRED
200034      5111111123      31-Oct-03      EXPIRED
200042      4111111107      30-Jun-07      CANCELLED
200042      4111111199      30-Jun-04      EXPIRED
200050      5111111112      30-Jun-07      REPLACED
200050      4111111120      30-Jun-07      STOP
200050      5111111116      30-Jun-08      REPLACED
200050      5111111124      30-Jun-07      NORMAL
200050      5111111128      30-Jun-07      CANCELLED
what i want the last status for the A/C number if it was Normal show the normal even if the expiry date is less other than that show me the max expired date for the A/C

ASKER

Remember there is some status with the same expiry date

<and detail which of the records you want the query to return.< I don't know how to make it any clearer. Which of those 13 records do you want to see.

ASKER

200002 4111111136 29-Feb-08 NORMAL
200017 5111111178 31-May-07 NORMAL
200032 4111111165 31-Oct-03 EXPIRED
200034 5111111123 31-Oct-03 EXPIRED
200042 4111111107 30-Jun-07 CANCELLED
200050 5111111124 30-Jun-07 NORMAL

The longer I look at this problem the more I am convinced that we cannot get to the answer you want with the info you provided. Is there a field named StatusChgDate for example? Additionally, for client 200032 why do you want to know the earliest date that one of his two cards expired (both in 2003) even though his other card was still active unitl it also expired a month later?

ASKER

this an example sir see there another tbl i have with accounts that i want to match between it and between this tbl (the one above) so what i need to know what is the last status for these accounts and if the status is normal show it to othere that that show the last status

So I repeat, why then do you want to see the first rather than the last ExpDate for Client 200032?

ASKER

sorry this posrt by mistake show me the last one
200032 5111111149 30-Nov-03 EXPIRED

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

that what i call heterogeneous job thanks alot

Markus: Nicely done! My hat's off to you.

Glad to help and thank you both for the compliments!
(^v°)