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

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.....
LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
 
folderolConnect With a Mentor Commented:
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
 
folderolCommented:
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
 
folderolCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Auerelio VasquezETL DeveloperAuthor Commented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
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
 
folderolCommented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
PERFECT! This worked perfectly......i tweaked it a little because of requirements change, but it's working like a charm. thanks!
0
All Courses

From novice to tech pro — start learning today.