UDF - Table - 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 2000 Forums

Transact-SQL (2000)

UDF - Table

Author

Topic

tc
Starting Member

11 Posts

Posted - 2006-02-07 : 12:09:57

I have the following code I'm using in Microsoft Access to find out what crew is working for a specific date. I want to create a UDF that does the same thing, but I'm not sure where to start. Instead of an array should I be using a table?

Const ShiftCycleStart As Date = "1/6/1997" 'known starting date for shift rotation
Const AnodeCycleStart As Date = "7/23/2003"

Function Get_Shift12hr(SDate As Date, DayOrNight As Integer) As String
' This function calculates the crew that is working for a given day (SDate) and Shift (DayOrNight)
' Crew rotation works on a 28 day cycle
' DayOrNight should be 0 for dayshift and 1 for nightshift

Dim Cycle12Hour(28, 1) As String
Dim DayInCycle As Integer

'populate the array for the shiftcycle
Cycle12Hour(1, 0) = "H": Cycle12Hour(1, 1) = "J"
Cycle12Hour(2, 0) = "H": Cycle12Hour(2, 1) = "I"
Cycle12Hour(3, 0) = "H": Cycle12Hour(3, 1) = "I"
Cycle12Hour(4, 0) = "H": Cycle12Hour(4, 1) = "I"
Cycle12Hour(5, 0) = "J": Cycle12Hour(5, 1) = "K"
Cycle12Hour(6, 0) = "J": Cycle12Hour(6, 1) = "K"
Cycle12Hour(7, 0) = "J": Cycle12Hour(7, 1) = "K"
Cycle12Hour(8, 0) = "I": Cycle12Hour(8, 1) = "K"
Cycle12Hour(9, 0) = "I": Cycle12Hour(9, 1) = "J"
Cycle12Hour(10, 0) = "I": Cycle12Hour(10, 1) = "J"
Cycle12Hour(11, 0) = "I": Cycle12Hour(11, 1) = "J"
Cycle12Hour(12, 0) = "K": Cycle12Hour(12, 1) = "H"
Cycle12Hour(13, 0) = "K": Cycle12Hour(13, 1) = "H"
Cycle12Hour(14, 0) = "K": Cycle12Hour(14, 1) = "H"
Cycle12Hour(15, 0) = "J": Cycle12Hour(15, 1) = "H"
Cycle12Hour(16, 0) = "J": Cycle12Hour(16, 1) = "K"
Cycle12Hour(17, 0) = "J": Cycle12Hour(17, 1) = "K"
Cycle12Hour(18, 0) = "J": Cycle12Hour(18, 1) = "K"
Cycle12Hour(19, 0) = "H": Cycle12Hour(19, 1) = "I"
Cycle12Hour(20, 0) = "H": Cycle12Hour(20, 1) = "I"
Cycle12Hour(21, 0) = "H": Cycle12Hour(21, 1) = "I"
Cycle12Hour(22, 0) = "K": Cycle12Hour(22, 1) = "I"
Cycle12Hour(23, 0) = "K": Cycle12Hour(23, 1) = "H"
Cycle12Hour(24, 0) = "K": Cycle12Hour(24, 1) = "H"
Cycle12Hour(25, 0) = "K": Cycle12Hour(25, 1) = "H"
Cycle12Hour(26, 0) = "I": Cycle12Hour(26, 1) = "J"
Cycle12Hour(27, 0) = "I": Cycle12Hour(27, 1) = "J"
Cycle12Hour(28, 0) = "I": Cycle12Hour(28, 1) = "J"

DayInCycle = ((SDate - ShiftCycleStart) Mod 28) + 1
Get_Shift12hr = Cycle12Hour(DayInCycle, DayOrNight)
End Function

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-07 : 13:46:19

U can use a temp table as an array

The table can be populated, more or less like a normal table

Create table #Cycle12Hour( DayInCycle int, DayOrNight int, Shift12hr varchar(1) )

Insert Into #Cycle12Hour
Select 1,0,'H' Union All
Select 2,0,'H' Union All
Select 3,0,'H' Union All
Select 4,0,'H' Union All
Select 5,0,'J' Union All
....
....
Select 6,1,'K' Union All
Select 7,1,'K' Union All
...
Select 28,1,'J'

Then instead of
Get_Shift12hr = Cycle12Hour(DayInCycle, DayOrNight)
Use
something similar to the following:

-- @DayInCycle and @DayOrNight are the parameters passed to the function
Declare @Get_Shift12hr varchar(1)
Set @Get_Shift12hr = (Select Shift12hr from #Cycle12Hour where 
          DayInCycle = @DayInCycle and DayOrNight = @DayOrNight)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 03:38:37

>>Set @Get_Shift12hr = (Select Shift12hr from #Cycle12Hour where
>> DayInCycle = @DayInCycle and DayOrNight = @DayOrNight)

you will get error if the subquery returns more than one data
The safer method is to use

Select @Get_Shift12hr = Shift12hr from #Cycle12Hour where
DayInCycle = @DayInCycle and DayOrNight = @DayOrNight

Madhivanan

Failing to plan is Planning to fail

tc
Starting Member

11 Posts

Posted - 2006-02-08 : 16:32:02

Sorry for my ignorance, but I'm new to all of this. Do I need to create a function that creates the temp table and then references that table?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-08 : 19:09:40

I answered with the assumption thet need to convert static array used in Access is to be used in a UDF of SQL server 2000.

If I'm not correct
could you please tell what do you want to do. Not the logic of the function (which I can see).

Also tell, why do u want to create a function?
Ur concern is creating a function or making use of an array? or anything else?

tc
Starting Member

11 Posts

Posted - 2006-02-09 : 08:30:47

Thanks for your reply. Originally, my table was in Access, now the data has been moved to a SQL server. I now have the table with the following format:

Sample_Date Pot_No Sample_Value
01/01/2006 7:00:00AM 1 2.3
01/01/2006 7:00:00AM 2 3.6
01/01/2006 7:00:00AM 3 4.5
01/01/2006 7:00:00PM 1 2.7
01/01/2006 7:00:00PM 2 6.3
01/01/2006 7:00:00PM 3 9.2

So based on this data, I need to find out what crew was working for each shift. I don't have any experience with UDF's, I just assumed that would be the best way to go, am I going about this wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 08:33:59

can you explain what is this table ? How is it related to the ShiftCycle ?

----------------------------------
'KH'
everything that has a beginning has an end

tc
Starting Member

11 Posts

Posted - 2006-02-09 : 10:34:20

Each day consists of two shifts, based on the "Sample_Date" (if ends in "AM" it's day shift, "PM" it's night shift) and the array originally mentioned, I need to determine what crew was working for each shift. I apologize for not explaining myself very well and hope this helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 20:32:57

Sorry... still a bit lost on what you want.

>> I need to determine what crew was working for each shift
Where will the crew information comes from ? the ShiftCycle only contain the CycleDay, AM / PM Shift and 12hourshift ?

Can you post your tables DDL, some sample data and expected result also ?

----------------------------------
'KH'
everything that has a beginning has an end

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 22:16:51

PLEASE, follow the link below and post what it asks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tc
Starting Member

11 Posts

Posted - 2006-02-10 : 15:38:43

I found a way around that seems pretty clumsy, feedback would be appreciated.

First, I set up this function:

CREATE FUNCTION dbo.GetCycle
(@SDate datetime)
RETURNS int
AS
BEGIN
RETURN ((DateDiff(Day,'01/06/1997',dbo.DatePart(@SDate))) % 28) + 1
END

Then I run a select statement on the following table:

SDateTime--------------Pot_No---Shift---Aes
2006-01-01 00:00:00.000 1 DAY 1
2006-01-01 00:00:00.000 2 DAY 3
2006-01-01 00:00:00.000 3 DAY 2
2006-01-01 00:00:00.000 1 NIGHT 6
2006-01-01 00:00:00.000 2 NIGHT 1
2006-01-01 00:00:00.000 3 NIGHT 3

SQL Statement:
SELECT dbo.DatePart(SDateTime) as Day, Pot_No, Shift, Aes,
(CASE WHEN dbo.GetCycle(SDateTime) < 5 AND Shift = 'DAY' THEN 'H'
WHEN dbo.GetCycle(SDateTime) > 4 and dbo.GetCycle(SDateTime) < 8 AND Shift = 'DAY' THEN 'J'
WHEN dbo.GetCycle(SDateTime) > 7 and dbo.GetCycle(SDateTime) < 12 AND Shift = 'DAY' THEN 'I'
WHEN dbo.GetCycle(SDateTime) > 11 and dbo.GetCycle(SDateTime) < 15 AND Shift = 'DAY' THEN 'K'
WHEN dbo.GetCycle(SDateTime) > 14 and dbo.GetCycle(SDateTime) < 19 AND Shift = 'DAY' THEN 'J'
WHEN dbo.GetCycle(SDateTime) > 18 and dbo.GetCycle(SDateTime) < 22 AND Shift = 'DAY' THEN 'H'
WHEN dbo.GetCycle(SDateTime) > 21 and dbo.GetCycle(SDateTime) < 26 AND Shift = 'DAY' THEN 'K'
WHEN dbo.GetCycle(SDateTime) > 25 AND Shift = 'DAY' THEN 'I'

WHEN dbo.GetCycle(SDateTime) =1 AND Shift = 'NIGHT' THEN 'J'
WHEN dbo.GetCycle(SDateTime) > 1 and dbo.GetCycle(SDateTime) < 5 AND Shift = 'NIGHT' THEN 'I'
WHEN dbo.GetCycle(SDateTime) > 4 and dbo.GetCycle(SDateTime) < 9 AND Shift = 'NIGHT' THEN 'K'
WHEN dbo.GetCycle(SDateTime) > 8 and dbo.GetCycle(SDateTime) < 12 AND Shift = 'NIGHT' THEN 'J'
WHEN dbo.GetCycle(SDateTime) > 11 and dbo.GetCycle(SDateTime) < 16 AND Shift = 'NIGHT' THEN 'H'
WHEN dbo.GetCycle(SDateTime) > 15 and dbo.GetCycle(SDateTime) < 19 AND Shift = 'NIGHT' THEN 'K'
WHEN dbo.GetCycle(SDateTime) > 18 and dbo.GetCycle(SDateTime) < 23 AND Shift = 'NIGHT' THEN 'I'
WHEN dbo.GetCycle(SDateTime) > 2 and dbo.GetCycle(SDateTime) < 26 AND Shift = 'NIGHT' THEN 'H'
WHEN dbo.GetCycle(SDateTime) > 25 AND Shift = 'NIGHT' THEN 'J'
Else 'Z' End) As Crew
FROM dbo.tblPotHist

I then get the desired output:
Day----------Pot_No-----Shift--Aes-----Crew
01/01/2006 1 DAY 1 J
01/01/2006 2 DAY 3 J
01/01/2006 3 DAY 2 J
01/01/2006 1 NIGHT 6 K
01/01/2006 2 NIGHT 1 K
01/01/2006 3 NIGHT 3 K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources