Solved

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

Posted on 2008-06-25
7
224 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:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Auerelio Vasquez
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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:Auerelio Vasquez
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:Auerelio Vasquez
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 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