x

Need to Create function for this block

In the warehouse , they scan the products to know the details of it. One such is Lot number. Here for a product , this lot number is 4 digit and comes in this format 'YYWW' eg: 0914 , 1003, 0842 . In this the first 2 digit is the year and last 2 digit represents the number of the week of that particular year. So i was asked to retrieve the date i.e the last day of that particular week is going to be the manufacturing date and 2 yrs from that date will be expiration date. When the lot number is scanned it triggers to run this query and returns both manufacturing date and expiration date. The point is i have the block (shown below with an example '0942'and this number is not constant) and i need to create a function for this block to make it a proper query so that when the trigger is called it will return the manufacturing and expiry date. Plz guide me.

declare @yywk varchar(5) set @yywk = '0942' declare @k int set @k = datalength(@yywk) declare @s int set @s = cast(substring(@yywk,0,3) as int) declare @d int set @d = CASE @s  when 20 then -4  when 19 then -3  when 18 then -2  when 17 then -1  when 16 then -6  when 15 then -5  when 14 then -4  when 13 then -3  when 12 then -1  when 11 then -7  when 10 then -6  when 09 then -5  when 08 then -3  when 07 then -2  when 06 then -1  when 05 then -7  when 04 then -5  when 03 then -4  when 02 then -3  when 01 then -2  when 00 then -7  else 0 end declare @yy int set @yy = cast('20' + substring(@yywk,0,3) as int) declare @ww int set @ww = cast(substring(@yywk,3,@k) as int) select  dateadd (week, @ww,dateadd(year,(@yy-1900),0)) + @d As 'Mandate',  dateadd(yy,2,dateadd (week, @ww,dateadd(year,(@yy-1900),0))+ @d) AS 'ExpDate' 
more ▼

asked Jan 21, 2010 at 10:21 PM in Default

Suresh gravatar image

Suresh
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

Let's think about the year first:

dateadd(year, cast(left(@yywk,2) as int) -1, '20000101')

Now, we just add on the number of weeks...

dateadd(week, cast(right(@yywk,2) as int) -1, dateadd(year, cast(left(@yywk,2) as int) -1, '20000101'))

So 0901 would give us Jan 1, 2009. 1002 would give us Jan 8, 2010. Different days of the week. You need to make an adjustment, and this is most easily done using datediff and dateadd.

Take a known end-of-week, and make it a constant marker point. Let's suppose you consider Dec 26, 1999 as that date. Count the days from there, divide by seven (rounding down), and add that many weeks back on.

dateadd(week, datediff(day, '19991226', SOMEDATE) / 7, '19991226')

Now we put that all together, and you get:

dateadd(week, datediff(day, '19991226', dateadd(week, cast(right(@yywk,2) as int) -1, dateadd(year, cast(left(@yywk,2) as int) -1, '20000101'))) / 7, '19991226')

That's your function. Use an inline TVF for performance reasons:

CREATE FUNCTION dbo.EndOfWeek(@yywk char(4)) RETURNS TABLE AS 
RETURN (SELECT dateadd(week, datediff(day, '19991226', dateadd(week, cast(right(@yywk,2) as int) -1, dateadd(year, cast(left(@yywk,2) as int) -1, '20000101'))) / 7, '19991226') AS EndOfWeek) ;

And call it using:

SELECT t.*, w.EndOfWeek FROM dbo.TableOfDates t  CROSS APPLY  dbo.EndOfWeek(t.yywk) w ; 
more ▼

answered Jan 21, 2010 at 11:54 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(comments are locked)
10|1200 characters needed characters left

This should do the trick:

/* ****************************************************************************
SELECT * FROM dbo.DoDatesFromYW('0942')
   **************************************************************************** */
CREATE FUNCTION dbo.DoDatesFromYW
(
@yywk VARCHAR(5)
)
RETURNS @TblSub TABLE
(
    Mandate DATETIME,
    ExpDate DATETIME
)
AS
BEGIN

DECLARE @k INT
DECLARE @s INT
DECLARE @d INT
DECLARE @yy INT
DECLARE @ww INT

SET @k = DATALENGTH(@yywk)
SET @s = CAST(SUBSTRING(@yywk, 0, 3) AS INT)
SET @d = CASE @s    WHEN 20 THEN -4
                    WHEN 19 THEN -3
                    WHEN 18 THEN -2
                    WHEN 17 THEN -1
                    WHEN 16 THEN -6
                    WHEN 15 THEN -5
                    WHEN 14 THEN -4
                    WHEN 13 THEN -3
                    WHEN 12 THEN -1
                    WHEN 11 THEN -7
                    WHEN 10 THEN -6
                    WHEN 09 THEN -5
                    WHEN 08 THEN -3
                    WHEN 07 THEN -2
                    WHEN 06 THEN -1
                    WHEN 05 THEN -7
                    WHEN 04 THEN -5
                    WHEN 03 THEN -4
                    WHEN 02 THEN -3
                    WHEN 01 THEN -2
                    WHEN 00 THEN -7
                    ELSE 0 END
SET @yy = CAST('20' + SUBSTRING(@yywk,0,3) AS INT)
SET @ww = CAST(SUBSTRING(@yywk,3,@k) AS INT)

INSERT INTO @TblSub (ManDate, ExpDate) VALUES
    (DATEADD (week, @ww,DATEADD(year,(@yy-1900),0)) + @d,
    DATEADD(yy,2,DATEADD (week, @ww,DATEADD(year,(@yy-1900),0))+ @d))
RETURN
END

You did all the hard work.

more ▼

answered Feb 05, 2010 at 11:02 PM

dvroman gravatar image

dvroman
1.1k 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x413
x58
x43

asked: Jan 21, 2010 at 10:21 PM

Seen: 1410 times

Last Updated: Jan 21, 2010 at 11:42 PM