יום שני, 11 ביולי 2011

כמה ימי ראשון יש בין שני תאריכים?

שלום רב,




שאלה:
יש לי מערכת לחישוב שעות עבודה המבוססת כמובן בסיס נתונים מסוג sql server :) .
בחברה שלנו מוגדר כי יום ראשון בשבוע הינו חצי יום עבודה ולכן על מנת לחשב שעות נוספות ליום עבודה ספציפי זה , אני צריך לחשב כמה ימי ראשון יש לי בין שני תאריכים.
כיצד ניתן לחשב ב - sql server כמה ימי ראשון לדוגמא יש לי בין שני תאריכים?




תשובה:
על מנת לחשב כמה ימי ראשון, ובכלל כמה "שמות" ימים יש לי בין שני תאריכים אני אקרא בהתחלה לפונקציות dateadd ו- datename על מנת לקבל את מספר היום -
datename(weekday,dateadd(day,number,@date1).
לאחר מכן, אני אעזר בטבלת המערכת master..spt_values אשר מכילה כ- 2500 פרמטרים על מנת לפענח את שם היום.




הפתרון הינו:




declare @date1 datetime, @date2 datetime
select
@date1='2011-07-01', @date2='2011-07-11'
select
sum(case when datename(weekday,dateadd(day,number,@date1))='sunday' then 1 else 0 end)
as sundays,
sum(case when datename(weekday,dateadd(day,number,@date1))='Monday' then 1 else 0 end)
as Monday,
sum(case when datename(weekday,dateadd(day,number,@date1))='Tuesday' then 1 else 0 end)
as Tuesday,
sum(case when datename(weekday,dateadd(day,number,@date1))='Wednesday' then 1 else 0 end)
as Wednesday,
sum(case when datename(weekday,dateadd(day,number,@date1))='Thursday' then 1 else 0 end)
as Thursday,
sum(case when datename(weekday,dateadd(day,number,@date1))='Friday' then 1 else 0 end)
as Friday,
sum(case when datename(weekday,dateadd(day,number,@date1))='Saturday' then 1 else 0 end)
as Saturday
from master..spt_values
where type='p' and dateadd(day,number,@date1)<=@date2



כמובן שאפשר עוד לייעל את השליפה. ובמידה ויש לכם רעיונות נוספים לפתרון הבעיה אשמח לשמוע!!
בהצלחה!



3 תגובות:

ronen אמר/ה...

איתי הבדיקה שלך מתבססת למעשה על מעבר על כל הימים בין 2 התאריכים שהוכנסו (בסך הכל עושים שימוש בטבלה של מספרים על מנת לאפשר dateadd כל פעם ליום הבא)

הרעיון יפה

נקודה קטנה: נכון ששאילתות בחירה הן הבסיס של SQL והן עובדות מהר מאוד אבל עדיין מאחורי הקלעים יש מעבר על כל הנתונים יום אחרי יום (גם אם מנוהל על ידי השרת ונעשה בצורה מקבילית)

רעיון:
הרעיון הבא בתכנות ושימוש בלולאות בוודאות ייתן שיפור ניכר של סדרי גודל אבל ב SQL אני לא יודע כמה השיפור יראה. עדיין לא בדקתי אבל אני צופה שככל שתקופת הזמן תהיה גדולה יותר השיפור יראה ב צורה ברורה יותר.

הנחת יסוד: אם נתייחס לנתונים כנתונים של ימים רצופים אז לא יכול להיות יום חסר באמצע החישוב אלא רק בהתחלה או בסיום. ז"א לא יכול להיות שיום ראשון יחסר אם היה יום שני אחריו ויום שבת לפניו

הבסיס לרעיון: נספור שבועות במקום ימים.

לכן מספיק לבדוק את ההשלמה לשבוע בהתחלה ובסיום ואז אתה יודע כמה ימים היו בלי לעבור על כל הנתונים. את מספר השבועות ניתן לקבל מיידית מחישוב בודד של חיסור תאריכים.

למשל אם מתחילים ביום שני אז אנחנו יודעים כבר שחסר לנו יום ראשון אחד במספור. אם מסיימים ביום חמישי אז אנחנו יודעים שחסר לנו יום שישי + שבת במיספור (לנוחות תמיד אפשר להוסיף את הימים שהיו חסרים כדי לקבל שבועות שלמים תמיד)

עתה בלי שום לולאה ושום מעבר על הנתונים נשאר רק לבדוק כמה שבועות היו בין התאריכים שלנו ויש לנו מייד את התשובה הסופית:
יום ראשון היה כמספר השבועות פחות 1 (היה חסר בהתחלה)
יום שני עד חמישי היה כמספר השבועות
יום שישי + שבת היה כמספר השבועות פחות אחד (היה חסר בסיום)

כאמור ככל שתקופת הזמן תהיה גדולה יותר אני צופה עדיפות גבוהה יותר לשיטה זו.

גרי רשף אמר/ה...

תרומתי הצנועה (חישוב מספר ימי ראשון ביולי 2011):
Declare @FromDate DateTime='20110701',
@ToDate DateTime='20110731';
Select (Cast(@ToDate As Int)+1)/7-(Cast(@FromDate As Int)+0)/7 [Saturdays];

פיני קרישר אמר/ה...

אני אוהב להשתמש בפונקציה
DATEPART

DECLARE @DateStart DATETIME='20110701', @i INT=0
WHILE @DateStart <'20110801'
BEGIN
IF DATEPART(dw,@DateStart)=1
BEGIN
SET @i=@i+1
END
SET @DateStart=DATEADD(d,1,@DateStart)
END
SELECT @i