CODEWITHSUNDEEP
sqlwhile-loophana
hana_wannabe
hana_wannabe

Reputation: 45

HANA While Loop

I have a requirement and I think a possible solution, but I wanted to validate it first to ensure its the best approach.

There is currently a view that needs 2 new fields added. The first field will be spread in such a way that no day has more than 24 in it. The allocation will begin on the start date and continue each calendar day until the hours required are fully consumed. A new date entry will be made for each day the machine is in use.

So for example a machine requires 80 hours to finish a part. The new output will create 4 identical records except for the new date and hours required fields, as shown below.

Date Hours required
June 1 24
June 2 24
June 3 24
June 4 8

My thoughts were to create a table function and use a while loop to continuously check the current field that has the required hours and check if its greater than 0 and subtract 24 until it was less than 24 in which that would be the final entry. It would also increment the start date by 1 day each time it went through the loop. I would then put the table function in a graphical view and then the front end could consume it.

Any feedback would be greatly appreciated!

Upvotes: 2

Views: 1542

Answers (1)

Suchitra
Suchitra

Reputation: 101

suppose your table has below structure :

CREATE COLUMN TABLE "MachineCapacityHrs"( 
"Machine"  nvarchar(10),
"TotalHoursRequired" int,
"StartDate"   date
)

then you can generate date and hours required using below statement in ur table function without using loops :

select e."Machine",e."TotalHoursRequired",e."StartDate", 
t.date_sql as "DateSequence" ,
case when e."EndDate"<>t.date_sql then 24 else "Hrs" end as "HrsRequired"
from
(   select "Machine","TotalHoursRequired","StartDate",
    add_days("StartDate",case when floor("TotalHoursRequired"/24)=0 then 0
            else floor("TotalHoursRequired"/24) end
        ) as "EndDate",
    floor("TotalHoursRequired"/24) as "Days", 
    mod("TotalHoursRequired",24) as "Hrs",
    case when mod("TotalHoursRequired",24) >0 then floor("TotalHoursRequired"/24)+1 else floor("TotalHoursRequired"/24) end as "DateCount"
    from "MachineCapacityHrs" 
)e left outer join ( select date_sql from _sys_bi.m_time_dimension
)t on  t.date_sql  between e."StartDate" and e."EndDate"

result: enter image description here

Upvotes: 3

Related Questions