Search code examples
cognos-10cognos-bi

How do I get the value of the oldest date from a transaction table using a data item in Cognos 10 Report Studio?

How do I apply this query in Cognos Report Studio? Im thinking of creating a Data Item. Both queries are just the same, created in two different approach.

SELECT [Transaction Date], Amount
FROM DW.AmountTable
WHERE [Transaction Date] IN (SELECT Min([Transaction Date]) FROM DW.AmountTable)

SELECT A.[Transaction Date], A.[GWP Amt] [Amount] 
FROM DW.AmountTable A
INNER JOIN (SELECT min(transaction date) MTD, [Policy Number]) 
            FROM dw.amountTable 
            GROUP BY [Policy Number]) B
 on B.MTD=A.[Transaction Date]
and A.[Policy Number] = B.[Policy Number]
where A.[Policy Number] = '7030500'

Should I create a separate Data Item for minimum(Transaction Date)? Should I also create a new Data Item for the Amount that contains the Data Item for minimum(Transaction Date)?

enter image description here

Solution

  • The two queries you list do not do the same thing.

    The first will return the earliest date for the entire table and only show rows that match that date.

    The second query will fetch the earliest date for each policy number, join the main table on that date and then filter the results to only show policy number '7030500'.

    To replicate the first query use this filter:

    [Transaction Date] = minimum([Transaction Date] for report)

    To replicate the second query use this filter:

    [Transaction Date] = minimum([Transaction Date] for [Policy Number])
AND
[Policy Number] = '7030500'