Selecting first instance of an event

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting first instance of an event

Author

Topic

stewpyd
Starting Member

2 Posts

Posted - 2008-08-07 : 05:28:11

Hi,

I want to select the part no(part_no), transaction date (curr_date), and in stock qty (cost_layer_qty) from table inv_tran where the product first goes into negative quantity.

Do I use a group by for curr_date, or how do I do this?

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-07 : 05:31:30

Post your table DDL, sample data and your expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 05:46:23

quote:
Originally posted by stewpyd

Hi,

I want to select the part no(part_no), transaction date (curr_date), and in stock qty (cost_layer_qty) from table inv_tran where the product first goes into negative quantity.

Do I use a group by for curr_date, or how do I do this?

Thanks in advance

SELECT t.part_no,t.curr_date,t.cost_layer_qty
FROM YourTable t
INNER JOIN (SELECT part_no,MIN(curr_date) as MinDate
            FROM YourTable
            WHERE cost_layer_qty<0
            GROUP BY part_no)t1
ON t1.part_no=t.part_no
AND t1.MinDate=t.curr_date
WHERE t.cost_layer_qty<0

stewpyd
Starting Member

2 Posts

Posted - 2008-08-07 : 21:19:36

Thanks, works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources