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