I want a Sql Query for self join

Question

0.00/5 (No votes)

See more:

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 18:48pm

Member 9492907

Updated 8-Nov-12 7:45am

Maciej Los

v2

Add a Solution

Comments

skydger 6-Nov-12 1:08am

What is the database engine do you use?

2 solutions

Solution 1

SQL

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.

Posted 5-Nov-12 19:18pm

kankeyan

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....

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

damodara naidu betha · Accepted Answer · 2012-11-06T19:53:00

Hi Try this code block...

SQL

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