cature date a column appear

SELECT Item,
            PO,
            Status,
            (SELECT MIN(Date) FROM tblItemReport Internal WHERE Internal.Item = External.Item) AS Date_First_Appear_On_Report
FROM   tblItemReport External

Select allOpen in new window

If you give some schema and sample data we may be able to help further.

Giannis

Use of the min aggregate requires a group by clause -
select item, po, status, min(date) as date_first_appear_on_report
from yourtable
group by item, po, status

Some sample data and your expected results would be helpful as Giannis suggests.

Hi, it doesn'tneed a group by as it is a subquery and the min is the only output of it. You need a group by when you have a column which you don' t agregate on.

Giannis

jyparask,
I apologize. I misread your comment and didn't see it as a sub-select. I'd still like to see some sample data and expected results, though. :-)

On that we agree. :)

>> a lot of data
if there is a lot of data; a correlated subquery within the selection list can be slow/expensive, an alternative is to place a subquery within the FROM such as this

SELECT
   Item
 , PO
 , STATUS
 , Date_First_Appear_On_Report
FROM tblItemReport EXTERNAL
INNER JOIN (
            SELECT
                Item
             ,  MIN(DATE) AS Date_First_Appear_On_Report
            GROUP BY Item
            FROM tblItemReport
            ) Internal
                ON EXTERNAL.Item = Internal.Item

Select allOpen in new window

but, as mentioned earlier, more information would help

Thank you folks. I don't know if I can provide more data.  I think the min date will work.  One main issue with this is  the Date is not any where in the table.  I simply want to capture the date from a sql function.  All the table has ItemX, PO , Status.  For example I run a query to collect those 3 piece of information to render to SSRS.  If a particular item appear on the report 3 days ago.  The would look like this:

Item       PO         Status         DateFirstAppear
x             123        Urgent         3/31/13

can this be done?

Can you post the code for the sql function that retrieves the date?

>>I think the min date will work.<<
min(date) can't work if there is no date. Perhaps something like "min(sql_function(parameter)) as Date_First_Appear_On_Report" might though, which is why I would like to see the function code.

can this be done?

NO

>> One main issue with this is  the Date is not any where in the table
>> I simply want to capture the date (and put it where?)
>> If a particular item appear on the report 3 days ago (will a stored procedure be  used when the report is run?)

You cannot "recall" when an item appeared in a report - unless you store that information

Will you be storing "the date"?

Paul,

 (and put it where?)
 Put the data on a report.
Yes a store procedure will be used.

(put it where?) =  where will you store the date?
if you are using a stored procedure,
you will know if an item is about to appear on the report...
and you can test also if it is has already appeared before, if you have a table for this...
if an item has not appeared before...
insert it into that table...

next time

you will know if an item is about to appear on the report...
and you can test also if it is has already appeared before, if you have a table for this...
if an item has not appeared before...
insert it into that table...

etc.

am I missing something? If you don't store the required information somewhere you simply will not be able to compute when an item first appeared on the report

I ended cancelling this report as the data was only manipulated via a VIEW. The date the item appear on the report is not stored field in a table.

thanks.