Need SQL help - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Need SQL help

Author

Topic

mukejee
Starting Member

5 Posts

Posted - 2014-02-17 : 06:10:27

Hi Experts,

I need to write a SQL for the below scenario. Please help me.

ID shiftname shiftstart shiftend
1 ABC 9:00AM 4:00PM
2 BCD 4:00PM 10:00PM

Now I have another table

ID Empname Intime OutTime Shiftname
1 Ram 10:00AM 3PM ABC
2 Sam 11:00AM 4PM ABC

So here i need to check the intime and outtime against the time shift start and shiftend in the 1st table and populate the shift name.
How can I achieve it?

Thanks
MUkesh

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-17 : 06:52:48

[code]
;with _shiftName
AS
(select 1 [ID], 'ABC' [shiftName], CAST('9:00AM' as TIME) [shiftStart],CAST('4:00PM' as TIME) [shiftEnd] union all
select 2 ,'BCD',CAST('4:00PM' as TIME),CAST('10:00PM' as TIME))

,
shiftDaily
AS
( select 1 [ID],'Ram'[EmpName],CAST('10:00Am' as TIME) [InTime],CAST('3:00PM'as TIME) [OutTime] union all
select 2 ,'Sam', CAST('11:00AM' as TIME), CAST('4:00PM' as TIME))

[/code]

[code]
SELECT *
FROM
shiftDaily SD
OUTER APPLY
(
SELECT TOP 1
[shiftName]
FROM _shiftName SN
WHERE SD.InTime>=SN.[shiftStart]
AND SD.OutTime<=SN.[shiftEnd]
ORDER BY ID asc
)SN

[/code]

depinding of what type are column , where is another :

[code]
SELECT *
FROM
shiftDaily SD
OUTER APPLY
(
SELECT TOP 1
[shiftName]
FROM _shiftName SN
WHERE CAST(SD.InTime as TIME)>=CAST(SN.[shiftStart] as TIME)
AND CAST(SD.OutTime as TIME) <=CAST(SN.[shiftEnd] as TIME)
ORDER BY ID asc
)SN
[/code]

S

sabinWeb MCP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-17 : 07:11:45

[code]
SELECT t1.*,t2.shiftname
FROM Table2 t2
CROSS APPLY (SELECT TOP 1 shiftname
FROM table1
ORDER BY DATEDIFF(ss,shiftstart,Intime),DATEDIFF(ss,shiftend,Outtime)
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources