how to compare multiple date ranges - find gaps

[lana80]

 Hi, I need help.

I need to compare dates  from invoices with date range wich is filter, and to print if range is covered, partialy coverd  and etc. with invoices by code...

Example: 

Table Invoices

ID	Code	StartDate	EndDate
1	111	1.5.2012.	31.5.2012.
2	111	1.7.2012.	20.7.2012.
3	111	25.7.2012.	31.7.2012.
4	222	1.4.2012.	15.4.2012
5	222	18.4.2012.	30.4.2012.

  Filter:  1.5.2012. - 5.6.2012.              Result that I need to get is: for code 111 partialy coverd

                                                                                                                        code 222 - invoice missing

 

  Filter:  1.5.2012. - 31.5.2012.              Result that I need to get is: for code 111 fully coverd

                                                                                                                        code 222 -  invoice missing

 

  Filter:  1.6.2012. - 30.6.2012.              Result that I need to get is: for code 111 -  invoice missing

                                                                                                                        code 222 -  invoice missing

 

etc...

 

Please help!

 EDIT:

Maybe, better question is how to find gaps in dates for site code, comparing with filer?

 

Post Edited by lana80 at 06/04/2012 12:09

[lana80]

anybody?

any idea?

please, it is urgent  :(

 

[brooneyx1]

I think you need to do this in the query where clause.

Where invoice.startdate between $P{StartDate} and $P{EndDate}

or // Next where cluase

[lana80]

Sure is in where clause, but it is much more complex...

Thanks anyway