שאלה בSQL

[ofir4u]

שאלה בSQL

היי

הייתי בראיון עבודה ואחת השאלות שניתנה היתה כזו:
נתונות 2 טבלאות:

טבלת חברות comp:

Name custno
300 חברה א


טבלת שעות עבודה timecol
NOTES DATEEND DATESATART Custno
300 ‏01/02/2007 17:46:00 01/02/2007 17:16:00 לוגיקה ייחודית
‏
ומבקשים להציג לכל חברה את סך השעות בכל החודשים בשנת 2007 בטבלה אחת שכוללת עמודת חודשים, זמן כולל, שם חברה ומספר חברה.

אשמח לקבל הכוונה לביצוע השאילתא.

 

[YoavLevin]

הכוונה בפסדו קוד

1. מטבלת שעות לשלוח LEFT JOIN לטבלת חברות - קישור ע"י שדה מפתח.
2. שדות הטבלה : שם חברה (מספר חברה היינו הך) - יגיע מטבלת חברות , שם חודש - יחולץ ע"י פקודת MONTH על שדה התאריך , זמן כולל - יחושב ע"י SUM על ערכי שעות.
3. פילטור על שנת 2007 ב WHERE CLUASE בעזרת פקודת YEAR על שדה התאריך .
4. הקבצה על שדות שם חברה ושם חודש.

 

[dror0548]

משהו כזה

Select
tblcust.custno
,tblcust.custname
,Isnull(A.month1,0) as month1
,Isnull(A.Sumhour,0) as Sumhour
from tblcust
Left Join
(
Select custno,
month(DATESATART) as month1,
Sum(DATEDIFF(hour,DATESATART,
(Case When YEAR(DATEEND) <> 2007
then '2007-12-31 00:00:00.000'
else DATEEND
end)
)
) as Sumhour
from tblcustdate
Where YEAR(DATESATART) = 2007
group by custno,month(DATESATART)
) A on A.custno = tblcust.custno

 

[ofir4u]

תודה על התשובה אבל..

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

אני אבהיר, אמורה להיות עמודה של חודשים: 1 , 2, 3, 4, וכו'...

כמו כן לא ברור לי מה ה-case אמור לעשות שם ואיך הוא מחשב סכום שעות לחודש אחד בכל פעם..

 

[pitoach]

מה הסיכוי שתעזור לנו לעזור לך ותצרף לנו DDL

+DML ?
בלי זה אין לי כוח בדרך כלל להתעמק אפילו בבעיה

 

[YoavLevin]

אני הבנתי

זה מצריך בניה יותר מורכבת של שאילתא לכן עדיף לשלוח את השאלה המקורית כי אולי רק נדמה לי שהבנתי..

 

[ofir4u]

אין הרבה מה להרחיב...

השאלה המקורית מופיעה בהודעה הראשונה שלי רק ששם יש דוגמא של הטבלה שמבקשים.

 

[dror0548]

שאלה לא קלה

 

[dror0548]

עברית הפוכה

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

שאלה לא פשוטה !!!
צירפתי את הטבלאות מעניין לראות את הפתרון

 

[pitoach]

זו פעולה פשוטה של שימוש ב PIVOT למשל

עתה כשרואים על מה מדובר אז אפשר להציג גם קוד אבל מדובר בעקרון בפעולה עם PIVOT

פשוט מבצעים LEFT JOIN כדי לקבל עבור כל חברה את כל הנתונים של שעות העבודה (כמובן מבצעים SUM על החישוב של שעות = הפרש הזמנים מהסיום להתחלה) ואז נשאר רק לכתוב הכל תחת PIVOT כדי להציג את התוצאה בצורה של נתון לפי חודש

* בהנחה שהבנתי מה רוצים בשאלה

אם עדיין יש בעיה לממש את מה שכתבתי מעל אני אצרף קוד

 

[pitoach]

שכחתי לציין שיש לבדוק כמובן אם זמן העבודה

משוייך לחודש מסויים (אם ההתחלה והסיום באותו חודש אז אין מה לבצע) או למשל משוייך ל2 חודשים ואז יש לפרק תחילה את הרשומה לפי הזמן של כל חודש

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

 

[dror0548]

הערה :

היי
לא אני שאלתי את השאלה,פשוט העלתי את הקוד כדי שיוכלו כדי לפתור
דבר שני :
מקווה שאני לא טועה אבל נראה לי שהשאלה קצת יותר מורכבת מהפתרון שהצעת
כי אמורים לחשב את מספר השעות לכל חודש
למשל 26.1 עד 10.2
למשל אם זה 5 ימים בחודש 1 וגולש לעוד 10 ימים בחודש השני
אז צריך בהתאמה לחשב שעות עבור כל חודש בנפרד

יהיה נחמד לראות פתרון בקוד לשאלה הזו

תודה

 

[pitoach]

אני אעלה קוד מוכן היום אם כן

* העניין של חישוב עבור כל חודש בנפרד מופיע אצלי ב"שכחתי לציין" דרך אגב
אבל תן לי כמה דקות לכתוב את הפתרון ועוד איזה 30 דקות לכתוב אותו בצורה קריאה בבלוג ואני אעלה אותו

 

[pitoach]

בבקשה --> קוד מלא מוכן עם הסבר

http://ariely.info/Blog/tabid/83/En...tting-the-entries-time-records-per-month.aspx

 

[pitoach]

אופיר אתה עדיין צריך עזרה?

 

[ofir4u]

לדעתי, הבאתם את השאלה למקומות שלא חשבו אליהם

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

יש לי איזה שהוא פתרון שחבר נתן לי (ללא התייחסות לחודשים ללא פעילות) אבל אני מתקשה להבין את ההגיון מאחוריו:
select p.name,t.custno,t.month,t.[SUM hours] from comp p inner join
(select tt.custno,month(tt.datestart) as [month],sum(DATEDIFF(minute,tt.datestart,tt.dateend)/60.0) as [SUM hours] from [timecol] tt
where year(tt.datestart)=2007
group by tt.custno,month(tt.datestart)
) t on p.custno=t.custno

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

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

 

[pitoach]

פתרון כן..אבל נכון? לא כל כך

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

שים לב מה קורה בחלק הבא בשאילתה:
DATEDIFF(minute,tt.datestart,tt.dateend)/60.0)
אבל מה קורה אם תאירך הסיום הוא בחודש שאחרי תאריך ההתחלה?!?
בשאילתה הזו תקבל את השעות כולן כאילו היו בחודש הראשון במקום
למשל אם ברשומה יש התחלה ב 30.04.12 וסיום ב 2.05.12 אז תקבל כאילו 3 ימים בוצעו בחודש 04 וזה לא נכון! הרשומה צריכה להוסיף יום אחד לחודש 4 ועוד יומיים לחודש 5

* פרט לזה שאין פירוק של מצב כזה (וזה התפקיד של הפונקציה) הפתרון מאוד דומה לשאילתה שאני הצגתי
** כשאתה רואה שאילתה שנראית לך מורכבת כמו אולי המקרה הנוכחי ואתה רוצה להבין אותה אז תתחיל מלפרק אותה לרכיבים שלה. למשל יש כאן שאילתה פנימית. נסה להריץ את השאילתה הפנימית ולהבין אותה ואז תראה שהסיפור לא גדול בכלל וזה JOIN פשוט
*** נקודה נוספת: בדוגמה שקיבלת מהחבר יש שימוש ב INNER JOIN במקום LEFT JOIN וזה אומר שכמו שכתבת אתה לא מטפל בלקוחות בלי פעילות וחבל. הם פשוט נעלמים לך מהתוצאה ואת המאבד אותם. את תעשה שמוש ב LEFT תקבל לפחות רשומה אחת עבור כל לקוח וככה לא תאבד אותו לצורך המשך ניתוח התוצאה (למשל כדי להציג אפס עבור לקוחות בלי שעות)
**** הסיבה שלא מוצגים כפילויות זה בגלל שנעשה GROUP BY ולכן יש רק רשומה אחת עבור כל לקוח עבור כל חודש (מוסבר בקישור שלי מעל גם כן)

 

[dror0548]

pitoach באמת השקעת

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

 

[pitoach]

כתבתי שיש פתרון גם ללא לולאה והסברתי אותו

באופן כללי דיי לעומק רק בלי קוד סופי.

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

* תודה