Link to home
Start Free TrialLog in
Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Date Loop for Stored Procedure

Hi all,

I have a stored procedure where there will be an input date of the week beginning. e.g. 03/03/2008 and i want my stored procedure to loop round for the 5 working days from the input date which will always be a Monday so could go DATEADD(D, 4, '03/03/2008').

Can i get it so it loops round adding each day of the working week?

Example:
User enters date 03/03/2008

Declare Variable of GrandTotal
Start Loop
Grandtotal = SELECT COUNT(DONAMOUNT) FROM MEPDATA WHERE INSERTDATE='03/03/2008'
Grandtotal = Grandtotal + SELECT COUNT(DONAMOUNT) FROM MEPDATA WHERE INSERTDATE='04/03/2008'
Grandtotal = Grandtotal + SELECT COUNT(DONAMOUNT) FROM MEPDATA WHERE INSERTDATE='05/03/2008'
Grandtotal = Grandtotal + SELECT COUNT(DONAMOUNT) FROM MEPDATA WHERE INSERTDATE='06/03/2008'
Grandtotal = Grandtotal + SELECT COUNT(DONAMOUNT) FROM MEPDATA WHERE INSERTDATE='07/03/2008'
End Loop

Hope i have explained it well enough.

Thanks in advance!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ummm...your example uses months, rather than days...so I am not sure what you need....
Avatar of drydenhogg
drydenhogg

The example is in days if you read it in UK date format not US, but know what you mean. The difficulty in the question is the phrase '5 working days' , which prevents a simple dateadd statement from working. If that really does mean 5 working days then the solution is going to be harder.

I see...I guess you'd have to include some logic for the days of the week....so, maybe

DATEPART(dw, DateField) BETWEEN 2 AND 6  --for monday through friday, assuming sunday is the first dday of the week
> from the input date which will always be a Monday  ....
doh, misread that, assumed 5 working days before not in advance, Ange's works fine then.
Avatar of matrix_aash

ASKER

yea i didn't think of doing that. thanks angelIII, that will work.
Thanks for reading my question properly unlike some of the people who replied!
matrix_aash:
glad I could help

note: don't be that harsh on people that are volunteering to help on your question. error is human.