sql server mseq: Query Help -- sql server mseq

Query Help

brian
11/20/2003 8:55:21 AM

sql server mseq:

The following query contains a order header table and a
modification table that keeps track of all changes made
to the order header table. The modification table stores
the date and time a mofication was made. Also, the
modifcation table can have numerous records with the same
order number for the same date.

The following query returns the order numbers when they
were sent to the 'YARD' (which is 41)

Lets say Order Number 4345 was sent to the Yard 2 times
on 11-20-2003 11:00 and once at 11-20-03 14:00. I need
only 1 record to show- the one that occured later in the
day.

Can someone help me structure this? Do I need to use a
Stored Procedure? I have never written a Stored
Procedure!

Thanks

SELECT to_ordnum, mo_date, dbo.DDTORD.to_trak2id,
to_totdue, to_broknum
FROM DDTORD INNER JOIN DXMOD ON DDTORD.to_ordnum =
DXMOD.mo_recid ANDDDTORD.to_ordnum = DXMOD.mo_recid
WHERE (mo_table = 'ddtord') AND (mo_newval = 41)

Query Help

Jorge Lavado
11/21/2003 1:48:13 AM

Hi.
You can use Select MAX of the date.

SELECT to_ordnum, mo_date, dbo.DDTORD.to_trak2id,
to_totdue, to_broknum
FROM DDTORD INNER JOIN DXMOD
ON DDTORD.to_ordnum = DXMOD.mo_recid
ANDDDTORD.to_ordnum = DXMOD.mo_recid
WHERE (mo_table = 'ddtord') AND (mo_newval = 41)
AND mo_date = ( Select MAX(mo_date)
from DDTORD C
Where DDTORD.[column_table_1] =
C.[column_table_!]
And .....
)
ORDER BY dbo.DDTORD.to_ordnum

[quoted text, click to view]

Re: Query Help

Vishal Parkar
11/22/2003 10:50:08 AM

Brian,

Pls post DDL/some sample records and expected result set. If following query doesn't
satisfy your requirement.

[quoted text, click to view]

I don't think so.

Try following query.

SELECT a.to_ordnum, b.mo_date, a.to_trak2id,
to_totdue, to_broknum --prefix right alias name here.
FROM DDTORD a INNER JOIN
(SELECT mo_recid, max(mo_date) mo_date
FROM DXMOD where mo_newval = 41
and mo_table = 'ddtord') b
ON a.to_ordnum =
b.mo_recid
inner join dxmod c on
c.mo_recid = b.mo_recid and c.mo_date = b.mo_Date
ORDER BY 1

--
-Vishal

Re: Query Help

Vishal Parkar
11/22/2003 10:51:28 AM

correction to my previous query.

SELECT a.to_ordnum, b.mo_date, a.to_trak2id,
to_totdue, to_broknum --prefix right alias name here.
FROM DDTORD a INNER JOIN
(SELECT mo_recid, max(mo_date) mo_date
FROM DXMOD where mo_newval = 41
and mo_table = 'ddtord' group by mo_recid) b
ON a.to_ordnum =
b.mo_recid
inner join dxmod c on
c.mo_recid = b.mo_recid and c.mo_date = b.mo_Date
ORDER BY 1

--
-Vishal

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.