محاسبه تفاوت بین دو روز به استثنای تعطیلات آخر هفته در SQL Server
فرض کنید می خواهیم روزهای کاری را از محدوده تاریخ پیدا کنیم. محاسبه اختلاف تاریخ کمی خسته کننده است زیرا باید تعطیلات آخر هفته و تعطیلات را حذف کنیم. در اینجا نحوه محاسبه روزهای کاری را یاد می گیریم.
بیایید ببینیم که چگونه می توانیم تاریخ کار را محاسبه کنیم
ابتدا باید ToDate و FromDate را تعریف کنیم.
Declare @Fromdate datetime, @Todate datetime,@Temp datetime
declare @day int
— دادن مقدار اولیه
‘Set @Fromdate=’9/2/2016
‘set @Todate=’9/10/2016
اگر شروع و پایان هفته از یکشنبه یا شنبه تا دوشنبه بود، باید FromDate را تنظیم کنیم. چون یکشنبه و شنبه را حذف می کنیم. بیایید ببینیم چگونه این تاریخ را حذف می کنیم.
if DATEPART(dw,@Fromdate)=1 set @Fromdate=@Fromdate+1 if DATEPART(dw,@Fromdate)=7 Set @Fromdate=@Fromdate+2 |
با کمک عملکرد DATEPART ما اقدام به یافتن روز میکنیم.
DATEPART (dw,@Date) مقدار عددی را برمی گرداند.
توجه: اولین روز هفته در هر کشور متفاوت است و باید ذر SQL Server تنظیم شود. با دستور ‘SELECT @@DATEFIRST AS ‘Result میتوان یافت که اولین روز هفته چه روزی تنظیم شده است.
if DATEPART(dw,@todate)=1 set @todate=@todate-2
if DATEPART(dw,@todate)=7 Set @todate=@todate-1
اکنون تاریخ شروع و پایان را بدون احتساب تاریخ دریافت می کنیم، اما به خاطر داشته باشید که این محدوده دارای آخر هفته نیز می باشد. حالا اقدام به حذف تاریخ آخر هفته می کنیم.
select @day=dateDiff(dd,@Fromdate,@todate)-(dateDiff(WK,@Fromdate,@Todate)*2) |
DATEDIFF(WK,@fromdate,@Todate) تعداد هفته را در محدوده داده شده برمی گرداند. پس ممکن است فکرکنید که چرا در 2 ضرب می شده است. دلیل آن این است که این تابع هفته ها را برمی گرداند، اما باید دو روز (یکشنبه و شنبه) را حذف کنیم.
اگر ToDate ما در آخر هفته باشد، آن را به عنوان جمعه تنظیم می کنیم. منظورم این است که باید جمعه را یک بار دیگر بشماریم. در نهایت باید بررسی کنیم که آیا ToDate تاریخ آخر هفته است یا خیر، باید یک روز دیگر به نتیجه اضافه کنیم. کوئری زیر را ببینید.
select @day= case when Datepart(dw,@Temp) in (1,7) then @day +1 else @day end
بالاخره تاریخ کار را می گیریم
اسکریپت کامل sql را در زیر ببینید.
Declare @Fromdate datetime, @Todate datetime,@Temp datetime
declare @day int
‘Set @Fromdate=’9/2/2016
‘Set @Todate=’9/10/2016
if DATEPART(dw,@Fromdate)=1 set @Fromdate=@Fromdate+1
if DATEPART(dw,@Fromdate)=7 Set @Fromdate=@Fromdate+2
Set @temp=@todate
if DATEPART(dw,@todate)=1 set @todate=@todate-2
if DATEPART(dw,@todate)=7 Set @todate=@todate-1
select @day=dateDiff(dd,@Fromdate,@todate)-(dateDiff(WK,@Fromdate,@Todate)*2)
select @day= case when Datepart(dw,@Temp) in (1,7) then @day +1 else @day end
Select @day as [Number of working day]
با اجرای این کوئری، نتیجه خروجی برای این مثال، 6 خواهد بود. یعنی تعداد روزهای کاری بین این دو تاریخ مشخص شده، 6 روز است.
از تقویم سیستم هم میتوانید این را برای این مثال خاص بررسی کنید (البته اگر بازه تاریخ انتخابی شما خیلی بزرگ است از این تقویم بررسی اش سخت است)
نظر بگذارید