SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Code Help for Looping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sisterwolf
Starting Member

4 Posts

Posted - 01/08/2013 :  10:33:11  Show Profile  Reply with Quote
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

Edited by - sisterwolf on 01/08/2013 10:37:33

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/08/2013 :  14:44:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/08/2013 :  23:33:21  Show Profile  Reply with Quote
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/

Go to Top of Page

sisterwolf
Starting Member

4 Posts

Posted - 01/09/2013 :  13:53:12  Show Profile  Reply with Quote
Thanks sodeep. Your code worked.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/09/2013 :  15:41:55  Show Profile  Reply with Quote
Welcome
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 01/11/2013 :  07:28:48  Show Profile  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000