nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

sql query - last row before specific date

magic_bern

Dear all,
i am having an issue retrieving data for a report that will be used to analyse the the energy tariff used on some sites.
my data source has reading for a site and each sites has many properties. readings are not available for each properties at the same frequency for instance, property A might have 3 readings for Sept, 1 for august and none for July, whilst property B might have one reading per month and so on.
the report for a month let's say September needs to have on the same row:
-the last reading and reading date prior to September (this will be at different date for different properties)
-the last September reading and reading date
this is so that consumption and credit as well as tariff could be calculated on the next column.
i have managed to get both the information using 2 data sets (one for prevReading- taking the last reading of all reading less than 1/09 and a second one for curReading- taking the last reading for the period.
the first problem i ad is that later on i was unable to do some arithmetic between the two dataset and after i tried storing them separately in global variable i realized the rows for each of the data set were not always matching - i.e. the first rows were for different properties.
i think i need to get a query that could return those reading in the same dataset.
Any help will be much appreciated.
Reagards

Berny

Find more posts tagged with

Comments

mwilliams

So, if you're able to have both in two separate dataSets, what is wrong with the solution of grabbing both values and storing them in variables to be accessed where needed in your report?

magic_bern

the problem is being able to track them, when i order them by the accountID, i dont always get the same amount of rows returned for each of the dataset as some of the property may not have had any reading for a given period. i.e. the first dataset for preReading is likely to return all the property but the curReading dataset my only have few as the property may not have registered their reading for that month. i am not sure how to link the rows for each of the dataset.
I have attache a sample doc of how the report should look like. may be it will give a better idea of the report

Thanks

mcremer

Hi Benny, 
 
What your asking is a quite complex query. First of I want to point you to a <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1314-reusing-parameters-in-birt-data-set/'>DevShare</a> a college of mine wrote. It talks about for Parameters. But the WITH statement is much much stronger then that. 
 
You could in your case (if the tables have similar data properties etc.) create a SQL statement as this (this is an example I do not know your DB and the WITH statement works in almost any DB (bf you ask I tested it against ORACLE). 
 
You could end up somting like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH PARAMS AS (select ? para1
,? para2
,? para3
from dual)
,query1 AS(select <your colmns of data here>
from <table_names>
,params para
where column => para.para1
and column between para.para2 and para.para3)
,query2 AS(select <your colmns of data here should be simular to query1 names if you want to join them>
from <table_names>
,params para
where column => para.para1
and column between para.para2 and para.para3)
/* Main Select */
SELECT <your colmns of data here>
FROM query1 a
,query2 b
where <logical statement were you join the query1 and to (or left join) so taht you get your data>

</pre>

magic_bern

Thank You very Much MCremer,
this seemed to be exactly what i am trying to do but unfortunately the data source for these report is MS access and the with statement doesn't seem to work. i have been trying a union too and even that doesn't seem to work in BIRT
Thanks