חישובי מע"מ, שערי חליפין וכו' המשתנים לאורך זמן

שערי החליפין היציגים של מט"ח מתפרסמים על ידי בנק ישראל בכל יום בו נערך מסחר במט"ח, ומהם נגזרים שערי החליפין של המוסדות הפיננסיים (למשל- שער הקנייה ושער המכירה בבנקים ואצל החלפנים). בימים בהם לא מתפרסם שער יציג- משתמשים בשער האחרון שהתפרסם ואשר עודו בתוקף.
שיעור מע"מ מתנהל באופן דומה: אחת לכמה שנים הוא משתנה, ווהשיעור האחרון שהתפרסם הוא בתוקף בתאריך העסקה.
באופן דומה אפשר לחשוב על מחירים ומחירונים שונים שמתשתנים מפעם לפעם, ומה שקובע הוא האחרון שהתפרסם.

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

Use tempdb;

Go

If Object_ID('T_Matbeot') Is Not Null Drop Table T_Matbeot;

Go

Create Table T_Matbeot(Matbea Varchar(50) Primary Key);

Go

Insert Into T_Matbeot Values('Dollar');

Insert Into T_Matbeot Values('Euro');

Go

ניצור טבלת שערי חליפין "דלילה" (אין שער לכל יום..):

If Object_ID('T_Shearim') Is Not Null Drop Table T_Shearim;

Go

Create Table T_Shearim(Taarih DateTime,

                    Matbea Varchar(50),

                    Shaar Decimal(8,5),

                    Constraint PK_T_Shearim Primary Key (Taarih, Matbea),

                    Constraint FK_T_Shearim_Matbea Foreign Key(Matbea) References T_Matbeot(Matbea));

Go

Insert Into T_Shearim Values('20100101','Dollar',3.9);

Insert Into T_Shearim Values('20100215','Dollar',4);

Insert Into T_Shearim Values('20100404','Dollar',3.8);

Insert Into T_Shearim Values('20100101','Euro',5);

Go

ולבסוף- טבלת עסקות בה מצויין התאריך, המחיר במט"ח והמטבע:

Create Table T_Asakot(Taarih DateTime,

                    Mehir Decimal(10,2),

                    Matbea Varchar(50),

                    Constraint FK_T_Asakot_Matbea Foreign Key(Matbea) References T_Matbeot(Matbea));

Go

Insert Into T_Asakot Values('20100101',100,'Dollar');

Insert Into T_Asakot Values('20100201',200,'Dollar');

Insert Into T_Asakot Values('20100301',300,'Dollar');

Insert Into T_Asakot Values('20100401',400,'Dollar');

Insert Into T_Asakot Values('20100501',500,'Dollar');

Insert Into T_Asakot Values('20100101',1000,'Euro');

Insert Into T_Asakot Values('20100601',1000,'Euro');

Go

וכעת נשלוף את העסקות מטבלת העסקות, תוך שאני מתאימים לכל עסקה את השער האחרון שהתפרסם במטבע המתאים.

בדוגמה הראשונה נשתמש ב-Cross Apply מורכב יחסית שלכל עסקה (A) מוצא את שער החליפין (S1) מהתאריך המקסימלי (S2) שלפני תאריך העסקה:

Select    *,

        Mehir*Shaar Shkalim

From    T_Asakot A

Cross Apply (Select    S1.Shaar

            From    T_Shearim S1

            Where    S1.Matbea=A.Matbea

                    And Taarih=(Select    Max(S2.Taarih)

                                From    T_Shearim S2

                                Where    S2.Matbea=S1.Matbea

                                        And S2.Taarih<=A.Taarih)) T;

בדוגמה השניה נהפוך את כל השליפה המורכבת שב-Cross Apply לחלק מה-Select  המקורי מטבלת העסקות, כשהלוגיקה אותה לוגיקה:

Select    *,

        Mehir*(Select    S1.Shaar

        From    T_Shearim S1

            Where    S1.Matbea=A.Matbea

                    And Taarih=(Select    Max(S2.Taarih)

                                From    T_Shearim S2

                                Where    S2.Matbea=S1.Matbea

                                        And S2.Taarih<=A.Taarih)) Shkalim

From    T_Asakot A;

ובדוגמה השלישית ניצור Join של טבלת השערים עם עצמה כך שלכל שער יותאם זה שאחריו (כדי שנוכל לחשב את תאריך הסיום שלו), וכך נקבל שליפה בה לכל שער יש תאריך התחלה ותאריך סיום (תאריך הסיום של שער החליפין האחרון מכל מטבע יהיה בסוף האלף..); ואז ההתאמה לטבלת העסקות תהיה פשוטה כי נצטרך למצוא את השער שתאריך העסקה חל בין ההתחלה והסיום שלו:

With T As

(Select    Row_Number() Over(Partition By Matbea Order By Taarih) Mispar,

        *

From    T_Shearim),

T1 As

(Select    T1.Matbea,

        T1.Taarih MiTaarih,

        IsNull(T2.Taarih-1,'29991231') AdTaarih,

        T1.Shaar

From    T T1

Left Join T T2

        On T1.Matbea=T2.Matbea

        And T1.Mispar+1=T2.Mispar)

Select    *,

        Mehir*Shaar Shkalim

From    T_Asakot A

Inner Join T1

        On A.Matbea=T1.Matbea

        And A.Taarih Between T1.Mitaarih And T1.AdTaarih;

קצת יותר מסורבל משתי השליפות הקודמות – מבחינת כמות הקוד, אבל דומה שהלוגיקה יותר ברורה, והתוצאות בכל מקרה זהות:

מה לגבי היעילות? נעיין ב-Execution Plans:

להפתעתי- השליפה השניה יעילה יותר מהראשונה, למרות שאינה אלא גרסה כמעט זהה שלה. הסיבה להבדל ביעילות הוא שבראשונה המערכת לא "ידעה" שלכל עסקה מתאים שער חליפין אחד והיא ביצעה Nested Loops מיותר, ובשניה זה נחסך (ליתר דיוק- ה-Clustered Index Scan של טבלת השערים ש"עלה" 28% נחסך ובעקבות כך גם ה-Nested Loops עם ה-Table Scan של טבלת העסקות).

מה שלא הפתיע אותי זה שהשליפה השלישית התגלתה כמאוד לא יעילה, אם כי ה-Execution Plan קצת מוזר: ציפיתי שהמערכת תבצע קודם את ה-Join של טבלת השערים עם עצמה כפי שהוגדר ב-CTE, ורק לאחר מכן יתבצע ה-Join בעזרת ה-Between מול טבלת העסקות; והמערכת הצליחה לפרק את ה-CTE, ביצעה קודם את ה-Join של תאריך הפתיחה בין טבלת השערים וטבלת העסקות, ורק לאחר מכן את ה-Join של תאריך הסיום בין טבלת השערים לתוצאה הקודמת.

באמת יפה מאוד (הפירוק של ה-CTE) אבל פעמיים Clustered Index Scan על טבלת השערים ולאחר מכן פעמיים Hash Match לא יעיל – עלו ביוקר.. אם ל-SQL Server היו פונקציות חלון עם Lead ו-Lag כפי שיש למתחרים מ-Oracle אז אולי היה לשיטה הזו סיכוי, וזו הזדמנות טובה להצטרף לקמפיין המבורך של איציק בן-גן לשדרוג פונקציות החלון בגרסה הבאה: יש שם קישורים לאתרים המתאימים בהם יש להצביע ולדרוש את המגיע לנו!