Click here to Skip to main content
12,248,339 members (46,670 online)
Rate this:
 
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
Maciej Los218.2K
v2
Comments
skydger 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 8-Nov-12 13:48pm
   
It should works, my 5!
Marcus Kramer 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 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 8 Nov 2012
Copyright © CodeProject, 1999-2016
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