en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
נוב28

Written by: ronen ariely
28/11/2014 13:46 RssIcon

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

------------------------------- DDL+DML
CREATE TABLE _jobs
        (
     id int identity primary key,
     startDate datetime,
     endDate datetime
    );
  
INSERT INTO _jobs
(startDate, endDate)
VALUES
('2014-11-11 12:25''2014-11-11 15:40'),
('2014-11-11 19:00''2014-11-12 17:00');
  
  
CREATE TABLE _slots
        (
     id int identity primary key,
     startHour nvarchar(20),
     endHour nvarchar(20),
     price int
    );
  
INSERT INTO _slots
(startHour, endHour,price)
VALUES
('00:00''08:00',1),
('08:00''16:00',2),
('16:00''00:00',3);

פתרון לדוגמה

-- split dates into days usiong numbers table
;with MCTE as (
    select J.id,J.startDate SD,J.endDate ED, A.d
    from _jobs J
    left JOIN _ArielyAccessoriesDatabase.dbo.ArielyDates A
        on convert(DATE,J.startDate) <= A.d and convert(DATE,J.endDate) >= A.d
)
,MCTE2 as (
    select
        C.id
        ,FixedStartDate = case
            when SD > d  then SD
            else d
        end
        , FixedEndtDate = case
            when ED < DATEADD(day, 1, d)  then ED
            else DATEADD(DAY, 1, d)
        end
        , d
    from MCTE C
)
, MCTE3 as (
    select m.id, FixedStartDate, FixedEndtDate, startHour + d as S_Shift, case when endHour = '00:00' then endHour + DATEADD(DAY,1,d) else  endHour + d end as E_Shift, price
    from MCTE2 m
    cross join _slots s
)
select
    id
    ,S_Date = case
        when FixedStartDate < S_Shift then S_Shift
        else FixedStartDate
    end
    ,E_Date = case
        when FixedEndtDate > E_Shift then E_Shift
        else FixedEndtDate
    end
from MCTE3
where not FixedStartDate > E_Shift and not FixedEndtDate < S_Shift
-- We could work with LEFT JOIIN instead of CROSS JOIN and insert the condition in the "on" section instead in teh "where" like here

.

.

.