Solved

Create a stored procedure to take in a date param, and depending on the day, take actions...

Posted on 2008-06-25
7
207 Views
Last Modified: 2012-05-05
I would like to create a stored procedure that does this:

Takes in two data parameters: Start Date and End Date

if the start date is monday, then i want to show monday's results, if it's tuesday i want to show monday and tuesday, if wednesday then m,t,w, etc till friday..... on friday i want to show the results of all five days.

i know this is possible using a datepart(dw, column) function, but 'm having a time putting the rest of it together.....
0
Comment
Question by:basile
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 21871568
create procedure week_to_date @enddate datetime as
declare @startdate datetime
if datepart(weekday, @enddate) not in (1,7)
begin
   set @startdate = cast(datediff(day,0,@enddate) - datepart(weekday, @enddate) as datetime)
   select * from my_table
          where resultdate between @startdate and @enddate
end
else
   select 'No report on weekends.'
go

From what I understand you want, you don't need to pass two parameters.  You can deduce the start from the end.
The datediff will remove the time portion if the parameter has one, so @starttime will be set to midnight.  You don't need it if you pass integer datetime values or varchar time.

0
 
LVL 19

Expert Comment

by:folderol
ID: 21871581
oops, pasted wrong lines ...

create procedure week_to_date @enddate datetime as
declare @startdate datetime
if datepart(weekday, @enddate) not in (1,7)
begin
   set @startdate = cast(datediff(day,0,@enddate) - datepart(weekday, @enddate) + 2 as datetime)
   select * from my_table
          where resultdate between @startdate and @enddate
end
else
   select 'No report on weekends.'
go
0
 
LVL 1

Author Comment

by:basile
ID: 21871593
well, it's not as simple as no weekends.... I have a report that has three colums: Date_entered, Technician, Numberofclosedtix

so, the first thing i want to check is the current day: and if the day is monday (i guess integer value 2) then on the report i want to show only values in the table where the day is monday: if it's tuesday i want to show tuesday and monday (one day before only though, i don't want to show all monays).


does that make sense? seem possible?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19

Accepted Solution

by:
folderol earned 500 total points
ID: 21871887
Then my code snippet works if you pass the procedure the current dateandtime.
Call the proc with

declare @today datetime
set @today = getdate()
exec week_to_date @today

The procedure changes the current day @today parameter to Monday.  That is the "Set @startdate ..." line.  If the current day is Monday, then the where clause works out to "between Monday and Monday" so you get one day. If @today is Tuesday, then the where clause becomes, "Between Monday and Tuesday", and so on.

If you are always going to use the current date, then you don't need parameters at all.

create procedure week_to_date as
declare @startdate datetime
   set @startdate = cast(datediff(day,0,getdate()) - datepart(weekday, getdate()) + 2 as datetime)
   select
   Date_entered, Technician, Numberofclosedtix
   from my_table
   where Date_entered between @startdate and getdate()
GO

This syntax requires the datediff() because you have to reset the startdate to midnight otherwise the between doesn't work.  Between is shorthand for
Date_entered >= @startdate and Date_entered <= getdate()

0
 
LVL 1

Author Comment

by:basile
ID: 21871991
ok i do understand that now, one question that comes to mind now is that the times are always static to 5:30, so will that make a difference? i will start to work on this in the morning and i'll let you know how i come up with. i have a weird query, so i'll send back the code snippet. thanks so much
0
 
LVL 19

Expert Comment

by:folderol
ID: 21876091
I don't have enough information to judge the static 5:30.  If you always run this after 5:30 or if the report is empty for today until 5:30, when all the technician data is updated, then no, it won't matter.  For defensive programming against Murphy's Law, you can use the dateadd function to make sure.
0
 
LVL 1

Author Comment

by:basile
ID: 21879062
PERFECT! This worked perfectly......i tweaked it a little because of requirements change, but it's working like a charm. thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now