I am using the following query that was suggested to me by a fellow from stackoverflow. The following query is using the dates in order to bring the current year (>2016,<2017) and the previous year (>2015, <2016). I want to use this in a Birt report - and particularly prompt the user to select the date (by default the current date will be selected) and automatically the query will subtract a year in order to calculate the current year's results and the previous year's results. Here's the code given to me
SELECT grade,
COUNT( DISTINCT CASE WHEN DATE '2015-01-01' >= date_column
AND date_column < DATE '2016-01-01'
THEN customer_id END
) AS number_of_unique_customers_in_2015,
COUNT( DISTINCT CASE WHEN DATE '2016-01-01' >= date_column
AND date_column < DATE '2017-01-01'
THEN customer_id END
) AS number_of_unique_customers_in_2016
FROM Customers
WHERE Date_Column >= DATE '2015-01-01'
AND Date_Column < DATE '2017-01-01'
GROUP BY grade;
Can anyone suggest how to do this in the birt report?
Have a look at my answer here to see how to add parameters to a SQL query in Birt. You should add your Report Parameter to input the date from the user first.
To substract a year from the input parameter you should use DATEADD with a negative value for one year inside your SQL query where you add the parameter.
your query should look like this for the Input-Parameter date 2016-01-01:
SELECT grade,
COUNT( DISTINCT CASE WHEN DATEADD(year, -1, ?) >= date_column
AND date_column < ?
THEN customer_id END
) AS number_of_unique_customers_in_last_year,
COUNT( DISTINCT CASE WHEN ? >= date_column
AND date_column < DATEADD(year, 1, ?)
THEN customer_id END
) AS number_of_unique_customers_in_current_year
FROM Customers
WHERE Date_Column >= DATEADD(year, -1, ?)
AND Date_Column < DATEADD(year, 1, ?)
GROUP BY grade;