How to Get Previos updated Date/Time based on the Date/Time column

[cognos74]

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,

[AnalyticsWithJay]

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?

[cognos74]

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,   

[AnalyticsWithJay]

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.

[cognos74]

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

[bdbits]

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.