I need to write Stored Procedure using IF Statement to Select a Begin Date and End Date based on the last Two weekends.


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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I just do not know how to make the Begin Date and End Date be the last two weekends of the month.
In case it helps, I wrote an article on building your own calendar table that make questions like this a single SQL call.
Scott PletcherSenior DBACommented:
I'm not 100% sure what you mean by "last two weekends of the month", but for purposes of completing sample code I'll assume you mean that @BeginDate should be the next-to-last Saturday of the month and that @EndDate should be the last Sunday of the month.

I'll wait on more info from you before trying to handle the case where the month ends on a Saturday -- what dates to report on then??

if (@DateRange='A')  
--Range 'A' is a special case meaning "last two weekends of the <reporting month>";
--the <reporting month> is the current month if today's day is the 4th or later,
--but if it's day 3 or less, assume requestor wants to see data from the previous month.
--To get the "last two weekends", first set the date to the last day of the <reporting month>,
--then do a calc to backup the @BeginDate to the next-to-last Saturday of the month.
--Finally, calc the @EndDate as the last Sunday of the month.
-- first, set @BeginDate to the last day of the <reporting month>
set @BeginDate = dateadd(month, datediff(month, 0, getdate()) + 1 + CASE WHEN DAY(GETDATE()) <= 3 THEN -1 ELSE 0 END), 0)
-- now, set @BeginDate to the next-to-last Saturday of that month
set @BeginDate = DATEADD(DAY, -DATEDIFF(DAY, 5, @BeginDate) % 7 - 7, @BeginDate)
-- set @EndDate to the last Sunday of that month
set @EndDate = DATEADD(DAY, 8, @BeginDate)
end --end if set two custom dates

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjc9809Author Commented:

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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.