Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-25
7
Medium Priority
?
226 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Accepted Solution

by:
folderol earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

718 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