July 30, 2017 at 3:27 am
Hi guys,
I have a little complicated issue,
I want to display all the members in the Allmembers table, and their card number.
However, when I have a member that have two or more cards ( one is active, the rest are inactive) i want it to display only the row of the member with the active card.
I mean, if the member is displayed only one time with an inactive card i want him to be displayed, but when he is displayed twice or more, i want only the row with the active card
Any suggestions?
Thanks a lot
Here is my query so far without the distinction :
Select A.MemberId , C.CardNumber , CS.Status
From AllMembers A INNER JOIN Cards C ON A.MemberId=C.IDMember INNER JOIN CardStatus CS ON C.CardStatus=CS.Id
July 30, 2017 at 3:39 am
JohnDoe564 - Sunday, July 30, 2017 3:27 AMHi guys,I have a little complicated issue,
I want to display all the members in the Allmembers table, and their card number.
However, when I have a member that have two or more cards ( one is active, the rest are inactive) i want it to display only the row of the member with the active card.
I mean, if the member is displayed only one time with an inactive card i want him to be displayed, but when he is displayed twice or more, i want only the row with the active cardAny suggestions?
Thanks a lotHere is my query so far without the distinction :
Select A.MemberId , C.CardNumber , CS.Status
From AllMembers A INNER JOIN Cards C ON A.MemberId=C.IDMember INNER JOIN CardStatus CS ON C.CardStatus=CS.Id
can you be absolutely certain that a member will only ever have one active card?
what about if a member has more than one card but they are all inactive?
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 30, 2017 at 3:46 am
JohnDoe564 - Sunday, July 30, 2017 3:27 AM
can you be absolutely certain that a member will only ever have on active card?
what about if a member has more than one card but they are all inactive?
Hi, Livingston
Thanks for your reply
Is can happen. In this case I would like to display this member one time only with one of the inactive cards (doesn't matter which one of them)
July 30, 2017 at 4:15 am
maybe....
CREATE TABLE #somedata(
ID INT
,CN INT
,Cstatus VARCHAR(8)
);
INSERT INTO #somedata(ID,CN,Cstatus) VALUES
(1,1001,'active'),(2,1002,'inactive'),(3,1003,'active'),(3,1004,'inactive')
,(4,1005,'active'),(4,1006,'active'),(5,1007,'inactive'),(5,1008,'inactive');
WITH statuses as
( SELECT ID,
CN,
Cstatus,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY CASE WHEN cstatus = 'active' THEN 1 ELSE 2 END, CN) rn
FROM #somedata
)
SELECT * FROM statuses
WHERE rn = 1;
DROP TABLE #somedata
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 31, 2017 at 6:40 am
Since John Doe wants active and inactive cards, I've built on J Livingston SQL's reply:
CREATE TABLE #somedata(
ID INT
,CN INT
,Cstatus VARCHAR(8)
);
INSERT INTO #somedata(ID,CN,Cstatus) VALUES
(1,1001,'active'),(2,1002,'inactive'),(3,1003,'active'),(3,1004,'inactive')
,(4,1005,'active'),(4,1006,'active'),(5,1007,'inactive'),(5,1008,'inactive')
,(6,1010,'inactive'),(6,1011,'active');
WITH cte as (
select DISTINCT ID
from #somedata
)
SELECT di.ID,
(SELECT TOP 1 sd.CN FROM #somedata sd
WHERE sd.ID= di.ID
order by Cstatus,CN) AS cardno
from cte di
;
DROP TABLE #somedata
[/code]
July 31, 2017 at 8:22 am
alicesql - Monday, July 31, 2017 6:40 AMSince John Doe wants active and inactive cards, I've built on J Livingston SQL's reply:
CREATE TABLE #somedata(
ID INT
,CN INT
,Cstatus VARCHAR(8)
);
INSERT INTO #somedata(ID,CN,Cstatus) VALUES
(1,1001,'active'),(2,1002,'inactive'),(3,1003,'active'),(3,1004,'inactive')
,(4,1005,'active'),(4,1006,'active'),(5,1007,'inactive'),(5,1008,'inactive')
,(6,1010,'inactive'),(6,1011,'active');
WITH cte as (
select DISTINCT ID
from #somedata
)
SELECT di.ID,
(SELECT TOP 1 sd.CN FROM #somedata sd
WHERE sd.ID= di.ID
order by Cstatus,CN) AS cardno
from cte di
;
DROP TABLE #somedata
[/code]
J. Livingston's query does produce both active and inactive records and it's much more efficient, because it only requires one table read/sort vs two table reads/sorts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 31, 2017 at 8:31 am
Apologies, mea culpa
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy