Sparql gets all months between two SPARQL dates

Question

I am working on a SPARQL query in TopBraid Composer to get the number of data quality rules in each month.

I have a start date and an end date, but I need to get all the months between the start date and the end date so that I can get the number of data quality rules that apply to this duration.

My current query and result: -

SELECT *    
WHERE    
{    
   ?Rules CDE:Data_Quality_Rule_added_on ?Date1.    
   ?Rules CDE:Data_Quality_Removed_On ?Date2

   BIND(month(?Date1) AS ?Month1)    
   BIND(month(?Date2) AS ?Month2)    
   BIND(smf:duration("mon",?Date1,?Date2) AS ?dur)    
}    
LIMIT 5

| [Rules]                  | Date1      | Date2      | Month1 | Month2 | dur    
| CDE:Data_Quality_Rule_13 | 2016-01-28 | 2016-09-15 | 01     | 09     | 8   
| CDE:Data_Quality_Rule_16 | 2016-02-29 | 2016-08-08 | 02     | 08     | 5   
| CDE:Data_Quality_Rule_18 | 2016-05-15 | 2016-10-31 | 05     | 10     | 6   
| CDE:Data_Quality_Rule_4  | 2016-03-28 | 2016-07-02 | 03     | 07     | 3   
| CDE:Data_Quality_Rule_5  | 2016-02-02 | 2016-06-06 | 02     | 06     | 4   

I can get the start month, end of the month and duration. But I would like to get all the months between the start month and the end month in SPARQL. The end result is to get a monthly count of the number of qulaity rules for the data based on the start and end dates as follows:

| Months   | Number Of Data Quality Rules |
|  1       | 2                        
|  2       | 4                            
|  3       | 6                            
|  4       | 3                             
|  5       | 3                            
|  6       | 4                           
|  7       | 4                            
|  8       | 4                            
|  9       | 5                             
|  10      | 3                             
|  11      | 2                             
|  12      | 5                            

Answer 1

-, :

@prefix : <urn:ex:>
@prefix xsd: <http://www.w3.org/2001/XMLSchema#>

:rule1 :begin "2011-01-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-06-10T14:45:13.815-05:00"^^xsd:dateTime .

:rule2 :begin "2011-04-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-10-10T14:45:13.815-05:00"^^xsd:dateTime .

:rule3 :begin "2011-06-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-11-10T14:45:13.815-05:00"^^xsd:dateTime .

, , . , , :

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?rule ?month {
  #-- Specify the possible values for ?month
  #-- in advance.  These are just the numbers
  #-- one through twelve.
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  #-- Get the begin and end dates of that
  #-- you're interested in.  The way you
  #-- do this depends on the structure of
  #-- your data, of course.
  ?rule :begin ?begin ; :end ?end .

  #-- Then take only the values of ?month
  #-- that are between the beginning month
  #-- and the ending month.
  filter ( month(?begin) <= ?month && ?month <= month(?end) )
}

------------------
| rule   | month |
==================
| :rule1 | 1     |
| :rule1 | 2     |
| :rule1 | 3     |
| :rule1 | 4     |
| :rule1 | 5     |
| :rule1 | 6     |
| :rule2 | 4     |
| :rule2 | 5     |
| :rule2 | 6     |
| :rule2 | 7     |
| :rule2 | 8     |
| :rule2 | 9     |
| :rule2 | 10    |
| :rule3 | 6     |
| :rule3 | 7     |
| :rule3 | 8     |
| :rule3 | 9     |
| :rule3 | 10    |
| :rule3 | 11    |
------------------

, :

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?month (count(distinct ?rule) as ?numRules) where {
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  ?rule :begin ?begin ; :end ?end .

  filter ( month(?begin) <= ?month && ?month <= month(?end) )
}
group by ?month

--------------------
| month | numRules |
====================
| 1     | 1        |
| 2     | 1        |
| 3     | 1        |
| 4     | 2        |
| 5     | 2        |
| 6     | 3        |
| 7     | 2        |
| 8     | 2        |
| 9     | 2        |
| 10    | 2        |
| 11    | 1        |
--------------------

12- , . , , :

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?month (count(distinct ?rule) as ?numRules) {
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  optional {
    ?rule :begin ?begin ; :end ?end .
    filter ( month(?begin) <= ?month && ?month <= month(?end) )
  }
}
group by ?month

--------------------
| month | numRules |
====================
| 1     | 1        |
| 2     | 1        |
| 3     | 1        |
| 4     | 2        |
| 5     | 2        |
| 6     | 3        |
| 7     | 2        |
| 8     | 2        |
| 9     | 2        |
| 10    | 2        |
| 11    | 1        |
| 12    | 0        |
--------------------