How to code a LOOP?

[#1]

I’m looking for help with coding a LOOP which I’ve never done before.  Do I do this in the SELECT statement or the LOAD statement?  Basically, what I want to do is create a StartDttm for each log entry associated with a Job Report.  As is the timelog table only stores the number of hours that a particular entry took.  Their dates would be derived from the Job Report StartDttm with the first entry's StartDttm equaling the Job's StartDttm.  The remaining Log Entries StartDttms would be derived by adding the HrsDur of the previous ones to the Job Report StartDttm or possibly from the previous derived StartDttm.

Logic:

SELECT

   RptID,

   RptStartDttm,

   RptEndDttm,

   LogID,

   TimeCode,

   LogSeq#,

   HrsDur,

   CASE WHEN LogSeq# = 1 THEN RptStartDttm               // -- from here down is just my thoughts on the logic

                 ELSE

                           DO WHILE  …… ??? ……

                              LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

                              LogSeq# = 3 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur

                              LogSeq# = 4 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur + LogSeq#3 HrsDur

                             etc.  until last LogSeq# for RptID

                  --- (OR) ---

                             LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

                             LogSeq# = 3 THEN LogSeq#2 OpsStartDttm + LogSeq#2 HrsDur

                             LogSeq# = 4 THEN LogSeq#3 OpsStartDttm + LogSeq#3 HrsDur

                            etc. until last LogSeq# for RptID

   END AS OpsStartDttm

FROM

   JobRpt,

   TimeLog

WHERE

   JobRpt.RptID = TimeLog.RptID

SQL Tables:

JobRpt

RptID       RptStartDttm                     RptEndDttm

R1           01/13/2013 06:00                01/14/2013 06:00

R2           01/14/2013 06:00                01/15/2013 06:00

TimeLog

LogID     RptID       HrsDur           TimeCode           LogSeq#

L1A           R1            9                MIRU               1             

L2B        R1          1                BHA                 2             

L3C        R1          4                DR                   3             

L4D        R1          2                SVY                 4             

L1AF      R2          5                BHA                 1             

L21G      R2          6                DR                    2             

L31D      R2          7                RR                    3             

QlikView Table:

Operations

RptID       RptStartDttm             RptEndDttm              LogID     TimeCode    LogSeq#       HrsDur       OpsStartDttm (derived field)     

R1            01/13/2013 06:00        01/14/2013 06:00       L1A         MIRU            1                   9                01/13/2013 06:00

R1            01/13/2013 06:00        01/14/2013 06:00       L2B         BHA              2                  1                01/13/2013 15:00

R1            01/13/2013 06:00        01/14/2013 06:00       L3C         DR                3                  4                01/13/2013 16:00

R1            01/13/2013 06:00        01/14/2013 06:00       L4D         SVY              4                  2                01/13/2013 20:00

R2            01/14/2013 06:00        01/15/2013 06:00        L1AF      BHA              1                  5                01/14/2013 06:00

R2            01/14/2013 06:00        01/15/2013 06:00        L21G      DR                2                  6                01/14/2013 11:00

R2            01/14/2013 06:00        01/15/2013 06:00        L31D      RR                3                  7                01/14/2013 17:00

[Gysbert_Wassenaar · Accepted solution]

Something like this:

JobRpt:

load RptID,

timestamp#(RptStartDttm,'MM/DD/YYYY hh:mm') as RptStartDttm,

timestamp#(RptEndDttm,'MM/DD/YYYY hh:mm') as RptEndDttm

inline [

RptID,RptStartDttm,RptEndDttm

R1,01/13/2013 06:00,01/14/2013 06:00

R2,01/14/2013 06:00,01/15/2013 06:00

];

join load * inline [

LogID,RptID,HrsDur,TimeCode,LogSeq#

L1A,R1,9,MIRU,1            

L2B,R1,1,BHA,2            

L3C,R1,4,DR,3            

L4D,R1,2,SVY,4             

L1AF,R2,5,BHA,1            

L21G,R2,6,DR,2            

L31D,R2,7,RR,3          

];

Timelog:

load LogID,RptID,HrsDur,TimeCode,LogSeq#,RptStartDttm,RptEndDttm,

timestamp(if(RptID=Previous(RptID), peek('OpsStartDttm') + peek('HrsDur')/24, RptStartDttm),'MM/DD/YYYY hh:mm') as OpsStartDttm

Resident JobRpt order by RptID,LogSeq#;

drop table JobRpt;

[#3]

Thanks a lot for your help.  I don't really understand how the code works so I'll have to study it more when I've got the time. I like that it's just one line that does the trick. However, I did have to change Timestamp#() to Date() in my code for it to work and display properly.