Extract value against highest date

[Guest]

I have an invoice table which monitors marketing information. I want to
extract the latest information for a client but EXCLUDE any invoices for
monthly support identified as BSO payments. I have this so far

SELECT t.CODE, t.CLIENT, t.INV_MONTH, t.WHYBOOK
FROM Transinv AS t
WHERE (((t.INV_MONTH)=(SELECT Max(inv_month) FROM Transinv WHERE code =
t.code)) AND ((t.WHYBOOK) Not Like "185 bso*"));

The problem I have is that if the latest invoice is identified as BSO then I
get nothing for that client rather than the latest 'normal' invoice. Must
have my WHERE wrong somewhere I think (don't really use SQL much).... any
help much appreciated.

Sheila

 

[Guest]

SELECT TOP 1
t.CODE,
t.CLIENT,
t.INV_MONTH,
t.WHYBOOK
FROM Transinv t
WHERE t.WHYBOOK Not Like "185 bso*"
ORDER BY t.INV_MONTH desc;

A lot depends on the type of data in INV_MONTH. If it's 1 though 12 the desc
will work except that December of 2005 will show up instead of June 2006.

 

[Guest]

Hi Jerry

Thanks for reply - this extracts the overall latest record - what I need is
the latest for each client hence the grouping. Any other ideas?

Sheila

 

[Guest]

Try this:

SELECT
TV.CODE,
TV.CLIENT,
TV.INV_MONTH,
TV.WHYBOOK
FROM Transinv as TV
WHERE TV.INV_MONTH IN
(SELECT TOP 1 T1.INV_MONTH
FROM Transinv AS T1
WHERE T1.CLIENT = TV.CLIENT
AND T1.WHYBOOK Not Like "185 bso*"
ORDER BY T1.INV_MONTH DESC);

 

[Guest]

Brilliant, works a treat thanks
Sheila

Try this:

SELECT
TV.CODE,
TV.CLIENT,
TV.INV_MONTH,
TV.WHYBOOK
FROM Transinv as TV
WHERE TV.INV_MONTH IN
(SELECT TOP 1 T1.INV_MONTH
FROM Transinv AS T1
WHERE T1.CLIENT = TV.CLIENT
AND T1.WHYBOOK Not Like "185 bso*"
ORDER BY T1.INV_MONTH DESC);