records getting lost when filtering group

[cardgunner]

I posted something similiar to this already, it was never solved. It is now affecting my job.

I have a report where when I use the filter property some of my data is getting cut out.

I filter equip_type = 10 and I got most of the 10s. I'm missing 6 records whose equip_type is 10.

If I write it into the SQL where equip_type=10 I get them all. I add the filter and all but 6. All 6 of follow each other. They are like record_ID 34, 35, 36, 37, 38, and etc. They are in the middle of the recordset and middle of the report.

I need to filter the group. I cannot use the where in the SQL cause it limits what I'm showing in the details.

Please HELP!!!!

 

[dgorka]

Could you post your code so we can see better what is going on?

 

[cardgunner]

What code are you looking for?

My SQL statement?


If so it's not in the statement. I get the correct results from the statement.

It's when I try to filter those results.

I've been banging my head for about 3 to 4 days trying everything to figure it out.

 

[cardgunner]

If it helps here is my SQL.
As some background info I learned SQL thru constant trial and error and forums. I started learing it a year ago. So it may not look great but it does work

select  	scfg200.t_ccfg,
	convert(varchar(3),scfg200.t_obtp)t_obtp,
	rext006.t_cffc,
CASE 	when rext310.t_stat = '4' THEN 'ON RENT' 
	when rext310.t_stat ='10'  THEN 'AVAIL' 
	when rext310.t_stat is null then 'AVAIL'
	when rext310.t_stat = '2' THEN 'QUOTED' 
	when rext310.t_stat = '11' THEN 'ON DEMO' 
	else 'OTHER' END AS t_stat, 
	scfg200.t_clot,
	rext310.t_blck,
	scfg200.t_modd,
	scfg200.t_cogr,
	sext205.t_tmtr,
	dext816.t_spec,
	scfg200.t_aloc, 
	rext401.t_rcno,
	scfg200.t_cser,
	scfg200.t_cmnf
from 	ttscfg200100 scfg200 left join
	terext310100 rext310 on rext310.t_clot=scfg200.t_clot left join
	terext006100 rext006 on rext006.t_clot=scfg200.t_clot left join
(select t_clot, t_spec
from 	ttdext816100 
where 	t_seqn=40
)	dext816 on dext816.t_clot=scfg200.t_clot left join
(select tmp.t_clot, ext401.t_rcno 
from 	
(select t_clot, max(t_arda)t_arda
from terext401100
group by t_clot
)tmp join
terext401100 ext401 on ext401.t_clot=tmp.t_clot and ext401.t_arda=tmp.t_arda
)	rext401 on rext401.t_clot=scfg200.t_clot left join
(select sum(ext205.t_mred) t_tmtr, 
	ext205.t_clot
from 	ttsext205100 as ext205 join 
(select max(t_date) as t_date,t_clot,t_mseq
from 		ttsext205100
group by 	t_mseq, t_clot
)	tmp on ext205.t_date=tmp.t_date and ext205.t_mseq=tmp.t_mseq and ext205.t_clot=tmp.t_clot
group by ext205.t_clot
)	sext205 on sext205.t_clot=scfg200.t_clot
where 	scfg200.t_stat in (80,10) and 
	scfg200.t_obtp<>15 and 
	isnull(rext310.t_stat ,0) not in ( '5','12') and 
	scfg200.t_cogr NOT IN ('AUT', 'SHP', 'ME015', 'ME032', 'XX009', 'XX008', 'XX007', 'XX004', 'XX001', 'ME007', 'ME005', 'ME036') and 
	scfg200.t_truc_c <> 1 and 
	convert(decimal(9,2),(case when  scfg200.t_cdel='' then '0' else scfg200.t_cdel end))<700