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; } } }

Date Adds

TheShadraq

I have a query that runs and pulls a comprehensive look at a massive group of rows. It is currently pulling 4300 rows, and that number grows each quarter. 
 
The report is not designed to show all 4300 rows, but rather a breakdown of the data. My question is 2 parts: 
 
1. How do I add dates together? 
 
I have 2 code snippets that work great: 
<pre class='_prettyXprint _lang-auto _linenums:0'>Total.ave(dataSetRow["origdate"],
BirtComp.lessThan(dataSetRow["reportdate"],"2012-04-01")
*BirtComp.equalTo(dataSetRow["docstyle"],"NPR")
*BirtComp.greaterOrEqual(dataSetRow["reportdate"],"2012-01-01")
*BirtComp.equalTo(dataSetRow["orig"],"ORIG"))

Total.ave(dataSetRow["compdate"],
BirtComp.lessThan(dataSetRow["reportdate"],"2012-04-01")
*BirtComp.equalTo(dataSetRow["docstyle"],"NPR")
*BirtComp.greaterOrEqual(dataSetRow["reportdate"],"2012-01-01")
*BirtComp.equalTo(dataSetRow["comp"],"COMP"))
</pre>
The idea is to grab an average date within a range based on certain criteria. This code actually works. Separately. My question is this: If the first returns 2/1/2012, and the second returns 2/15/12 - how can I subtract one from the other and get "14 days"...all in the same data field? 
2. Is there a better place to put my code than where I am putting it? (optimization) 
 
In my report, I have added different data expressions from the Palette. There are something like 40 of these and inside them, the expressions have various versions of the code above. It makes the report run heavy. The query takes 2-4 seconds to run, but the calculations take quite a while. 
 
Is there a better place to put this code than where I am currently doing it? 
 
I hope that makes sense, thanks for your time. 
 
-Shadraq

Find more posts tagged with

Comments

kclark

For your first question you can use BirtDateTime.diffDay() to get the number of days 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
date1 = 2/1/2012
date2 = 2/15/2012
diffDays = BirtDateTime.diffDay( date1, date2 )
</pre>
 
That should return 14 
 
You can quickly test this by adding a dynamic text object to your report and putting 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
BirtDateTime.diffDay( "2009-01-01", "2009-04-15" )
</pre>
 
In the expression builder.

TheShadraq

Thanks for the response. Yes I have used diffDay and it works as you indicated. My question more of how to combine the code I put in my initial post, together with what you suggested, into one expression that gives me "14".

TheShadraq

Ok, I did some more work and, coupled with your advice, I've worked through my first issue: 1. How do I add dates together? 
 
In case anyone runs into this, the below code will average a group of dates based on specific parameters, find the difference between those two date sets, then round it to 2 decimal spots. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>BirtMath.round(
diffDays = BirtDateTime.diffDay(
(Total.ave(dataSetRow["origdate"],
BirtComp.lessThan(dataSetRow["reportdate"],"2012-04-01")
*BirtComp.equalTo(dataSetRow["docstyle"],"NPR")
*BirtComp.greaterOrEqual(dataSetRow["reportdate"],"2012-01-01")
*BirtComp.equalTo(dataSetRow["orig"],"ORIG"))),

(Total.ave(dataSetRow["compdate"],
BirtComp.lessThan(dataSetRow["reportdate"],"2012-04-01")
*BirtComp.equalTo(dataSetRow["docstyle"],"NPR")
*BirtComp.greaterOrEqual(dataSetRow["reportdate"],"2012-01-01")
*BirtComp.equalTo(dataSetRow["comp"],"COMP")))
),2)</pre>
 
Anyone have an idea on my other issue? Having this much custom code, running at about 40 spots in the report, causes this to really lag. 2. Is there a better place to put my code than where I am putting it? (optimization)

Tubal

It's always faster to do the calculations in your query.

Where is your data coming from? Can you do the calculations before BIRT gets it?

TheShadraq

This is my query. I could probably rework it. With the calcs coming in after SQL runs, the report is massively sluggish. I'll have to give messing with the query again a shot. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select count(DISTINCT workorder.wonum) as total, workorder.reportdate as reportdate,
(select min(a.status) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'ORIG') as 'orig',
(select min(a.changedate) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'ORIG') as 'origdate',
(select min(a.status) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'RTS') as 'rts',
(select min(a.changedate) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'RTS') as 'rtsdate',
(select min(a.status) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'RELEASED') as 'released',
(select min(a.changedate) from wostatus a where a.wonum = workorder.wonum AND a.siteid = workorder.siteid and a.status = 'RELEASED') as 'releaseddate',
case when (select min(a.status) from wostatus a where a.wonum = workorder.wonum aND a.siteid = workorder.siteid and a.status in('comp','comp-rev','COMP-WCC','Completed','CLOSE')) is not null then 'COMP' else null end as 'comp',
workorder.actfinish as 'compdate', workorder.docstyle
from workorder
INNER JOIN wostatus on (workorder.wonum = wostatus.wonum and workorder.siteid = wostatus.siteid)
WHERE workorder.siteid in ('MAINT','WATER')
AND workorder.pmnum is null
AND workorder.status in ('close','comp','comp-rev','COMP-WCC','Completed')
AND workorder.istask = '0'
AND ((wostatus.status = 'RTS' AND workorder.DOCSTYLE = 'PLAN') or (workorder.DOCSTYLE = 'NPR'))
group by workorder.wonum,workorder.reportdate,workorder.siteid,workorder.actfinish,workorder.docstyle</pre>