Need Code Help for Looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Code Help for Looping

Author

Topic

sisterwolf
Starting Member

4 Posts

Posted - 2013-01-08 : 10:33:11

I want to create a derived Start Date Time for Time Logs based on the Report Start Date Time and the Duration of the Ops Codes.

Logic:


SELECT 
  RptID, 
  TimeCode,
  HrsDur,
  CASE WHEN LogSeq# = 1 THEN RptStartDttm
       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 LOOP
  END AS OpsStartDttm
FROM 
  DailyOps, 
  TimeLog
WHERE 
  DailyOps.RptID = TimeLog.RptID

Tables:
DailyOps
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

Query Result:
RptID___TimeCode__HrsDur__OpsStartDttm (derived field)
R1. . . . .MIRU. . . . .9. . . . . .01/13/2013 06:00
R1. . . . .BHA. . . . . 1. . . . . .01/13/2013 15:00
R1. . . . .DR. . . . . .4. . . . . .01/13/2013 16:00
R1. . . . .SVY. . . . . 2. . . . . .01/13/2013 20:00

R2. . . . .BHA. . . . . 5. . . . . .01/14/2013 06:00
R2. . . . .DR. . . . . .6. . . . . .01/14/2013 11:00
R2. . . . .RR. . . . . .7. . . . . .01/14/2013 17:00

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-08 : 14:44:38

[code]Declare @DailyOps Table
(RptId Varchar(10),RptStartDttm datetime,RptEndDttm datetime)
Insert into @DailyOps
Select 'R1','01/13/2013 06:00:00:000','01/14/2013 06:00:00:000' union all
Select 'R2','01/14/2013 06:00:00:000','01/15/2013 06:00:00:000'

Declare @TimeLog Table
(LogId Varchar(10),RptID Varchar(10),HrsDue int,TimeCode Varchar(10),LogSeg int)

Insert into @TimeLog
Select 'L1A','R1',9,'MIRU',1 union all
Select 'L2B','R1',1,'BHA',2 union all
Select 'L3C','R1',4,'DR',3 union all
Select 'L4D','R1',2,'SVY',4 union all
Select 'L1AF','R2',5,'BHA',1 union all
Select 'L21G','R2',6,'DR',2 union all
Select 'L31D','R2',7,'RR',3

Select D.RptID,T.TimeCode,T.HrsDue,T.LogSeg,ISNULL(DateAdd(hh,P.HrsDue,D.RptStartDttm),D.RptStartDttm)RptStartDttm
from @DailyOps D
inner join @TimeLog T on T.RptID = D.RptID
OUTER APPLY
(
Select K.RptID,SUM(K.HrsDue)HrsDue from @TimeLog K
left join @TimeLog S on S.RptID = K.RptID and S.LogSeg = K.LogSeg + 1
Where K.RptID = T.RptID
and S.LogSeg < = T.LogSeg
Group by K.RptID
)P

RptID TimeCode HrsDue LogSeg RptStartDttm
R1 MIRU 9 1 2013-01-13 06:00:00.000
R1 BHA 1 2 2013-01-13 15:00:00.000
R1 DR 4 3 2013-01-13 16:00:00.000
R1 SVY 2 4 2013-01-13 20:00:00.000
R2 BHA 5 1 2013-01-14 06:00:00.000
R2 DR 6 2 2013-01-14 11:00:00.000
R2 RR 7 3 2013-01-14 17:00:00.000[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 23:33:21

posted data indicates you're not storing date values in datetime fields. Please make sure you useappropriate datatypes for fields. Storing dates as varchar makes date manipulations like date addition complicated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sisterwolf
Starting Member

4 Posts

Posted - 2013-01-09 : 13:53:12

Thanks sodeep. Your code worked.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-09 : 15:41:55

Welcome

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2013-01-11 : 07:28:48

You can also accomplish the requirement as follows using some running total logic :


Create table DailyOps 
(
	RptID Varchar(2), 
	RptStartDttm DateTime, 
	RptEndDttm DateTime 
) 

Insert Into DailyOps 
Select 'R1', '2013/01/13 06:00', '2013/01/14 06:00'
Union ALL 
Select 'R2', '2013/01/14 06:00', '2013/01/15 06:00' 

Create Table TimeLog 
(
	LogID Varchar(5), 
	RptID Varchar(2), 
	HrsDur Int, 
	TimeCode Varchar(5), 
	LogSeqNo Int 
) 

Insert Into TimeLog 
Select 'L1A', 'R1', 9, 'MIRU', 1 
Union ALL	
Select 'L2B', 'R1', 1, 'BHA', 2	
Union ALL
Select 'L3C', 'R1', 4, 'DR', 3	
Union ALL
Select 'L4D', 'R1', 2, 'SVY', 4	
Union ALL
Select 'L1AF', 'R2', 5, 'BHA', 1	
Union ALL
Select 'L21G', 'R2', 6, 'DR', 2	
Union ALL
Select 'L31D', 'R2', 7, 'RR', 3 


;With CTE 
As 
(
	 Select a.*, p.LogID, p.HrsDur, p.TimeCode, p.LogSeqNo, p.hrsdur_new, ROW_NUMBER() Over (Partition By a.RptId Order By p.LogSeqNo) As rn
	 From DailyOps As a 
	 JOIN 
	 (
		  Select *, (Select SUM(HrsDur) From TimeLog Where RptID = a.RptID AND LogSeqNo <= a.LogSeqNo) As hrsdur_new 
		  From TimeLog As a 
	 ) As p ON a.RptID = p.RptID 
) 
Select a.RptID, a.TimeCode, a.HrsDur, (Case When b.rn IS NULL Then a.RptStartDttm Else DATEADD(HH,b.hrsdur_new,b.RptStartDttm) End) As OpsStartDttm 
From CTE as a LEFT JOIN CTE As b ON b.rn = (a.rn - 1) And a.RptID = b.RptID

I had a feeling that the Outer Apply might be working better performance wise.....but it seems that my query's performance is better compare to the one with the Outer Apply both in terms of scans and execution time. Here are the results :

 

--Stats of my query--------------------------------

(7 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DailyOps'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TimeLog'. Scan count 9, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
-------------------------------------------------------------

--Stats of the Outer Apply Query-----------------------------

(7 row(s) affected)
Table 'Worktable'. Scan count 14, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TimeLog'. Scan count 15, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DailyOps'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources