Notifications
Clear all
Topic starter
I am trying to write a report that will show the last two obs values side by side. Can anyone help me with how to set that up. Thanks!
Replies through here or my direct email are great. [email protected]
Posted : April 20, 2016 8:14 am
Just thinking....
Group by person.
Person will link to LastObs table (and Obs) , to get the last data point (date and element). Save to a variable.
Also have Person link to Obs (or RptObs) to read thru all.
If two dates match, then skip. Else, write to combined variable [call it CombData] in format "YYYYMMDD_data".
At group level, you can repeat your saved items from LastObs for date and data. But take the maximum of CombData and place at group level.
John Doe 1/1/2016 18.9 20150704_17.6
Then, break apart the CombData field to get the date and value formatted nice (and remove the maximum entry.
John Doe 1/1/2016 18.9 7/4/2015 17.6
Posted : April 20, 2016 9:03 am
You can create a view, in the view, create two columns, then you can do something like this:
Column One:
SELECT TOP 1 OBSVALUE
FROM OBS
WHERE HDID = 1111 AND PID = 12324352345234523453
ORDER BY OBSDATE DESC
Column Two:
SELECT TOP 1 OBSVALUE
FROM (SELECT TOP 2 OBSVALUE, OBSDATE, HDID
FROM OBS
WHERE PID = 1681213456006610 AND HDID = -1 AND (SELECT COUNT(*) FROM OBS OBS2 WHERE PID = OBS.PID AND HDID = OBS.HDID) > 1
ORDER BY OBSDATE DESC) OBSColumn2
ORDER BY OBSDATE ASC
Just change the PID and HDID as needed to meet your requirements
Posted : April 22, 2016 4:26 am
You probably also want to only include signed obs by adding a "change in (2,6) and xid = 100000000000000000000000000000000000"
Posted : April 22, 2016 8:29 am
