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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-14 : 16:15:00
|
Diana writes "I am creating a temp table which stores the warehouse id of warehouse required for a certain part number. Then I join it with a table containing ETA dates for that product. I only want to return the most current date for each warehouse and if there is not a record for a particular warehouse I want to return a NULL value. But if I use min on ETA date, then it does not return warehouse id's that do not contain an ETA record. And a left outer join does not return 1 row each.
Here is some code I have been playing with........
declare @cmd varchar(255),
@parm1 char(22),
@error int,
@stoday int,
@today datetime
select @today = getdate()
select @stoday = 1000* datepart(yy,@today) + datepart(dy,@today)
select a.WHSE_LOC_DESC,
'ETA_DATE' =
case
when p.REV_DUE_DATE >= @stoday then convert(char(10),dateadd(dy, (convert(int,
substring(convert(char(7), p.REV_DUE_DATE),5,3))-1), "Jan 1, " + substring(convert(char(7), p.REV_DUE_DATE),1,4)),101)
when p.REV_DUE_DATE <= @stoday then 'Past Due'
else NULL
end
from avail a left outer join PO_ETA p
on a.WHSE_NO = p.WAREHOUSE_NUMBER
where ETA_LINE_STATUS in ('O','P')
and p.IC_ITEM_NO = '407414'
and p.CORP_CODE = '20'
and PO_OPEN_QTY_SKUS > 0.00
" |
|
|
|
|
|
|
|
Use of min with outer join