Solved

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

Posted on 2008-06-25
7
219 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 57
SQL Convert rows to columns 5 34
Restore a log backup compressed 3 17
Need split for SQL data 7 50
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

751 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