Create table main
(name varchar,
member_type_id integer)
Following are the values in main table.
Name member_type_id
Sara 11
Jack 11
Jill 22
Mary 22
Anna 22
SELECT CASE
WHEN MEMBER_TYPE_ID = 11 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID)
END emp_count,
CASE
WHEN MEMBER_TYPE_ID = 99 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID)
END sp_count
FROM main
When I run the above query in sql server I get following result
emp_count
2
2
sp_count
3
3
3
However, when I run this query in crystal report, I get the result of only emp_count i.e 2. For sp_count nothing shows. I think the issue is with how crystal is reading the case statement. I don't know if this is the right place for me to ask this question. However, any help will be greatly appreciated. Thanks in advance.
You mentioned MEMBER_TYPE_ID = 99 for sp_count column in case statement. However in test data it is member_type_id=22.
Can you kindly confirm which is correct?