Hi
I have a date based question can anyone please help me:
I have simple list report with customer#, customer name , date/time updated columns.
I have to create one more column that is Previous updated date/time.
not sure how to get that previous updated date/time.
Ex:
customer# customer name Date/Time updated Previous date/time updated
1111 john 2016-07-01 02:30:00.000000 2016-06-30 22:30:00.000000
1234 steve 2016-07-01 03:09:00.000000 2016-07-01 02:30:00.000000
in the above example I need to create previous date/time updated column. if any one has idea please share with me
Thanks,
What exactly do you mean by not sure out to get that column? Is it a field in your database table that you don't know how to expose in your reporting model?
Hi thank you for your reply.
actually I have only one date/time column in my table. based on that I have to get previous date/time
nurses will check the patients every 2 hours 3 hours etc..
when I run the report with date between startdate and enddate I will get the records in my report with columns:
PNO, PNAME, NNAME, DATE/TIME Previous Date/Time
1111 John lisa 7/1/16 2:30 7/1/16 12:30
1111 John amy 7/1/16 3:09 7/1/16 2:30
1111 John lisa 7/1/16 4:30 7/1/16 3:09
1111 John lisa 7/1/16 6:30 7/1/16 4:30
in above example Previous Date/Time should be calculated column (which I do not have in my table) that shows for every record previously checked time not sure how to get that .
Thanks,
Hi,
The column either needs to be available in your table or you need to have a formula for calculating the column. Once you provide us with a formula, we can give you the syntax.
Hi here is the sql for PREVUPDATE/TIME
SELECT PAT#, PNAME, USNAME, RECDT,
(SELECT TRN2.RECDT FROM GOPRM TRN2
WHERE TRN2.PAT# = TEMP.PAT#
AND TRN2.RECDT < TEMP.RECDT
ORDER BY TRN2.RECDT DESC
FETCH FIRST 1 ROW ONLY) AS PREVUPDATE/TIME
Ugh... why do people insist on using SQL for report queries. This is not how Cognos should be used.
Based on what I see here you are going to need a SQL-based solution. The 'best' way to do it may vary with which database you are using.