Pevious SQL statement for Reporting Services:

I need to write a SQL statement or IIF statement to get results that I need in Reporting Services. Below is what I need and I am unsure how to create the statement for my results.

I have a lot number which is associated with a PB Number and an Expected Start and Expected End Date that is running in production. The lot number is a parameter that the end user will key in the report. I need the report to display the previous lot number which ran on the same PB number. I think that I would need to use the Expected State Date and the Expected End date for that lot's running time in order to get the previous lot number. How would I write the SQL statement? ANY SUGGESTIONS?

Thanks, Ronda

So how do you define "previous lot number". Is it a lot number that has an earlier start date?

If that is the case I would try something like this

select top 1 LotNumber from table_name t1 where LotNumber != @.LotNum AND PbNumber in (select PbNumber from table_name t2 where LotNumber = @.LotNum) order by StartDate descending

I'm not sure about descending on the end. It might have to be ascending. I forgot how Dates are ordered.

sql