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