February 14, 2011 at 12:50 pm
i'm after a function that i can apply to a datetime value and return the shift that was on.
I guess i should have a table with
StartTime, EndTime, Shift
1/1/11 06:00, 1/1/11 18:00, A
1/1/11 18:00, 2/1/11 06:00, B
2/1/11 06:00, 2/1/11 18:00, A
2/1/11 18:00, 3/1/11 06:00, C
but i'm struggling with the code. please help
February 14, 2011 at 1:09 pm
Looks like homework...
Have a look at the DATE/TIME functions in Books Online.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2011 at 1:34 pm
nah, self develpoment, then I can apply to ssrs
February 14, 2011 at 1:44 pm
well, i would tackle it with a table defining the hours associated to a givin shift period:
/*--results
RightNow ThisHour StartTime EndTime Shift
2011-02-14 15:43:31.960 15 6 18 A
*/
with MyShifts(StartTime,EndTime,Shift)
AS
(
SELECT 0,5,' B' UNION ALL
SELECT 6,18,' A' UNION ALL
SELECT 19,24,' B'
)
select
getdate() as RightNow ,
datediff(hh,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),getdate()) AS ThisHour,
MyShifts.*
from MyShifts
where datediff(hh,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),getdate())
between StartTime and EndTime
Lowell
February 14, 2011 at 2:40 pm
So far i got this
CREATE TABLE [dbo].[ShiftList](
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Shift] [char](1) NOT NULL
) ON [PRIMARY]
ALTER FUNCTION [dbo].[TEST]
(@Date datetime)
RETURNS nchar(1)
AS
BEGIN
Declare @shift nchar(1)
SELECT @shift= Shift
FROM Shiftlist
WHERE (StartDate < CONVERT(DATETIME, @Date, 102))
AND (EndDate > CONVERT(DATETIME, @Date, 102))
RETURN @shift -- performs the action
END
February 14, 2011 at 11:46 pm
my solution seem very slow, is there a quicker way to obtain the shift name?
February 15, 2011 at 6:26 pm
mea99sdp (2/14/2011)
my solution seem very slow, is there a quicker way to obtain the shift name?
Yes there is. 🙂 Don't do this in a scalar function. Inline code would probably be your best bet for this.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2011 at 10:42 am
inline code? a little help please:P
February 16, 2011 at 10:51 am
can you show the query that is using the shift function now? basically we are talking about joining that query to the shifts table directly, instead of using the function.
Lowell
February 16, 2011 at 11:35 am
TrackingNumDatestamp StationIDStatusCode
000235905 2008-12-02 18:36:42.0905-
000235905 2008-12-02 19:34:09.10731P
000235905 2008-12-02 19:34:10.09021P
001140044 2008-09-24 08:16:14.07710-
I want to group by shift so that i can compare, so from the datestamp i want to work out the shift by looking up the shifts table which lists all the start/end datestamps for the shifts.
February 16, 2011 at 11:49 am
not what I asked for , my friend sorry. Let me clarify.
I wanted to see the actual TSQL statement you are using.
show me something like this:
SELECT dbo.shift(somecolumn) as AValue, * from my table
with that SQL, and the schema you already provided for the shift table,we could give you a more coherent answer., something you could paste and test immediately.
Lowell
February 16, 2011 at 11:55 am
ALTER PROCEDURE [dbo].[usp_SEL_InputCount]
@StartDate datetime,@EndDate datetime
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100) PERCENT dbo.GetDay(ModuleTrx.Datestamp) AS Date, dbo.DayNight(ModuleTrx.Datestamp) AS DayNight, Modules.Type, COUNT(*) AS Count
FROM Modules INNER JOIN
ModuleTrx ON Modules.TrackingNum = ModuleTrx.TrackingNum INNER JOIN
Stations ON ModuleTrx.StationID = Stations.StationID
WHERE (ModuleTrx.Datestamp BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @Enddate, 102)) AND (Stations.StationType = N'LU')
GROUP BY dbo.DayNight(ModuleTrx.Datestamp), dbo.GetDay(ModuleTrx.Datestamp), Modules.Type
END
would like to add shift to the data so i can compare shifts over month
February 17, 2011 at 6:29 am
mea99sdp (2/16/2011)
inline code? a little help please:P
What I meant was that the code in the function is easy enough... remove the code from the function and use it directly in your main code.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2011 at 6:34 am
mea99sdp (2/16/2011)
ALTER PROCEDURE [dbo].[usp_SEL_InputCount]@StartDate datetime,@EndDate datetime
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100) PERCENT dbo.GetDay(ModuleTrx.Datestamp) AS Date, dbo.DayNight(ModuleTrx.Datestamp) AS DayNight, Modules.Type, COUNT(*) AS Count
FROM Modules INNER JOIN
ModuleTrx ON Modules.TrackingNum = ModuleTrx.TrackingNum INNER JOIN
Stations ON ModuleTrx.StationID = Stations.StationID
WHERE (ModuleTrx.Datestamp BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @Enddate, 102)) AND (Stations.StationType = N'LU')
GROUP BY dbo.DayNight(ModuleTrx.Datestamp), dbo.GetDay(ModuleTrx.Datestamp), Modules.Type
END
would like to add shift to the data so i can compare shifts over month
Heh... ok... now you have two functions in the mix and neither one of them is the "ShiftList" function you posted. They're probably both slower scalar functions. It's difficult for us to hit a rolling donut. Please take a look at the article at the first link in my signature line below. If you were to post your data in that type of readily consumable format and post ALL code, including any functions that may be involved, we could do better at giving you a high performance coded reply.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply