DateAdd is dropping some data for weekending 1

[kentwoodjean]

Below is the SQL of my query. It works fine and gives me the prevous 4 weeks results with the weekending date shown as Friday. Couple questions.(1) If I have overtime on Saturday, will that data go into the following weeks end of day? Currently that is not the case, which is OK with me.(2) When I run the report on Friday, I get the previous 3 weeks and the 4th (current week) thus far. When I run it on Monday, all data but the 4th week back is good. The 4th week appears to be dropping 1 day (maybe Monday????).

If I show -5 on my date add today, I get all my 4 weeks data plus some of the 5th.

Looking for a bit of help to make this right.

SELECT [Date1]+6-Weekday([Date1]) AS Weekending, Sum(CountRecvEC.CountOfDate1) AS SumOfCountOfDate1, Sum(CountClosedEC.[CountOfClosed Dt]) AS [SumOfCountOfClosed Dt], Avg(CountClosedEC.CorpAge) AS AvgOfCorpAge, Avg(CountClosedEC.DeptAge) AS AvgOfDeptAge
FROM CountRecvEC INNER JOIN CountClosedEC ON CountRecvEC.Date1 = CountClosedEC.[Closed Dt]
WHERE (((CountRecvEC.Date1)>DateAdd("ww",-4,Date())))
GROUP BY [Date1]+6-Weekday([Date1])
HAVING ((([Date1]+6-Weekday([Date1]))<=Date()));

 

[PHV]

WHERE Date1>=DateAdd("ww",-4,Date()+2-Weekday(Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

How was it the other way grouped them by the 4 weeks? this way has it by each date so I am not sure if the total is correct.

 

[PHV]

I suggested to replace the WHERE clause only keeping the GROUP BY and the HAVING clauses.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

My fault. I was going into design view and making the adjustment rather than going into SQL so I could see what I was doing.

Data looks much better now, and I understand what you did. Understanding it is half the battle but will serve me well as I try to become proficient in writing in SQL.

Thanks a ton.