Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
In my project i am using a table tbloperatinghours..the table structure is like this
 [timingId]  ,[StartTime]   ,[EndTime]    ,[ShiftId]    ,[BranchId] ,[DayId]
     1       10:00:00.000    12:00:00.000     1             112         1
     2       12:00:00.000    13:00:00.000     2             112         1
     3       13:00:00.000    14:00:00.000     3             112         1
     4       14:00:00.000    15:00:00.000     1             112         2
     5       15:00:00.000    16:00:00.000     2             112         2
 
I need the values like this
           Shift1                 Shift2                       Shift3                 
10:00:00.000-12:00:00.000     12:00:00.000-13:00:00.000      13:00:00.000-14:00:00.000
 
how can i get this?
 
plz help me....
Posted 5-Nov-12 19:48pm
Edited 8-Nov-12 8:45am
v2
Comments
skydger at 6-Nov-12 1:08am
   
What is the database engine do you use?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Try this code block...
 
DECLARE @DeptShiftDetails TABLE
(
[timingId] INT,[StartTime] VARCHAR(15),[EndTime] VARCHAR(15),[ShiftId] INT,[BranchId] INT,[DayId] INT
)
 
INSERT INTO @DeptShiftDetails VALUES
(1,'10:00:00.000','12:00:00.000',1,112,1),
(2,'12:00:00.000' ,'13:00:00.000',2 ,112 ,1),
(3,'13:00:00.000','14:00:00.000', 3, 112, 1),
(4,'14:00:00.000','15:00:00.000',1, 112, 2),
(5, '15:00:00.000','16:00:00.000', 2, 112, 2),
(6,'09:00:00.000','11:00:00.000',1,112,3);
 
DECLARE @strQuery VARCHAR(MAX);
 
WITH ShiftCTE As
(
  SELECT timingId,[StartTime]+'-'+[EndTime] ShiftTiming, 'Shift'+CAST(ShiftId as VARCHAR) ShiftName,DayId 
  FROM @DeptShiftDetails
)
SELECT DayId,ISNULL(Shift1,'No Work') Shift1,ISNULL(Shift2,'No Work') Shift2,ISNULL(Shift3,'No Work') Shift3 FROM (
SELECT DayId,ShiftTiming,ShiftName FROM ShiftCTE) Main
PIVOT
(
 MAX(ShiftTiming) FOR ShiftName IN (Shift1,Shift2,Shift3)
) PVT
 
Thank You
  Permalink  
Comments
Maciej Los at 8-Nov-12 13:48pm
   
It should works, my 5!
Marcus Kramer at 8-Nov-12 14:01pm
   
+5.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Select case when k.shiftid=1 then  isnull(j.starttime  +' '+  j.endtime,'') end as Shift1,
case when k.shiftid=2 then  isnull(j.starttime  +' '+  j.endtime,'') end as Shift2,
case when k.shiftid=3 then  j.starttime  +' '+  j.endtime end as Shift3
from jk j
JOIN jk k
ON     j.shiftid=k.shiftid
AND    j.timingid=k.timingid
AND    j.dayid=k.dayid
 

Try the above query.
 
I hope it will useful.
  Permalink  
Comments
Member 9492907 at 6-Nov-12 2:16am
   
I got the answer like this...I d't want this null columns..how can i remove it...
 
2012-11-06 10:00 2012-11-06 02:00: NULL NULL NULL NULL 225 8
NULL NULL 2012-11-06 03:00 2012-11-06 04:00:NULL NULL 225 8
NULL NULL NULL NULL 2012-11-06 05:00:00.000 2012-11-06 06:00:00.000 225 8
2012-11-06 07:00:00.000 2012-11-06 08:00:00.000 NULL NULL NULL NULL 225 6
NULL NULL 2012-11-06 05:00:00.000 2012-11-06 06:00:00.000 NULL NULL 225 6
NULL NULL NULL NULL 2012-11-06 03:00:00.000 2012-11-06 04:00:00.000 225 6
Member 9492907 at 6-Nov-12 2:21am
   
I d't getting values in all the cases ..plz help me to do it....

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

  Print Answers RSS
0 OriginalGriff 7,205
1 DamithSL 5,114
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,747
4 Kornfeld Eliyahu Peter 4,474


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 8 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100