بعضی اوقات وقتی ما میخواهیم کوئری هایی در ارتباط با تاریخ در دیتابیس ها بنویسیم ممکن است با یکسری مشکلات مرسوم روبرو شویم در واقع اگر به این موضوع کمی دقت کنیم متوجه میشویم که این ها مشکلات نیستند بلکه نکاتی را در این خصوص رعایت نکرده ایم. تابع EOMONTH هم یکی دیگر از این مواردی است که باید در استفاده از آن دقت کنیم. در این مقاله سعی بر آن شده است که به بررسی تابع EOMONTH و نحوه استفاده از آن در SQL Server بپردازیم و در آخر یک سناریو کاربردی را مطرح کنیم.
اگر بخواهیم این تابع را بصورت ساده تعریف کنیم میتوان گفت: به ازای یک تاریخ خاص , آخرین روز ماه را بر میگرداند. به syntax زیر توجه فرمایید.
EOMONTH(start_date [, offset] );
تابع EOMONTH دو ورودی میگیرد:
فرض کنید میخواهیم آخرین روز از ماه را در تاریخ 10-02-2020 را بدست آوریم
SELECT EOMONTH('2020-02-10') AS End_Of_Month
با توجه به دستور بالا خروجی بصورت زیر میباشد.
حال فرض کنید این بار شما تصمیم گرفتید آخرین روز ماه را در همان تاریخ ولی در سال 2021 یعنی 10-02-2021 را بدست آورید.
SELECT EOMONTH('2021-02-10') AS End_Of_Month
خروجی دستور بالا بصورت زیر میباشد.
اگر به خروجی دو دستور بالا نگاه کنید متوجه میشوید که دو جواب متفاوت است و این تفاوت فقط با تغییر سال بوجود آمده است. اما چرا؟ اگر به جدول زیر با دقت نگاه کنید متوجه میشوید که تعداد روز ها در ماه های مختلف و یا بعضا در سال ها مختلف متفاوت است به عنوان مثال ژانویه (January) دارای 31 روز می باشد اما فوریه (February) در سال 2020 , 29 روز است ولی در سال 2021 , 28 روز است که به اضای هر 4 سال متفاوت است
فرض کنید یک جدولی به نام Orders وجود دارد این جدول شامل سفارشات هر مشتری - کارمند و تاریخ درج یک سفارش در این جدول میباشد. به ما گفته شده است که آخرین سفارش موجود که در آخرین روز از هر ماه است را بدست آوریم
روش اول استفاده از تابع EOMONTH
همانطور که ذکر شد این تابع آخرین روز از ماه را به ازای تاریخ مربوطه برمیگرداند
SELECT * FROM Orders
WHERE orderdate = EOMONTH(orderdate)
همانطور که میبینید به ازای هر تاریخ آخرین روز را محاسبه میکند. توجه داشته باشد که اگر یک مشتری در یک تاریخ خاص دو عدد سفارش داشته باشد تابع EOMONTH هر دوی آن را بر میگرداند
روش دوم استفاده از Ranking Function است. ما به این دلیل از() DENSE_RANK استفاده کرده ایم که اگر یک مشتری در یک تاریخ خاص دو عدد سفارش داشته باشد هر دوی آن را در خروجی نشان دهد
;WITH CTE_Order AS
(
SELECT *,
DENSE_RANK()OVER(PARTITION BY YEAR(orderdate), MONTH(orderdate)
ORDER BY DAY(orderdate) DESC) AS rw
FROM Orders
)
SELECT
orderid, custid, empid, orderdate, shipcountry
FROM CTE_Order
WHERE rw = 1
خروجی دستور بالا بصورت زیر است
هر دو این دستور آخرین روز از هر ماه را بر میگرداند اما تعداد خروجی ها متفاوت است همانطور که میبینید EOMONTH خروجی 26 رکورد و روش دوم خروجی 42 رکورد دارد .
نکته بسیار مهمی که شما باید به آن توجه ویژه کنید این است که EOMONTH آخرین روز از جدول 1-1 که در بالا توضیح داده شده است را بر میگرداند اگر این روز در Table شما (Orders) باشد در خروجی نشان میدهد اگر نباشد صرف نظر میکند ولی روش دوم آخرین روز از تاریخ موجود که در Table میباشد را بر میگرداند به عبارت دیگر اگر شما در تاریخ 25-01-2021 سفارشی در جدول خود درج کرده باشید تابع EOMONTH آن را بر نمیگرداند چون تابع EOMONTH آخرین روز یعنی 31 را میشناسد و کاری ندارد که شما آخرین روز درج سفارشتان در آن تاریخ 25 است اما روش دوم میگوید من آخرین تاریخی که در جدول سفارشات وجود دارد بر میگردانم به شرطی که بزرگترین باشد. همانطور که میبینید در روش اول خبری از ماه 8 نیست چون آخرین درج سفارشش 30 ام بوده ولی جدول 1-1 برای ماه 8, 31 روز را نشان میدهد پس شامل خروجی نمیباشد ولی روش دوم میگوید بزرگترین روز در ماه 8 , 30 است پس در خروجی می آید.
به عنوان مثال تکمیلی فرض کنید ما یک رکورد در تاریخ 26-09-2013 در جدول Orders درج میکنیم
INSERT INTO Orders(custid,empid,orderdate,shipcountry)
VALUES(10,3,'2013-09-26','Iran')
خروجی روش اول باز هم 26 رکورد می باشد چون آخرین روز ماه 9, 30 روز است ولی آخرین روزی که ما در ماه 9 درج کردیم 26 است پس در خروجی ظاهر نمیشود . خروجی روش دوم این دفعه 43 رکورد میشود چون تاریخ جدید درج شده است .
