Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i want query for display per hourly production from shift timing .
example: shift timing is 8:00:00 to 20:00:00.i want show 8-9,9-10,10-11 like this.please help me?
production table

recordno startrtc endrtc
'69', '2014-08-18 09:47:52', '2014-08-18 09:47:53',
'70', '2014-08-18 09:47:58', '2014-08-18 09:47:59',
'71', '2014-08-18 09:48:01', '2014-08-18 09:48:02',
'72', '2014-08-18 09:48:04', '2014-08-18 09:48:05',
'73', '2014-08-18 09:48:07', '2014-08-18 09:48:08',
'74', '2014-08-18 09:48:11', '2014-08-18 09:48:12',
'75', '2014-08-18 09:48:19', '2014-08-18 09:48:20',
'76', '2014-08-18 09:48:23', '2014-08-18 09:48:24',
'77', '2014-08-18 09:48:32', '2014-08-18 09:48:33',
'78', '2014-08-18 09:48:44', '2014-08-18 09:48:45',

job table

id jobid jobrtc
1 3 '2014-08-17 09:47:58'
2 6 '2014-08-18 09:47:58'
Posted
Updated 3-Nov-14 17:44pm
v5
Comments
syed shanu 3-Nov-14 23:28pm    
yesterday you ask this same question,No one can answer you with this much information,explain it in detail and provide your table details
Sergey Alexandrovich Kryukov 3-Nov-14 23:33pm    
Not a question, no problem explained. Re-posting is considered as abuse.
—SA
Laiju k 3-Nov-14 23:35pm    
try to give more details

1 solution

Try this,Hope this will help you.

SQL
declare @sDate datetime,
        @eDate datetime;

select  @sDate = '11/4/2014 08:00:00' ,
        @eDate = '11/4/2014 20:00:00';
--select @sDate StartDate,@eDate EndDate
;with cte as
   (
      select @sDate StartDate
      ,datepart(hour, @sDate)  StartTime
      , datepart(hour, dateadd(Hour, 1, @sDate)) as EndTime
      ,convert(varchar(8),datepart(hour, @sDate))  
      + ' - '
      + convert(varchar(8),datepart(hour, dateadd(Hour, 1, @sDate))) as Hours
                
  union all
 
      select  dateadd(Hour, 1, StartDate)
      ,datepart(hour, dateadd(Hour, 1, StartDate))  StartTime
      , datepart(hour, dateadd(Hour, 2, StartDate)) as EndTime
       ,convert(varchar(8),datepart(hour, dateadd(Hour, 1, StartDate)))
      + ' - '
      + convert(varchar(8),datepart(hour, dateadd(Hour, 2, StartDate))) as Hours
              
  FROM  cte
  WHERE dateadd(Hour, 1, StartDate)<=  @eDate
    )
select * from cte
option (maxrecursion 0)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900