sql server msde: Hit Steve,

you sure have to put that back in your formatted sql string syntax, but
that be the solution you are searching for:

Select TOP 1 *
from [stheader]
inner join [customer] on
customer.[cust_code] =3D stheader.[cust_code]
where
([stheader].[stat_date] =3D maxfn and
[stheader].[cycle] =3D 'F')
OR
([stheader].[stat_date] =3D 'maxmonth' and
[stheader].[cycle] =3D 'M')
OR
([stheader].[stat_date] =3D 'maxweek'
and [stheader].[cycle] =3D 'W')
ORDER BY stat_date

Try to avoid the "*" in production code, something would have been even
clearer to me if you wouldn=B4t use the asteriks in there. I know for
some developers thats a religious war, but save yourself a lot time and
code your columnNames. BTW why didn=B4t you use a view for this, that
would be a good work for this query.


BTW: please provide us with DDL and sample data to help you better.
http://www.aspfaq.com/5006

HTH, jens Suessmeyer.

Sorry missed that:

select MAX(stat_date),
CustomerId,
cycle
from [stheader]
inner join [customer] on
customer.[cust_code] = stheader.[cust_code]
where
([stheader].[stat_date] = 'maxfn' and
[stheader].[cycle] = 'F')
OR
([stheader].[stat_date] = 'maxmonth' and
[stheader].[cycle] = 'M')
OR
([stheader].[stat_date] = 'maxweek'
and [stheader].[cycle] = 'W')
Group by customerID,cycle--,... Some other columns you want to display
ORDER BY stat_date

PS: Still no sample or DDL data ? :-D

HTH, Jens Suessmeyer.

Hi All

I have a statement table which stores customers latest statement details and
I retrieve them with the code below

I am using MSDE 2000

maxmonth, maxfn, maxweek are calculated dates

sql = "Select * from [stheader] inner join [customer] on
customer.[cust_code] = stheader.[cust_code]" & _
" where [stheader].[stat_date] = '" & Format(maxmonth, "m-d-yyyy") &
"' and [stheader].[cycle] = 'M'" & _
" UNION ALL Select * from [stheader] inner join [customer] on
customer.[cust_code] = stheader.[cust_code]" & _
" where [stheader].[stat_date] = '" & Format(maxfn, "m-d-yyyy") & "'
and [stheader].[cycle] = 'F'" & _
" UNION ALL Select * from [stheader] inner join [customer] on
customer.[cust_code] = stheader.[cust_code]" & _
" where [stheader].[stat_date] = '" & Format(maxweek, "m-d-yyyy") &
"' and [stheader].[cycle] = 'W'" & _
" order by stheader.cust_code"

The problem I have is that if a customer has his statemnent date changed
from say monthly to weekly then this sql can return 2 copies of the customer
1 for his last monthly statement and 1 for the new weekly one

I only want the most recent returned for each customer regardless if it is
monthly, fortnightly or weekly

Any ideas

Regards
Steve

Hi Jens

This answer does not return what I want
It only returns 1 record in total

I need the latest record for each customer

DDL
stat_date cust_code cycle p_end0 p_end1 p_end2 p_end3
30/11/05 COOSH M 0 0 0
0
25/12/05 COOSH W 100 0 0 0
18/12/05 AMAS W 10 0 32
0
25/12/05 AMAS W 20 0 0
32

I need to return COOSH for 25/12/05 but my original code returns both as
30/11/05 satisfies the monthly where

Any ideas please

Regards
Steve

[quoted text, click to view]
Hit Steve,

you sure have to put that back in your formatted sql string syntax, but
that be the solution you are searching for:

Select TOP 1 *
from [stheader]
inner join [customer] on
customer.[cust_code] = stheader.[cust_code]
where
([stheader].[stat_date] = maxfn and
[stheader].[cycle] = 'F')
OR
([stheader].[stat_date] = 'maxmonth' and
[stheader].[cycle] = 'M')
OR
([stheader].[stat_date] = 'maxweek'
and [stheader].[cycle] = 'W')
ORDER BY stat_date

Try to avoid the "*" in production code, something would have been even
clearer to me if you wouldn´t use the asteriks in there. I know for
some developers thats a religious war, but save yourself a lot time and
code your columnNames. BTW why didn´t you use a view for this, that
would be a good work for this query.


BTW: please provide us with DDL and sample data to help you better.
http://www.aspfaq.com/5006

HTH, jens Suessmeyer.

Hi Jens

This answer does not return what I want
It only returns 1 record in total

I need the latest record for each customer

DDL
stat_date cust_code cycle p_end0 p_end1 p_end2 p_end3
30/11/05 COOSH M 0 0 0
0
25/12/05 COOSH W 100 0 0 0
18/12/05 AMAS W 10 0 32
0
25/12/05 AMAS W 20 0 0
32

I need to return COOSH for 25/12/05 but my original code returns both as
30/11/05 satisfies the monthly where

Any ideas please

Regards
Steve

[quoted text, click to view]
Hit Steve,

you sure have to put that back in your formatted sql string syntax, but
that be the solution you are searching for:

Select TOP 1 *
from [stheader]
inner join [customer] on
customer.[cust_code] = stheader.[cust_code]
where
([stheader].[stat_date] = maxfn and
[stheader].[cycle] = 'F')
OR
([stheader].[stat_date] = 'maxmonth' and
[stheader].[cycle] = 'M')
OR
([stheader].[stat_date] = 'maxweek'
and [stheader].[cycle] = 'W')
ORDER BY stat_date

Try to avoid the "*" in production code, something would have been even
clearer to me if you wouldn´t use the asteriks in there. I know for
some developers thats a religious war, but save yourself a lot time and
code your columnNames. BTW why didn´t you use a view for this, that
would be a good work for this query.


BTW: please provide us with DDL and sample data to help you better.
http://www.aspfaq.com/5006

HTH, jens Suessmeyer.

That should do it, if you awant to you can get me an email privately
not to quote ourself to death :-) We will post this then back to the
NG. If you will contact me please rpovide some DDl and sample data
http://www.aspfaq.com/5006

jens Suessmeyer.

Jens

This still does not work

It returns 1 record with the maximum date

I want the record with the latest date FOR EACH CUSTOMER

Example table data...
stat_date cust_code cycle p_end0 p_end1 p_end2 p_end3
30/11/05 COOSH M 0 0 0
0
25/12/05 COOSH W 100 0 0
0
18/12/05 AMAS W 10 0 32
0
25/12/05 AMAS W 20 0 0
32

The code should return ONLY...
25/12/05 COOSH W 100 0 0
0
25/12/05 AMAS W 20 0 0
32

as they are the most recent entries for the 2 customers, even when COOSH has
changed from M (Monthly) to W(Weekly)

Regards
Steve



[quoted text, click to view]