Max Date of a Week

[dscoiho]

I am using SQL 2000 and I have the following query.
SELECT C.CONTACT_ID,
TH.TRADE_YEAR,
MAX(TH.POSTING_DATE) AS ENDOFWEEK,
DATEPART(WK,TH.POSTING_DATE) AS WEEK,
ISNULL(CASE
WHEN TH.TRADE_CLASS IN ('S','Z') THEN SUM(TH.GROSS_AMOUNT)
END,0) AS SALES,
ISNULL(CASE
WHEN TH.TRADE_CLASS IN ('R','W') THEN SUM(TH.GROSS_AMOUNT)
END,0) AS REDEMPTIONS

FROM DBO.CONTACT C
LEFT OUTER JOIN DBO.TRANSACTION_HISTORY TH
ON C.CONTACT_ID = TH.CONTACT_ID
LEFT OUTER JOIN DBO.FUNDS FD
ON TH.FUND = FD.FUND
LEFT OUTER JOIN DBO.REP_PROFILE RP
ON C.CONTACT_ID = RP.CONTACT_ID

GROUP BY C.CONTACT_ID,TH.TRADE_YEAR,DATEPART(WK,TH.POSTING_DATE),TH.TRADE_CLASS

What I am trying to do is summarize the weekly totals by Contact_ID, by Year, by Week, with sum of sales and Redem.
I also want the MAX date of the corresponding week. Problem I am having is that the Maximum date is not showing the Max of the week just of the Transaction
ex... 2005 week 2 is MAX Date is 01-06-2005 should be 01-08-2005. Any guidance you can give would be appreciated.

 

[Crowley16]

you mean how to find the last date of the week?

how's about:
select DATEADD(wk, DATEDIFF(wk, 0, 'someDate' - 1), 6)

the 0 means monday is the start day of week, change that to 1 for tuesday and so on...

--------------------
Procrastinate Now!