Work days this week within current month

[stillsteam]

Hi
Need some help finding out how many work days it is this week in the current month counting back from today

I will use this to calculate a salesbudget for those days

I have found in this forum solutions to how many workdays it is this week but I don´t know how to make it work for current month only.

SQL2005

Declare @_WeekStartdate smalldatetime
SET @_WeekStartdate = dateadd(wk, datediff(Wk, 0, getdate()), 0)
 ((((DATEDIFF(dd, @_WeekStartDate, @EndDate) + 1) - ((DATEDIFF(dd, @_WeekStartDate, @EndDate) + 1) % 7)) / 7) * 5)  + CASE WHEN (((DATEDIFF(dd, @_WeekStartDate, @EndDate) + 1) % 7) - CASE WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime),  @_WeekStartDate) % 7) + 1) = 1 THEN 1 WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @_WeekStartDate) % 7) + 1)  = 7 THEN 2 ELSE 0 END - CASE WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @EndDate) % 7) + 1) = 1 THEN 2 WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @EndDate) % 7) + 1) = 7 THEN 1 ELSE 0 END) <= 0 THEN 0 ELSE (((DATEDIFF(dd,@_WeekStartDate, @EndDate) + 1) % 7) - CASE WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @_WeekStartDate) % 7) + 1) = 1 THEN 1 WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @_WeekStartDate) % 7) + 1) = 7 THEN 2 ELSE 0 END - CASE WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @EndDate) % 7) + 1) = 1 THEN 2 WHEN ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @EndDate) % 7) + 1) = 7 THEN 1 ELSE 0 END) END AS [Weekarbetsdagar],

Thanks in advance...

/Jonas

 

[stillsteam]

@Enddate = Getdate()

/Jonas

 

[gmmastros]

I don't understand your question. Can you please show some sample data and expected results? I think that would help us a lot.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

Use the @s variable as the start date and the @e variable as the end date (inclusive).

DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = '20081001'
SET @e = '20081104'



SELECT
  days/7*5 + days%7
    - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
    - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
        DATEDIFF(day, @s, @e) + 1 AS days,
        DATEPART(weekday, @s + @@DATEFIRST - 1) AS wd
     ) AS D

HTH,

M.

 

[stillsteam]

I don't have sample data.
What I want is a count for how many working days it is between Monday (this week) - Today.
But it has to within current month.
I will run this report every week but if there is a month-break like it was in Sept-Oct (Sept 30 Tuesday)and I run the report on Thursday I want 2 days.

I will use these numbers to calculate the salesbudget.
I known the yearly budget and I know the percentage every month (9% in Oct) but they want to know the weekly salesbudget.
Hope I explained it a little bit better

Thanks

/Jonas

 

[stillsteam]

Thanks M.

Alot nicer code than what I had but I think its the

SET @s =

where the trouble is.



/Jonas

 

[stillsteam]

I've tried this and it seems to be working

SET @_WeekStartdate = case when Month(Dateadd(wk,datediff(wk,0,getdate()),0))<> Month(getdate()) then dateadd(MM,datediff(MM,0,getdate()),0) else dateadd(wk,datediff(wk,0,Getdate()),0) end

IF Month(Monday this week) <> Month(Today) THEN First day of month ELSE Monday.

I think that will work

/Jonas

 

[mutley1]

Do you mean if Monday is the 30th of a 31 day month and you run this on a Friday you want the result to be 3 days (i.e. Mon is 30th, Tues is 31st)........then you want to know that Weds, Thurs Fri are the 1st, 2nd and 3rd and hence get just 3 days back?

 

[stillsteam]

Yes, that is exactly what I need.

/Jonas

 

[mutley1]

Is it a package / job or is it something you run manually?

 

[stillsteam]

I´m using Reporting Services and display the report in Moss 2007 portal. It will run automatic every Saturday and a copy in Excel-format will be posted in this portal.
But we want to run it manually sometimes too.

/Jonas

/Jonas

 

[mutley1]

Try this,

@s should now pull back the first day of the month and @e is 'todays date' so the calc should do working days between.

DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = dateadd(month,datediff(month,0, getdate()+1),0)
SET @e = getdate () --'20081105'



SELECT
  days/7*5 + days%7
    - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
    - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
        DATEDIFF(day, @s, @e) + 1 AS days,
        DATEPART(weekday, @s + @@DATEFIRST - 1) AS wd
     ) AS D

Take out the getdate() in @e and play with the date field that is remmed out and you should see what it's doing.

Any help?

M.

 

[stillsteam]

This work the first week of the month but not the weeks after that.

I've tried this

case when Month(Dateadd(wk,datediff(wk,0,getdate()),0))<> Month(getdate()) then dateadd(MM,datediff(MM,0,getdate()),0) else dateadd(wk,datediff(wk,0,Getdate()),0) end

and it seems to be working

I get first day of month if monday this week is in previous month otherwise I get monday´s date this week.


/Jonas

 

[mutley1]

Glad you're up and running.