June 30, 2017 at 1:19 pm
Take a look at the code. I am interested in counting the distinct codes that a member has within a certain range.
Right now , with the data that is in the table it gives a count of 1 more than the correct count which is 3.
( Now I can always subtract 1 from the cnt and take it as the final answer, BUT No ! That is not going to work out when ABC has rows in the table where all the rows have the code within the range )
( If you don't see my point here ... Run Listing 2 and you will see what I mean ) .
QUESTION:
So what I need is for you to modify the SELECT statement so that it always gives me a 3 for ABC ( Sometimes ABC can have ROWS where the code is out of range )
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0103', 'ABC'
UNION
Select 'D0104', 'ABC'
UNION
Select 'D0105', 'ABC'
Select
MEMBER,
COUNT
(
DISTINCT
(
CASE WHEN CODE BETWEEN 'D0100' AND 'D0103' THEN
CODE
ELSE
'00000'
END
)
)
FROM #t
GROUP BY MEMBER
LISTING 2 ( below )
This time I get the correct count ( But that is because ABC had all codes within range
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0103', 'ABC'
Select
MEMBER,
COUNT
(
DISTINCT
(
CASE WHEN CODE BETWEEN 'D0100' AND 'D0103' THEN
CODE
ELSE
'00000'
END
)
)
FROM #t
GROUP BY MEMBER
June 30, 2017 at 1:26 pm
One solution might be for me to add a where clause that says
WHERE CODE BETWEEN 'D0100' AND 'D0103'
That wont work for me. Why ?
I plan to add more COUNT statements like the listing below
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0102', 'ABC'
UNION
Select 'D0103', 'ABC'
UNION
Select 'D0104', 'ABC'
UNION
Select 'D0105', 'ABC'
Select
MEMBER,
COUNT
(
DISTINCT
(
CASE WHEN CODE BETWEEN 'D0100' AND 'D0103' THEN
CODE
ELSE
'00000'
END
)
) as CNT1
,
COUNT
(
DISTINCT
(
CASE WHEN CODE BETWEEN 'D0104' AND 'D0105' THEN
CODE
ELSE
'00000'
END
)
)as CNT2
FROM #t
GROUP BY MEMBER
June 30, 2017 at 1:42 pm
IGNORE THE ABOVE POST-- I was wrong
Help me here please ... I need to count the distinct codes between a range. So the output I am expecting is 2 .. Please modify the code...
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0103', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0105', 'ABC'
Select
MEMBER,
SUM
(
CASE WHEN CODE BETWEEN 'D0104' AND 'D0105' THEN
1
ELSE
0
END
)as SUMCNT1
FROM #t
GROUP BY MEMBER
June 30, 2017 at 1:44 pm
mw112009 - Friday, June 30, 2017 1:42 PMIGNORE THE ABOVE POST-- I was wrong
Help me here please ... I need to count the distinct codes between a range. So the output I am expecting is 2 .. Please modify the code...
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0103', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0105', 'ABC'
Select
MEMBER,
SUM
(
CASE WHEN CODE BETWEEN 'D0104' AND 'D0105' THEN
1
ELSE
0
END
)as SUMCNT1
FROM #t
GROUP BY MEMBER
There are 3 rows for D01014 and 1 for D0105. So I need is a distinct count within the range .. So the final answer should be 2
June 30, 2017 at 2:00 pm
I FOUND THE ANSWER.....
NO NEED TO REPLY PLEASE... The following works
So I get my count of 3
If( object_id('tempdb..#t') is not null )DROP TABLE #t;
Select 'D0100'as CODE, 'ABC' as MEMBER INTO #t
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0102', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0103', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0104', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0105', 'ABC'
INSERT INTO #t ( CODE, MEMBER )
Select 'D0106', 'ABC'
Select
MEMBER,
COUNT
(
DISTINCT
(
CASE WHEN CODE BETWEEN 'D0104' AND 'D0106' THEN
CODE
ELSE
NULL
END
)
) as cn
FROM #t
GROUP BY MEMBER
Viewing 5 posts - 1 through 4 (of 4 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