Changing DATEFIRST to another start day

[lamarw]

Hello everyone,
I have a report that does date grouping by week. Of course that all works fine. In the report are commissions and gratuities that get totaled per week and of course that is working fine as well (I know, if everything is working so FINE why are you calling? Yeah). However, the week grouping by default has the start day either Sunday or Monday. I want my start day to be Thursday. Which means the payweek is from Thursday to Wednesday and payday is Friday.

When I run the report it is totalling to the best of it's (and my) ability but it isn't correctly reflecting the pay week. If there is no DATEFIRST way of handling this in code is there syntax for a SQL statement that gets the proper information? Something like

SELECT Commission, Gratuity FROM MyTable
GROUP BY WEEKS STARTINGDAY = 4

(I know this is incorrect SQL but I hope I'm getting the thought across)

I would much prefer to just tell the program what the STARTDAY is and let it manage the rest but a simple SQL statement would be as reliable(probably).

Any ideas?

Lamar

 

[lamarw]

Well, I've discovered that DATEPART and it's counterpart's have the ability to specify a startday and an annual startweek but can I use it in retrieveing records from a table?

 

[PWise]

SELECT empid,sum(Commission), sum(Gratuity) FROM MyTable
GROUP empid,format([Datefield],"ww",5) ,year([Datefield])

 

[lamarw]

Hi pwise!
Thank you very much for your response. I will test this solution.

Lamar

 

[lamarw]

That seems to be working. I had to add the sample to a more extensive statement. It included the HAVING clause which I was completely unfamiliar with until now.

Thanks again

This is what I ended up with:

"SELECT Format([ApptDate],'ww',5) AS ApptWeek, Year([ApptDate]) AS ApptYear, Therapists.key, Therapists.LastName AS Therapists_LastName, Therapists.FirstName AS Therapists_FirstName, Appointments.LastName AS Appointments_LastName, Appointments.FirstName AS Appointments_FirstName, Appointments.Commission, Appointments.Gratuity, Appointments.ApptDate FROM Therapists INNER JOIN Appointments ON Therapists.key=Appointments.TherapistID GROUP BY Format([ApptDate],'ww',5), Year([ApptDate]), Therapists.key, Therapists.LastName, Therapists.FirstName, Appointments.LastName, Appointments.FirstName, Appointments.Commission, Appointments.Gratuity, Appointments.ApptDate, Appointments.Finished, Appointments.Billed, Appointments.ApptDeleted HAVING (((Appointments.Finished)=True) AND ((Appointments.Billed)=True) AND ((Appointments.ApptDeleted)=False)) ORDER BY Format([ApptDate],'ww',5), Year([ApptDate]);"

Any comments?

Lamar