June 25, 2011 at 10:45 pm
Hello Everybody!
I am not sure if this task is easy or complicated. But I need to write a stored procedure that needs to reference a table & I am unsure on how to go about this, I think this could be achieved through a join but I could be wrong.
Scenario:
In my application I have the USER inputting a Start Date & End Date. now this goes into the database no problem, I can sum these two date ranges & populate a column in SQL with the number of days in the same table (Table A), which also isn't a problem. The problem is I don't want to account for weekends & specific individual dates. I have a table (Table B) that has all the information of weekend dates of every month & those individual specific dates as well. What I am trying to achieve is, once the User inputs the Start Date and End Date which gets input into "Table A", the stored procedure populates the column Value ("TotalDays") by doing a sum by referencing the weekends/holidays data in "Table B". So for instance if I pick 7 days, which include a Saturday & Sunday, my sum value should be 5.
I hope someone can help me out.
Thanks a lot
June 26, 2011 at 8:44 am
Posts like this put a hard burden on us to blindly write a bunch of code for you. You'll get much more success by posting your mostly-written stored proc that doesn't work quite right and asking a specific question about the missing/broken piece.
Also post the DDL for the tables involved to clarify things.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 26, 2011 at 9:31 am
Follow Jim's advice, but as a few hints.
1. write a SELECT that calculates the data first. You can do this for all rows, joining with your table of days. Once that's working, you can change it to an update, wrap it in a stored proc that grabs parameters for the ID or the start and end dates.
2. When joining with the other table, if you have some days marked as holidays, weekends, add a SUM for those, and substract that from your total days.
June 26, 2011 at 10:28 am
Hey guys,
this gives me the interval excluding weekends, I was wondering instead of doing a join, is there a way I could exclude specific dates in this query?:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DATEDIFFWEEKENDSDATES
(
@startdate as DATETIME,
@enddate as DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @res int
SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
-(DATEDIFF(wk, @startdate, @enddate) * 2)
-(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)
RETURN @res
END
GO
June 26, 2011 at 10:40 am
you're not doing a join here.
Also, what do you mean by exclude some dates? Is there a reason you don't have these marked in your calendar table?
June 26, 2011 at 10:55 am
yes I know I am not doing a join over there. I have a calendar table with all the dates specified. But I figured I might be able to get around that by just taking the start date and end date from the application which is being inserted into SQL. Then within that table it is being inserted in, to run a query that populates a column which will exclude weekends and specific dates instead of doing a join onto the calendar table. Hence that's why I asked if I can input those specific dates in that query instead of doing a join? that would make things straight forward for me.
June 26, 2011 at 12:47 pm
Why don't you want to join with the calendar table? Do you somehow think this is more efficient? Likely it's not if you plan on running a series of calculations here.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply