Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Subtracting consecutive date rows in wostatus report
shamo
<p>I used this formula if {wostatus.wonum}= Next({wostatus.wonum}) then (Next({wostatus.changedate}) else currentdatetime in crystal report to obtain a column to get time differnces between status change period. How can I achieve same in birt?</p>
Find more posts tagged with
Comments
wwilliams
<p>Can you just explain what you are trying to accomplish?It looks like you may want the changedate difference between statuses say from APPR/INPRG to COMP?</p>
shamo
<p>Exactly</p>
wwilliams
<p>Are you using Oracle or SQL Server?</p>
shamo
<p>Oracle but both solutions will be appreciated</p>
wwilliams
<p>Well the easiest solution is to do it in SQL</p>
<p> </p>
<p>Something like</p>
<p> </p>
<p>select wonum, description, wdate, nvl(adate,sysdate) adate from workorder w</p>
<p>JOIN (select wonum, siteid, max(changedate) wdate from wostatus where status = 'WAPPR' group by wonum, siteid) wstatus</p>
<p>ws on w.wonum = ws.wonum and w.siteid = ws.sitie</p>
<p>LEFT JOIN (select wonum, siteid, max(changedate) adate from wostatus where status = 'APPR' group by wonum, siteid) astatus</p>
<p>as on w.wonum = as.wonum and w.siteid = as.siteid</p>
shamo
<p>what is adate and wdate?</p>
wwilliams
<p>in the example adate is the date approved, wdate the date it was wappr..just column names I made up</p>
shamo
<p>I've tried with your query but not working. it gives errors.</p>
wwilliams
<p>what is the error? I don't have access to Oracle right now, but the error should give you hint, is it an ambiguous error. I found a typo as well</p>
<p>try</p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">select w.wonum, w.description, wdate, nvl(adate,sysdate) adate from workorder w</span></p>
<p> </p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">also</span></p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">JOIN (select wonum, siteid, max(changedate) wdate from wostatus where status = 'WAPPR' group by wonum, siteid) wstatus</span></p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">ws on w.wonum = ws.wonum and w.siteid = <span style="color:#a52a2a;">ws.siteid</span></p>
shamo
<p>Error says missing key word. Maybe further explanation will help. what I want to do is to find the historical period from 'WAPPR' status to 'WMATL' and how long it stays in 'WMATL'. All fields are coming from the wostatus table with wo.workorder.status as 'WMATL'. This then gives me all the status changes to 'WMATL' and its between these statuses that I want to find the time it stayed in each period before it changed. Attached is the crystal report. Hope this helps. Statuschangedate field is from the formula above</p>
wwilliams
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Try</p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">select wonum, description, wdate, nvl(mdate,sysdate) adate from workorder w</p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">JOIN (select wonum, siteid, max(changedate) wdate from wostatus where status = 'WAPPR' group by wonum, siteid) wstatus</p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">ws on w.wonum = ws.wonum and w.siteid = ws.siteid</p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">LEFT JOIN (select wonum, siteid, max(changedate) mdate from wostatus where status = '<span>WMATL</span>' group by wonum, siteid) mstatus</p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> on w.wonum = m<span>status</span>.wonum and w.siteid = m<span>status</span><span>.siteid</span></p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p>
<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><span>I had the table alias as 'AS'... not smart..</span></p>
shamo
<p>This is the error I'm getting</p>
shamo
<p>I achieved it with this query</p>
