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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Gropuing by Time Durations

Author  Topic 

coagulance
Yak Posting Veteran

78 Posts
Posted - 2008-05-03 : 16:52:50
I have the below data:
ProdDate RntValue RNT
2008.05.02 07:01:00 25 Test67
2008.05.02 08:15:00 29 Test56
2008.05.02 10:21:00 45 Test17
2008.05.02 12:16:00 28 Test98
2008.05.02 14:31:00 46 Test72
2008.05.02 15:31:00 75 Test78
2008.05.02 18:35:00 95 Test56
2008.05.02 19:07:00 12 Test23
2008.05.02 21:13:00 83 Test13
2008.05.02 23:31:00 34 Test22

I would like to Group this in :
07:00:00 to 10:00:00
10:00:00 to 15:00:00
15:00:00 to 19:00:00
19:00:00 to 23:59:59

and Also I would like to have the Min and Max ProdDate for each Interval listed for all the result sets belonging to that grou

Would be thankful for any suggestion

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2008-05-03 : 17:45:56
Can you give an example of the desired output?




An infinite universe is the ultimate cartesian product.
Go to Top of Page

coagulance
Yak Posting Veteran

78 Posts
Posted - 2008-05-03 : 18:07:56
This is how it is done now , I am sure there should be a better way

[Code]
DECLARE
@iLoadingTime int = 20,
@vcEndTime11 varchar(10) = '11:00:00',
@vcStartTime11 varchar(10)= '07:00:00',
@vcEndTime12 varchar(10) = '15:00:00',
@vcStartTime12 varchar(10)= '11:30:00',
@vcEndTime21 varchar(10) = '19:00:00',
@vcStartTime21 varchar(10)= '15:00:00',
@vcEndTime22 varchar(10) = '23:00:00',
@vcStartTime22 varchar(10)= '19:30:00'

BEGIN

SELECT
'Shift 1 - Pre Lunch' as 'Session'
,COUNT ([RNTID]) as 'Parts'
,CONVERT(VARCHAR(10),MIN([CompletionTime]),108) as'First Part'
,CONVERT(VARCHAR(10),MAX([CompletionTime]),108) as 'Last Part'
,convert(varchar(20),dateadd(ss,SUM([SetupLoss]) ,0),108) as 'SetUpLoss'
,convert(varchar(20),dateadd(ss,SUM([RNTDuration]) ,0),108) as 'Hours Run'
,convert(varchar(20),dateadd(ss,COUNT ([RNTID]) * @iLoadingTime ,0),108) as 'LoadingTime'
,COUNT(DISTINCT [ProgramNo]) as 'No Of Setups'
,COUNT(DISTINCT [SubAssemblyNo]) as 'Special Parts'
,(SUM([RNTDuration]) + (COUNT ([RNTID]) * @iLoadingTime))/(CAST(DATEDIFF(ss,@vcStartTime11,@vcEndTime11) as real))
FROM [RPMS].[dbo].[List_MachineShopRNT]
WHERE ProductionDay = '2008.05.02' and Machine = 202 and ( CONVERT(varchar(10),[CompletionTime],108) BETWEEN @vcStartTime11 AND @vcEndTime11)

union all
SELECT
'Shift 1 - Post Lunch' as 'Session'
,COUNT ([RNTID]) as 'Parts'
,CONVERT(VARCHAR(10),MIN([CompletionTime]),108) as'First Part'
,CONVERT(VARCHAR(10),MAX([CompletionTime]),108) as 'Last Part'
,convert(varchar(20),dateadd(ss,SUM([SetupLoss]) ,0),108) as 'SetUpLoss'
,convert(varchar(20),dateadd(ss,SUM([RNTDuration]) ,0),108) as 'Hours Run'
,convert(varchar(20),dateadd(ss,COUNT ([RNTID]) * @iLoadingTime ,0),108) as 'LoadingTime'
,COUNT(DISTINCT [ProgramNo]) as 'No Of Setups'
,COUNT(DISTINCT [SubAssemblyNo]) as 'Special Parts'
,(SUM([RNTDuration]) + (COUNT ([RNTID]) * @iLoadingTime))/(CAST(DATEDIFF(ss,@vcStartTime12,@vcEndTime12) as real))
FROM [RPMS].[dbo].[List_MachineShopRNT]
WHERE ProductionDay = '2008.05.02' and Machine = 202 and ( CONVERT(varchar(10),[CompletionTime],108) BETWEEN @vcStartTime12 AND @vcEndTime12)

union all
SELECT
'Shift 2 - Pre Lunch' as 'Session'
,COUNT ([RNTID]) as 'Parts'
,CONVERT(VARCHAR(10),MIN([CompletionTime]),108) as'First Part'
,CONVERT(VARCHAR(10),MAX([CompletionTime]),108) as 'Last Part'
,convert(varchar(20),dateadd(ss,SUM([SetupLoss]) ,0),108) as 'SetUpLoss'
,convert(varchar(20),dateadd(ss,SUM([RNTDuration]) ,0),108) as 'Hours Run'
,convert(varchar(20),dateadd(ss,COUNT ([RNTID]) * @iLoadingTime ,0),108) as 'LoadingTime'
,COUNT(DISTINCT [ProgramNo]) as 'No Of Setups'
,COUNT(DISTINCT [SubAssemblyNo]) as 'Special Parts'
,(SUM([RNTDuration]) + (COUNT ([RNTID]) * @iLoadingTime))/(CAST(DATEDIFF(ss,@vcStartTime21,@vcEndTime21) as real))
FROM [RPMS].[dbo].[List_MachineShopRNT]
WHERE ProductionDay = '2008.05.02' and Machine = 202 and ( CONVERT(varchar(10),[CompletionTime],108) BETWEEN @vcStartTime21 AND @vcEndTime21)

union all
SELECT
'Shift 2 - Post Lunch' as 'Session'
,COUNT ([RNTID]) as 'Parts'
,CONVERT(VARCHAR(10),MIN([CompletionTime]),108) as'First Part'
,CONVERT(VARCHAR(10),MAX([CompletionTime]),108) as 'Last Part'
,convert(varchar(20),dateadd(ss,SUM([SetupLoss]) ,0),108) as 'SetUpLoss'
,convert(varchar(20),dateadd(ss,SUM([RNTDuration]) ,0),108) as 'Hours Run'
,convert(varchar(20),dateadd(ss,COUNT ([RNTID]) * @iLoadingTime ,0),108) as 'LoadingTime'
,COUNT(DISTINCT [ProgramNo]) as 'No Of Setups'
,COUNT(DISTINCT [SubAssemblyNo]) as 'Special Parts'
,(SUM([RNTDuration]) + (COUNT ([RNTID]) * @iLoadingTime))/(CAST(DATEDIFF(ss,@vcStartTime22,@vcEndTime22) as real))
FROM [RPMS].[dbo].[List_MachineShopRNT]
WHERE ProductionDay = '2008.05.02' and Machine = 202 and ( CONVERT(varchar(10),[CompletionTime],108) BETWEEN @vcStartTime22 AND @vcEndTime22)

union all
SELECT
'Overtime' as 'Session'
,COUNT ([RNTID]) as 'Parts'
,CONVERT(VARCHAR(10),MIN([CompletionTime]),108) as'First Part'
,CONVERT(VARCHAR(10),MAX([CompletionTime]),108) as 'Last Part'
,convert(varchar(20),dateadd(ss,SUM([SetupLoss]) ,0),108) as 'SetUpLoss'
,convert(varchar(20),dateadd(ss,SUM([RNTDuration]) ,0),108) as 'Hours Run'
,convert(varchar(20),dateadd(ss,COUNT ([RNTID]) * @iLoadingTime ,0),108) as 'LoadingTime'
,COUNT(DISTINCT [ProgramNo]) as 'No Of Setups'
,COUNT(DISTINCT [SubAssemblyNo]) as 'Special Parts'
,(SUM([RNTDuration]) + (COUNT ([RNTID]) * @iLoadingTime))/(CAST(DATEDIFF(ss,@vcEndTime22,'06:59:00') as real))
FROM [RPMS].[dbo].[List_MachineShopRNT]
WHERE ProductionDay = '2008.05.02' and Machine = 202 and ( CONVERT(varchar(10),[CompletionTime],108) BETWEEN @vcEndTime22 AND '06:59:00')
END
/[Code]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-05-05 : 15:12:49
This might help you get to a solution 
DECLARE @Interval TABLE (ID TINYINT, StartHour TINYINT, EndHour TINYINT)

INSERT @Interval
SELECT 1, 7, 10
UNION ALL SELECT 2, 10, 15
UNION ALL SELECT 3, 15, 19
UNION ALL SELECT 4, 19, 24

DECLARE @Yak TABLE (ProdDate DATETIME, RntValue INT, RNT VARCHAR(20))

INSERT @Yak
SELECT '2008.05.02 07:01:00', 25, 'Test67'
UNION ALL SELECT '2008.05.02 08:15:00', 29, 'Test56'
UNION ALL SELECT '2008.05.02 10:21:00', 45, 'Test17'
UNION ALL SELECT '2008.05.02 12:16:00', 28, 'Test98'
UNION ALL SELECT '2008.05.02 14:31:00', 46, 'Test72'
UNION ALL SELECT '2008.05.02 15:31:00', 75, 'Test78'
UNION ALL SELECT '2008.05.02 18:35:00', 95, 'Test56'
UNION ALL SELECT '2008.05.02 19:07:00', 12, 'Test23'
UNION ALL SELECT '2008.05.02 21:13:00', 83, 'Test13'
UNION ALL SELECT '2008.05.02 23:31:00', 34, 'Test22'


SELECT
	MIN(Yak.ProdDate) AS MinDate,
	MAX(Yak.ProdDate) AS MaxDate,
	SUM(RntValue) AS SumRntValue
FROM 
	@Interval AS I
INNER JOIN
	@Yak AS Yak
	ON DATEPART(HOUR, Yak.ProdDate) >= I.StartHour
	AND DATEPART(HOUR, Yak.ProdDate) < I.EndHour
GROUP BY
	I.ID
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site