Two Method for Return the Count of Weekday between two date!
--Two Method for Return the Count of Weekday between two date!
declare @b datetime,@e datetime
set @b = '2004-10-03'
set @e = '2004-11-04'
set datefirst 3
--playyuer@Microshaoft.com Invent
select
@b as BeginDate
,@e as EndDate
,datediff(week,@b,@e) + 1
--+ case when (@@datefirst + datepart(weekday,@b)) % 7 = 1 then 1 else 0 end
- case when (@@datefirst + datepart(weekday,@e)) % 7 = 1 then 1 else 0 end as CountOfWeeks
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 2 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 2 then 0 else 1 end as CountOfMonday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 3 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 3 then 0 else 1 end as CountOfTuesday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 4 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 4 then 0 else 1 end as CountOfWednesday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 5 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 5 then 0 else 1 end as CountOfThursday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 6 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 6 then 0 else 1 end as CountOfFriday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 7 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 7 then 0 else 1 end as CountOfSaturday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 1 then 0 else 1 end as CountOfSunday
--zjcxc Invent
select
@b as BeginDate
,@e as EndDate
,CountOfWeeks
,CountOfMonday=case a
when -1 then case when 1 between b and c then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,CountOfTuesday=case a
when -1 then case when 2 between b and c then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,CountOfWednesday=case a
when -1 then case when 3 between b and c then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,CountOfThursday=case a
when -1 then case when 4 between b and c then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,CountOfFriday=case a
when -1 then case when 5 between b and c then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,CountOfSaturday=case a
when -1 then case when 6 between b and c then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,CountOfSunday=case a
when -1 then case when 0 between b and c then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select CountOfWeeks=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
,a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end) T 
This stored proc notify the user(s) if the data/log file grew. When you execute it for the first time it will get the data/log files sizes and inserts in a table. Next time when you run the script if will compare the current size with the previous files size. This script must need sqlmail configured […]
2003-11-07
1,469 reads
A Procedure send Alert by E-Mail about allfiles with option "Restrict File Growth" ifavailable space less then 10%.
2003-02-24
305 reads
SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example […]
2005-10-12 (first published: 2002-10-08)
3,758 reads
Here is a strait forward script that allows you to use CDONTS to send email. Note that the Subject, From, CC and BCC are all optional parameters. Because it uses a fully qualified path, this procedure can live in any database on the server. CDONTS will need to be installed on the server.
2002-06-06
682 reads
This stored procedure can be used to send mail through CDONTS and SQL Server. It resides in the Master database and accepts email addresses and subjects with a maximum of 50 characters and a body with a maximum of 500 characters. (You can increase or decrease these according to your requirements.) For this stored procedure […]
2002-06-03
2,130 reads