jjc9809
asked on
I need to write Stored Procedure using IF Statement to Select a Begin Date and End Date based on the last Two weekends.
Hello,
I have created a stored procedure that will count the amount of times a person is checked in with a fingerprint scan and the amount of times a person has manually checked in. I have already obtained these calculations.
However, I need to create a report that will show these counts always based on the Last Two Weekends in a month that the person is visiting. These dates should come in automatically based on the system date that a user is signed in.
I just do not know how to make the Begin Date and End Date be the last two weekends of the month. There has to be some type of calculation in the sp that is done to do this based on a DateRange Parameter that is entered. For example, if the user types in the textbox field called DateRange a letter "A" the Begin Date and End Date fields should automatically populate with the dates of the last two weekends always.
The counts and other data should come in based on these dates.
Any help is very much appreciated. I have attached a stored procedure that I have been working on. Please review this sp and you will see what I am trying to do.
Thanks
SP-For-Scan.doc
I have created a stored procedure that will count the amount of times a person is checked in with a fingerprint scan and the amount of times a person has manually checked in. I have already obtained these calculations.
However, I need to create a report that will show these counts always based on the Last Two Weekends in a month that the person is visiting. These dates should come in automatically based on the system date that a user is signed in.
I just do not know how to make the Begin Date and End Date be the last two weekends of the month. There has to be some type of calculation in the sp that is done to do this based on a DateRange Parameter that is entered. For example, if the user types in the textbox field called DateRange a letter "A" the Begin Date and End Date fields should automatically populate with the dates of the last two weekends always.
The counts and other data should come in based on these dates.
Any help is very much appreciated. I have attached a stored procedure that I have been working on. Please review this sp and you will see what I am trying to do.
Thanks
SP-For-Scan.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott,
We are trying to keep track of visitors to inmates in our prison facilities. Visits are made on Saturday and Sunday every week of the month. After thinking about this and reading the user's request over and over about the last two weekends, I believe the user means he wants to be able to enter a BeginDate and End Date for any Saturday and Sunday for any week in the month and determine a count of fingerprint scans and manual scans made of visitors to each institution.
I believe he wants to enter the first Saturday of the month and the next Sunday date of the month and determine the count. The same is true for the second Saturday and third week Sunday of a month or the 3rd Saturday and 4th Sunday week of the month.
I believe a custom date would work fine here. What do you think?
We are trying to keep track of visitors to inmates in our prison facilities. Visits are made on Saturday and Sunday every week of the month. After thinking about this and reading the user's request over and over about the last two weekends, I believe the user means he wants to be able to enter a BeginDate and End Date for any Saturday and Sunday for any week in the month and determine a count of fingerprint scans and manual scans made of visitors to each institution.
I believe he wants to enter the first Saturday of the month and the next Sunday date of the month and determine the count. The same is true for the second Saturday and third week Sunday of a month or the 3rd Saturday and 4th Sunday week of the month.
I believe a custom date would work fine here. What do you think?
In case it helps, I wrote an article on building your own calendar table that make questions like this a single SQL call.