Query Help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

jughead1111
Starting Member

14 Posts

Posted - 2007-05-17 : 16:34:38

I have a query that I need help with.

PART_ID
WANT_DATE
BASE_ID

PART_ID WANT_DATE BASE_ID
R838336LX 4/20/2007 12:00:00 AM W85221
R838391J 4/25/2007 12:00:00 AM W85292
R838402T 7/12/2007 12:00:00 AM W85554
R838402T 5/25/2007 12:00:00 AM W85553
R839272LX 4/24/2007 12:00:00 AM W83985

I need to pull the BASE_ID and PART_ID with the lowest WANT_DATE. As you can see R838402T has to BASE_ID's. How would I pull the Base_ID with the first WANT_DATE?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 18:47:26

[code]
select *
from tbl
where WANT_DATE = (select min(WANT_DATE) from tbl)
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 23:05:39

select *
from tbl
where WANT_DATE = (select min(WANT_DATE) from tbl where base_id=T.base_id)

Madhivanan

Failing to plan is Planning to fail

jughead1111
Starting Member

14 Posts

Posted - 2007-05-18 : 09:20:04

That is how I have been trying it but it does not work.

This is athe actual SQL.

SELECT TOP 100 PERCENT dbo.vwJCTSelectExpeditedLines.CUST_ORDER_ID, dbo.vwJCTSelectExpeditedLines.LINE_NO,
dbo.vwJCTSelectExpeditedLines.PART_ID, dbo.WORK_ORDER.DESIRED_WANT_DATE AS WANT_DATE, dbo.WORK_ORDER.BASE_ID,
dbo.WORK_ORDER.LOT_ID, dbo.WORK_ORDER.SPLIT_ID, dbo.WORK_ORDER.SUB_ID
FROM dbo.vwJCTSelectExpeditedLines INNER JOIN
dbo.WORK_ORDER ON dbo.vwJCTSelectExpeditedLines.PART_ID = dbo.WORK_ORDER.PART_ID
WHERE (dbo.WORK_ORDER.STATUS = 'R') AND (dbo.WORK_ORDER.DESIRED_WANT_DATE =
(SELECT MIN(DESIRED_WANT_DATE) AS Expr1
FROM dbo.WORK_ORDER AS WORK_ORDER_2
WHERE (BASE_ID = dbo.WORK_ORDER.BASE_ID)))
ORDER BY dbo.vwJCTSelectExpeditedLines.CUST_ORDER_ID, dbo.vwJCTSelectExpeditedLines.LINE_NO

Partial results.

Order Line Part_ID Want_date Base_ID Lot Split Sub
72410 1 R861042T 6/8/2007 12:00:00 AM W85479 1 0 0
72410 1 R861042T 2/14/2007 12:00:00 AM W84331 1 0 0
72598 1 R835593T 3/8/2007 12:00:00 AM W84678 1 0 0
73130 1 R835892T 2/5/2007 12:00:00 AM W82404 1 0 0
73626 4 G837393 3/20/2007 12:00:00 AM W86281 1 0 0

What I need from the output is one record for each part_ID where the the want_Date is lowest.

vwJCTSelectExpeditedLines is a view. In the Work_Order table there can be more than one work order for each part_id.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources