count for employee and dependent shows incorrect

[arulsettu]

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

[arulsettu]

yes i commented all other tables and distinct employee ID gives this result 173714

[rubenmarin]

So you'll never get the 192.807 distinct employee IDs, can you paste the query done in DB?

[arulsettu]

here

[arulsettu]

i just run this query in DB

select count (distinct ME_EMP_ID) from T_MD_EMPLOYEE

i got this 181533

[arulsettu]

any idea guys?

[rubenmarin]

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.

[rubenmarin]

I forget the "GROUP BY ME_AGE, ME_EMPLOYEE_YN"

[arulsettu]

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

[rubenmarin]

Sorry but doesn't tells me anything, my bad.  The group above 60 with ME_EMPLOYEE_YN='Y' gives 3002

[arulsettu]

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