如何使用存储过程计算特定月份的工作日 [英] How to calculate working days in particular month by using stored procedure

问题描述

实际上我有一个名为month_master的表，其中包含name_month，start_date，end_date

，我想编写存储过程，它将计算存储在数据库中的每个月的总工作日。



我尝试了什么：

  SELECT  m.Month_Name，
 [Days  in  Month] = day（dateadd（mm，datediff（mm， -1，m.StartDate）， -  1）），
（ SELECT   convert （< span class =code-keyword> decimal ，（ select  COUNT（att.PRESENT）））
   -    [WDays in Month] =（DATEDIFF（dd，m.StartDate，m.EndDate）+ 1） - （DATEDIFF（wk，m.StartDate） ，m.EndDate）* 2） - （当DATENAME（dw，m.StartDate）='星期日'然后1结束0结束时的情况） - （DATENAME（dw，m.EndDate）='星期六'然后1 ELSE 0 END） 
  FROM  ATTENDANCE_MASTER ATT 
  WHERE  att.STUD_ID =  @ Id  和 att_date> = m.StartDate 和 att_date< = m.EndDate 和 att.PRESENT = '  1'）
  as 现在
 来自 Month_Table m 

解决方案

我使用递归公用表表达式（CTE）获取范围内的所有日期，然后得到工作日部分（Sun = 1，Sat = 7）：

 声明  @ start   as   date ， @ end  < span class =code-keyword> as  日期 
 
 设置  @ start  = '  01 jan 2016' 
  set   @ end  = '  2016年1月31日 
 
;   -    CTE在它们之前不能有任何选择。声明不计，但cte不知道。声明部分，并用分号设置。你不需要在Stopred Proc中使用它。 
 
   -   首先，递归获取范围内的所有日期： 
   allDates  as （
 选择  @start  为 [< span class =code-keyword> date ] 
  union  全部 
 选择 dateadd（日， 1 ，[日期]）
 来自 allDates   -   注意递归。它很酷^ _ ^  
 其中​​ [ date ]< @end）
，
   -   秒，得到工作日的枚举值。注意，级联CTE用逗号 
 allWeekDays  as （
  select  [ date ]，datepart（工作日，[ date ]） as  [dayofweek] 
  from  allDates 
）
   -  然后执行您的选择 
 选择 count（*）来自 allWeekDays 其中​​ [dayofweek] 不  in （ 1 ， 7 ）

这将为您提供给定范围内所有星期一至星期五的计数。



银行假期很少有逻辑。因此，您可以找到未来银行假期的列表，这些假期一直持续到2070年（这只是一个例子，但它很优惠：银行和世界公众假期 - 1970-2070 [ ^ ] ，但我也为英国下载了一个免费的2110！）



可能需要考虑银行假期。如果是这样，为他们创建一个db表并排除该表中的任何所有日期



希望有所帮助^ _ ^



Andy

actually i have table named month_master this contains name_month, start_date, end_date
and i want to write stored procedure which will calculate total working days present in each month stored in database.

What I have tried:

SELECT   m.Month_Name,
	[Days in Month] =day(dateadd(mm,datediff(mm,-1,m.StartDate),-1)),
	(SELECT convert(decimal,(select COUNT(att.PRESENT)))
	--[WDays in Month] = (DATEDIFF(dd, m.StartDate, m.EndDate) + 1)-(DATEDIFF(wk, m.StartDate, m.EndDate) * 2)-(CASE WHEN DATENAME(dw, m.StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, m.EndDate) = 'Saturday' THEN 1 ELSE 0 END)
	FROM ATTENDANCE_MASTER ATT
	WHERE att.STUD_ID = @Id  and att_date >= m.StartDate and att_date <= m.EndDate and att.PRESENT='1'    )
	as PRESENT
	from Month_Table m

解决方案

I'd use a recursive Common Table Expression (CTE) to get all dates in the range, then get the weekday part (Sun = 1, Sat = 7):

declare @start as date, @end as date

set @start = '01 jan 2016'
set @end = '31 jan 2016'

;--CTEs can't have any selects before them.  Declares don't count but the cte doesn't know that.  section off the declares and sets with a semi-colon.  You don't need this in a Stopred Proc.

--First, recursively get all dates in range:
with allDates as (
	select @start as [date]
	union all 
	select dateadd(day,1, [date])
	from allDates                --Note the recursion.  It's just cool ^_^
	Where [date] <@end)
,
--second, get the weekday enum value.  NB, cascade CTE's with a comma
allWeekDays as (
	select [date], datepart(weekday,[date]) as [dayofweek]
	from allDates
	)
--Then perform your select
select count(*) from allWeekDays where [dayofweek] not in (1,7)

This will give you a count of all of the Mondays to Fridays in a given range.

Bank holidays rarely have as much logic. Because of this you can find lists of future bank holidays that go all the way to 2070 (this one is just an example but it premium: bank and public holidays of the world - 1970-2070[^], but I also downloaded a free one for UK that goes to 2110!)

Bank holidays may need to be factored in. if so, create a db table for them and exclude any allDates that are in that table

Hope that helps ^_^

Andy

这篇关于如何使用存储过程计算特定月份的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！