asked on

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.....

folderol

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.

folderol

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

Auerelio Vasquez

ASKER

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?

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Auerelio Vasquez

ASKER

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

folderol

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.

Auerelio Vasquez

ASKER

PERFECT! This worked perfectly......i tweaked it a little because of requirements change, but it's working like a charm. thanks!