May 4, 2010 at 12:13 am
How can i bring below result
Shift st-time end-timd
1 06:00 13:59
2 14:00 21:59
3 22:00 05:59
Here i vil send current datetime, if current time is 2010-05-04 11:42:43.983 then shift is 1 and if i send time 2010-05-04 03:42:43.983
then shift is 3. this time is not constant. it may change . how can i bring this result. result should be match between start and end time. can any one plz guide me through sample code.
May 4, 2010 at 12:21 am
Which version of the sql you are using 2005/2008
May 4, 2010 at 12:23 am
im using sql2005
May 4, 2010 at 1:02 am
Please Check the below Query might be useful as i don't know how you are storing the shift time in the table.
declare @start1 varchar(10)
set @start1 = '06:00'
declare @End1 varchar(10)
set @End1 = '13:59'
declare @start2 varchar(10)
set @start2 = '14:00'
declare @End2 varchar(10)
set @End2 = '21:59'
declare @start3 varchar(10)
set @start3 = '22:00'
declare @End3 varchar(10)
set @End3 = '05:59'
--select convert(varchar(5), @start1, 108)
--select convert(varchar(5), GetDate(), 108)
Select
case
when (@start1 >convert(varchar(5), GetDate(), 108)) and (@End1 < convert(varchar(5), GetDate(), 108)) Then '1'
when (@start2 >convert(varchar(5), GetDate(), 108)) and (@End2 < convert(varchar(5), GetDate(), 108)) Then '2'
when (@start3 >convert(varchar(5), GetDate(), 108)) and (@End3 < convert(varchar(5), GetDate(), 108)) Then '3'
End as Shift
May 4, 2010 at 1:18 am
hi SSC Journeyman
My problem is, that i cant say this exact start and end time in my table. it may change. Shift may also extend . but time format should be 24 hour format.
May 4, 2010 at 3:25 am
Try This...
Declare @vShiftTable Table (ShiftID int, startTime datetime, endtime datetime)
Declare @vVarTime datetime
declare @start varchar(10)
set @start = '06:00'
declare @End varchar(10)
set @End = '13:59'
Insert into @vShiftTable
Select 1,@start,@End
set @start = '14:00'
set @End = '21:59'
Insert into @vShiftTable
Select 2,@start,@End
set @start = '22:00'
set @End = '05:59'
Insert into @vShiftTable
Select 3,@start,@End
Set @vVarTime = DateAdd(minute,-23,getdate())
Select *
from @vShiftTable
where case when Convert(datetime,Convert(varchar(10),@vVarTime,108)) > '1900-01-01 00:00:00.000' and Convert(datetime,Convert(varchar(10),@vVarTime,108)) <= '1900-01-01 05:59:00.000' then
DateAdd(dd,1,Convert(datetime,Convert(varchar(10),@vVarTime,108)))
else
Convert(datetime,Convert(varchar(10),@vVarTime,108)) end
between
Convert(datetime,Convert(varchar(10),startTime,108))
and
case when startTime > EndTime then
DateAdd(dd,1,Convert(datetime,Convert(varchar(10),EndTime,108)))
else
Convert(datetime,Convert(varchar(10),EndTime,108))
end
May 28, 2010 at 2:02 am
thank you Atif Sheikh.Its really helpful. I got exact result.
May 28, 2010 at 3:12 am
Glad it helped you.:-)
Please be sure to specify the test data yourself when you post any other question. It really helps to find quick and better answer.
May 29, 2010 at 11:11 am
-- Shift information
CREATE TABLE dbo.Shift
(
shift_id INTEGER NOT NULL
CONSTRAINT [PK dbo.Shift shift_id]
PRIMARY KEY CLUSTERED,
start_time DATETIME NOT NULL
CONSTRAINT [CK dbo.Shift start_time time_only]
CHECK (start_time >= '1900-01-01T00:00:00' AND start_time < '1900-01-02T00:00:00'),
end_time DATETIME NOT NULL
CONSTRAINT [CK dbo.Shift end_time time_only]
CHECK (end_time >= '1900-01-01T00:00:00' AND end_time < '1900-01-02T00:00:00'),
CONSTRAINT [UQ dbo.Shift start_time, end_time]
UNIQUE NONCLUSTERED (start_time, end_time)
);
-- Sample shift definitions
INSERT dbo.Shift (shift_id, start_time, end_time)
VALUES (1, '06:00', '14:00');
INSERT dbo.Shift (shift_id, start_time, end_time)
VALUES (2, '14:00', '22:00');
INSERT dbo.Shift (shift_id, start_time, end_time)
VALUES (3, '22:00', '06:00');
-- Parameterised view to find shift details from a given date & time
CREATE FUNCTION dbo.LookupShift
(
@InputDateTime DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH TimeOnly (input_time)
AS (
-- Remove date component from @TestTime
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, @InputDateTime), @InputDateTime)
)
SELECT TOP (1)
SelectedShift.input_time,
SelectedShift.shift_id,
SelectedShift.start_time,
SelectedShift.end_time
FROM (
SELECT T.input_time, S.shift_id, S.start_time, S.end_time
FROM TimeOnly T
JOIN dbo.Shift S
ON T.input_time >= S.start_time
AND T.input_time < S.end_time
AND S.start_time < S.end_time
UNION ALL
SELECT T.input_time, S.shift_id, S.start_time, S.end_time
FROM TimeOnly T
JOIN dbo.Shift S
ON T.input_time >= S.end_time
AND T.input_time >= S.start_time
AND T.input_time < DATEADD(DAY, 1, S.start_time)
AND S.start_time > S.end_time
UNION ALL
SELECT T.input_time, S.shift_id, S.start_time, S.end_time
FROM TimeOnly T
JOIN dbo.Shift S
ON T.input_time <= S.start_time
AND T.input_time >= DATEADD(DAY, -1, S.end_time)
AND S.start_time > S.end_time
) SelectedShift;
GO
-- Test with a single lookup
DECLARE @TestTime DATETIME;
SET @TestTime = '2010-05-04T11:42:43.983';
SELECT FN.input_time,
FN.shift_id,
FN.start_time,
FN.end_time
FROM dbo.LookupShift (@TestTime) FN;
GO
-- Test with multiple lookups from a source table
DECLARE @SourceData TABLE (input_datetime DATETIME NOT NULL);
-- Sample dates and times to test with
INSERT @SourceData (input_datetime)
SELECT '2010-05-29T04:01:32' UNION ALL
SELECT '2010-05-29T06:51:17' UNION ALL
SELECT '2010-05-29T11:33:41' UNION ALL
SELECT '2010-05-29T17:27:08' UNION ALL
SELECT '2010-05-29T23:59:11' UNION ALL
SELECT '2010-05-29T06:00:00' UNION ALL
SELECT '2010-05-29T14:00:00' UNION ALL
SELECT '2010-05-29T22:00:00';
-- Find all the shifts all in one statement
SELECT SD.input_datetime,
FN.input_time,
FN.shift_id,
FN.start_time,
FN.end_time
FROM @SourceData SD
CROSS
APPLY dbo.LookupShift (SD.input_datetime) FN;
GO
-- Tidy up
DROP FUNCTION dbo.LookupShift;
DROP TABLE dbo.Shift;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2010 at 10:36 pm
Hi Paul White NZ
Its was nice. But one clariffication. here Starttime and endtime is nchar(10) type.
i need to get the result without using any functions.
I need another one more help. When user try to insert start time and endtime, we need to validate (i.e start time and endtime should not repeat again.)
eg.
sttime endtime
'06:00','08:59'
'09:00','15:59'
'16:00','21:59'
if user try to insert the data as
sttime endtime
11:00 17:59
we should allow not to save, because its already this falls between the time.
sttime endtime
22:00 05:59
here we allow the data to save. because its new which is not falls between any time.
May 31, 2010 at 12:36 am
urzsuresh (5/30/2010)
Hi Paul White NZIts was nice. But one clariffication. here Starttime and endtime is nchar(10) type.
i need to get the result without using any functions.
I need another one more help. When user try to insert start time and endtime, we need to validate (i.e start time and endtime should not repeat again.)
eg.
sttime endtime
'06:00','08:59'
'09:00','15:59'
'16:00','21:59'
if user try to insert the data as
sttime endtime
11:00 17:59
we should allow not to save, because its already this falls between the time.
sttime endtime
22:00 05:59
here we allow the data to save. because its new which is not falls between any time.
Care to explain why you can't use functions?
May 31, 2010 at 1:35 am
my higher person asking us to eliminate the funtions.
May 31, 2010 at 1:50 am
urzsuresh (5/31/2010)
my higher person asking us to eliminate the funtions.
Is there still any problem in solution I posted? I thought you got the exact results?
May 31, 2010 at 2:57 am
hello friend,
I have small doubt in your query.Why did u hard code the date like below
where case when Convert(datetime,Convert(varchar(10),@vVarTime,108)) > '1900-01-01 00:00:00.000' and Convert(datetime,Convert(varchar(10),@vVarTime,108)) <= '1900-01-01 05:59:00.000' then
This is another requirement,
I need another one more help. When user try to insert start time and endtime, we need to validate (i.e start time and endtime should not repeat again.)
eg.
sttime endtime
'06:00','08:59'
'09:00','15:59'
'16:00','21:59'
if user try to insert the data as
sttime endtime
11:00 17:59
we should allow not to save, because its already this falls between the time.
sttime endtime
22:00 05:59
here we allow the data to save. because its new which is not falls between any time.
May 31, 2010 at 3:54 am
Its just to check the multi dated shift and its end time. you can use variable here.
Declare @vShiftTable Table (ShiftID int, startTime datetime, endtime datetime)
Declare @vVarTime datetime
Declare @vMultiDateTime datetime
declare @start varchar(10)
declare @End varchar(10)
set @start = '06:00'
set @End = '13:59'
Insert into @vShiftTable
Select 1,@start,@End
set @start = '14:00'
set @End = '21:59'
Insert into @vShiftTable
Select 2,@start,@End
set @start = '22:00'
set @End = '05:59'
Insert into @vShiftTable
Select 3,@start,@End
Set @vVarTime = DateAdd(minute,-49,getdate())
Select @vMultiDateTime = Convert(datetime,Convert(varchar(10),EndTime,108))
from @vShiftTable
where startTime > EndTime
--Select @vVarTime,Convert(datetime,Convert(varchar(10),@vVarTime,108)),@vMultiDateTime
Select * ,@vVarTime
from @vShiftTable
where case when Convert(datetime,Convert(varchar(10),@vVarTime,108)) > '1900-01-01 00:00:00' and Convert(datetime,Convert(varchar(10),@vVarTime,108)) <= @vMultiDateTime
then
DateAdd(dd,1,Convert(datetime,Convert(varchar(10),@vVarTime,108)))
else
Convert(datetime,Convert(varchar(10),@vVarTime,108))
end
between
Convert(datetime,Convert(varchar(10),startTime,108))
and
case when startTime > EndTime then
DateAdd(dd,1,Convert(datetime,Convert(varchar(10),EndTime,108)))
else
Convert(datetime,Convert(varchar(10),EndTime,108))
end
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply