select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

select

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2008-04-22 : 01:25:32

Hi everyone. i need help in select statement.

DECLARE @employeeshift TABLE
(
shiftcode varchar(20),
shiftdesc varchar(50)
)
INSERT INTO @employeeshift

SELECT 'S1', 'Shift 1'
SELECT 'S2', 'Shift 2'
SELECT 'S3', 'Shift 3'

DECLARE @employeesched TABLE
(
fmon bit,
shiftmon varchar(20),
ftue bit,
shifttue varchar(20),
fwed bit,
shiftwed varchar(20),
fthu bit,
shiftthu varchar(20),
ffri bit,
shiftfri varchar(20),
fsat bit,
shiftsat varchar(20),
fsun bit,
shiftsun varchar(20)
)

INSERT INTO @employeesched
SELECT 1,'S1',1,'S2',1,'S3',1,'S1',1,'S1',0,'S2',0,'S3'

Above is my sample table and data.
@employeesched table.. fmon - fsun ... 0 if Day-off and 1 Not dayoff.
Given a 2 daterange. for example '04/16/2008' and '04/30/2008'.
My expected result is:

04/16/2008 S3 - Shift 3
04/17/2008 S1 - Shift 1
04/18/2008 S1 - Shift 1
04/19/2008 Day-Off
04/20/2008 Day-Off
04/21/2008 S1 - Shift 1
04/22/2008 S2 - Shift 2
04/23/2008 S3 - Shift 3
04/24/2008 S1 - Shift 1
04/25/2008 S1 - Shift 1
04/26/2008 Day-Off
04/27/2008 Day-Off
04/28/2008 S1 - Shift 1
04/29/2008 S2 - Shift 2
04/30/2008 S3 - Shift 3

tnx

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 02:29:23

[code]SET DATEFIRST 1--set monday as first day of week
DECLARE @employeeshift TABLE
(
shiftcode varchar(20),
shiftdesc varchar(50)
)
INSERT INTO @employeeshift

SELECT 'S1', 'Shift 1'
UNION ALL
SELECT 'S2', 'Shift 2'
UNION ALL
SELECT 'S3', 'Shift 3'

DECLARE @employeesched TABLE
(
fmon bit,
shiftmon varchar(20),
ftue bit,
shifttue varchar(20),
fwed bit,
shiftwed varchar(20),
fthu bit,
shiftthu varchar(20),
ffri bit,
shiftfri varchar(20),
fsat bit,
shiftsat varchar(20),
fsun bit,
shiftsun varchar(20)
)

DECLARE @startdate datetime,@enddate datetime
SELECT @startdate='16 Apr 2008',@enddate='30 Apr 2008'

INSERT INTO @employeesched
SELECT 1,'S1',1,'S2',1,'S3',1,'S1',1,'S1',0,'S2',0,'S3'

SELECT DATEADD(dd,v.number,@startdate),
CASE WHEN LEFT(tab.Shift,1) = '1' THEN s.shiftcode + ' - '+ s.shiftdesc ELSE 'Day Off' END
FROM @employeeshift s
INNER JOIN (
SELECT CASE RIGHT(ShiftInfo,3)
WHEN 'mon' THEN 1
WHEN 'tue' THEN 2
WHEN 'wed' THEN 3
WHEN 'thu' THEN 4
WHEN 'fri' THEN 5
WHEN 'sat' THEN 6
WHEN 'sun' THEN 7
END AS DayOrder,*
FROM
(
SELECT
CAST(fmon as varchar(1)) +shiftmon As shiftmon,
CAST(ftue as varchar(1)) +shifttue As shifttue,
CAST(fwed as varchar(1)) +shiftwed As shiftwed,
CAST(fthu as varchar(1)) +shiftthu As shiftthu,
CAST(ffri as varchar(1)) +shiftfri As shiftfri,
CAST(fsat as varchar(1)) +shiftsat As shiftsat,
CAST(fsun as varchar(1)) +shiftsun As shiftsun
FROM @employeesched)m
UNPIVOT ( Shift
FOR ShiftInfo IN ([shiftmon],[shifttue],
[shiftwed],
[shiftthu],
[shiftfri],
[shiftsat],
[shiftsun]))p)tab
ON s.shiftcode=RIGHt(tab.Shift,2)
CROSS JOIN master..spt_values v
WHERE v.type='p'
ANd DATEADD(dd,v.number,@startdate) <= @enddate
AND DATEPART(dw,DATEADD(dd,v.number,@startdate))=DayOrder
ORDER BY DATEADD(dd,v.number,@startdate)

output
------------------------------------

Day ShiftInfo
----------------------- ---------------------
2008-04-16 00:00:00.000 S3 - Shift 3
2008-04-17 00:00:00.000 S1 - Shift 1
2008-04-18 00:00:00.000 S1 - Shift 1
2008-04-19 00:00:00.000 Day Off
2008-04-20 00:00:00.000 Day Off
2008-04-21 00:00:00.000 S1 - Shift 1
2008-04-22 00:00:00.000 S2 - Shift 2
2008-04-23 00:00:00.000 S3 - Shift 3
2008-04-24 00:00:00.000 S1 - Shift 1
2008-04-25 00:00:00.000 S1 - Shift 1
2008-04-26 00:00:00.000 Day Off
2008-04-27 00:00:00.000 Day Off
2008-04-28 00:00:00.000 S1 - Shift 1
2008-04-29 00:00:00.000 S2 - Shift 2
2008-04-30 00:00:00.000 S3 - Shift 3
[/code]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2008-04-22 : 02:40:59

thank you very much !

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources