Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i am trying to show the count of employee and dependent by age wise in barchart. but i am getting incorrect values. i checked the data model also.
my approval date format like this
so i did this in back end to get date
date(Floor(MP_APPR_DT)) as APPROVED_DATE
from this APPROVED_DATE created master calendar.
while checking in DB the values getting in QV is incorrect.
i dont know what cause the issue.
please check the attached app.
this the expected output for the age range
some one please suggest some solution. its really urgent.
Thanks
yes i commented all other tables and distinct employee ID gives this result 173714
So you'll never get the 192.807 distinct employee IDs, can you paste the query done in DB?
here
i just run this query in DB
select count (distinct ME_EMP_ID) from T_MD_EMPLOYEE
i got this 181533
any idea guys?
Not yet, can you try a simpler query like:
SELECT ME_AGE, ME_EMPLOYEE_YN, COUNT(DISTINCT ME_EMP_ID)
WHERE EXISTS (SELECT MP_BI_ID FROM T_MD_POLICY WHERE MP_BI_ID=ME_MP_BI_ID AND TRUNC(MP_APPR_DT) BETWEEN '01-JAN-2015' AND '10-SEP-2015')
Note: Changed the date to 9/10/2015 as it's the max APPROVAL_DATE in sample qvw.
I forget the "GROUP BY ME_AGE, ME_EMPLOYEE_YN"
i got this
0 | N | 2106 |
0 | Y | 36 |
1 | N | 2890 |
1 | Y | 23 |
2 | N | 3066 |
2 | Y | 5 |
3 | N | 3054 |
3 | Y | 11 |
4 | N | 2982 |
4 | Y | 14 |
5 | N | 2946 |
5 | Y | 7 |
6 | N | 2697 |
6 | Y | 2 |
7 | N | 2623 |
7 | Y | 8 |
8 | N | 2379 |
8 | Y | 7 |
9 | N | 2208 |
9 | Y | 2 |
10 | N | 2106 |
10 | Y | 2 |
11 | N | 2011 |
11 | Y | 2 |
12 | N | 1836 |
12 | Y | 2 |
13 | N | 1649 |
13 | Y | 1 |
14 | N | 1547 |
14 | Y | 2 |
15 | N | 1465 |
15 | Y | 1 |
16 | N | 1345 |
16 | Y | 7 |
17 | N | 1246 |
17 | Y | 13 |
18 | N | 929 |
18 | Y | 175 |
19 | N | 637 |
19 | Y | 709 |
20 | N | 571 |
20 | Y | 1504 |
21 | N | 481 |
21 | Y | 2393 |
22 | N | 434 |
22 | Y | 3625 |
23 | N | 484 |
23 | Y | 4769 |
24 | N | 534 |
24 | Y | 6385 |
25 | N | 662 |
25 | Y | 8064 |
26 | N | 806 |
26 | Y | 9675 |
27 | N | 961 |
27 | Y | 10565 |
28 | N | 1076 |
28 | Y | 11232 |
29 | N | 1076 |
29 | Y | 11335 |
30 | N | 1138 |
30 | Y | 11236 |
31 | N | 1261 |
31 | Y | 11187 |
32 | N | 1198 |
32 | Y | 10869 |
33 | N | 1176 |
33 | Y | 10376 |
34 | N | 1121 |
34 | Y | 10014 |
35 | N | 1145 |
35 | Y | 9110 |
36 | N | 1065 |
36 | Y | 8476 |
37 | N | 912 |
37 | Y | 7969 |
38 | N | 861 |
38 | Y | 7473 |
39 | N | 821 |
39 | Y | 6939 |
40 | N | 748 |
40 | Y | 6443 |
41 | N | 710 |
41 | Y | 5955 |
42 | N | 636 |
42 | Y | 5592 |
43 | N | 595 |
43 | Y | 5312 |
44 | N | 597 |
44 | Y | 5023 |
45 | N | 537 |
45 | Y | 4683 |
46 | N | 455 |
46 | Y | 4234 |
47 | N | 415 |
47 | Y | 3822 |
48 | N | 371 |
48 | Y | 3465 |
49 | N | 358 |
49 | Y | 3133 |
50 | N | 349 |
50 | Y | 2889 |
51 | N | 297 |
51 | Y | 2768 |
52 | N | 283 |
52 | Y | 2568 |
53 | N | 257 |
53 | Y | 2284 |
54 | N | 222 |
54 | Y | 2182 |
55 | N | 183 |
55 | Y | 2006 |
56 | N | 166 |
56 | Y | 1838 |
57 | N | 143 |
57 | Y | 1602 |
58 | N | 108 |
58 | Y | 1422 |
59 | N | 89 |
59 | Y | 1257 |
60 | N | 73 |
60 | Y | 1001 |
61 | N | 68 |
61 | Y | 820 |
62 | N | 58 |
62 | Y | 636 |
63 | N | 40 |
63 | Y | 440 |
64 | N | 34 |
64 | Y | 352 |
65 | N | 32 |
65 | Y | 220 |
66 | N | 18 |
66 | Y | 151 |
67 | N | 19 |
67 | Y | 124 |
68 | N | 12 |
68 | Y | 84 |
69 | N | 15 |
69 | Y | 65 |
70 | N | 9 |
70 | Y | 32 |
71 | N | 2 |
71 | Y | 28 |
72 | N | 10 |
72 | Y | 19 |
73 | N | 9 |
73 | Y | 31 |
Sorry but doesn't tells me anything, my bad. The group above 60 with ME_EMPLOYEE_YN='Y' gives 3002
one thing same emp id present in different age but the policy tans id is unique
empid,age,pol_id
035 | 30 | 63873 |
035 | 26 | 80587 |
035 | 34 | 57981 |
035 | 31 | 76244 |
035 | 24 | 69281 |
035 | 35 | 70361 |
035 | 25 | 72759 |
035 | 40 | 79043 |
035 | 36 | 80227 |
035 | 34 | 83714 |
is this make any sense