January 24, 2012 at 1:16 pm
Ok.. I'm relatively new to using a SQL Server.
I have access to create and almost do my will on a "virtual" instance of SQL Server 2005.
I have created the following. ( It does not need information to run )
Essentially, what I wanted was a routine or procedure or what ever the appropriate method for SQL server is. That would take a date ( like a value in some other records "datetime" field ) and return a specified "FISCAL INFORMATION" for that date. I don't know how to display what I get now as it's not important.
I'm at a loss tho... If I have a table that has a "DATETIME" column... How do I tie in this procedure? Should this be a function? I could want anywhere from 1 to 21 different pieces of information. Help would sure be appreciated.
Regards,
B
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[calcFiscalInfo]
--Add the parameters for the stored procedure here ( incomming parameters )
@MyDatetime datetime,
@FixedPayDaydatetime,
-- Local Variables
@dteRefSundaydatetime = NULL,
@dteInternaldatetime = NULL,
@dteInternalSundatetime = NULL,
@dteWrkDayBegdatetime = NULL,-- MFG Workday are from the hours of 19:00 - 19:00 hrs
@dteWrkDayEnddatetime = NULL,
@dteShiftBegdatetime = NULL,-- Shift's begin on 19:00 or 07:00
@dteShiftEnddatetime = NULL,
@dteWrkWkBegdatetime = NULL,
@dteWrkWkEnddatetime = NULL,
@dtePayPerBegdatetime = NULL,
@dtePayPerEnddatetime = NULL,
@dteTemp1datetime = NULL,
@dteTEmp2datetime = NULL,
@dteBegYeardatetime = NULL,
@dteEndYeardatetime = NULL,
@strMonthvarChar(3) = NULL,
@strQtrvarChar(2) = NULL,
@strRefSwShftvarChar(2) = NULL,
@strISShiftvarChar(1) = NULL,-- 1 == Night Shift 19 - 07
-- 2 == Day Shift 07 - 19
@strWrkShiftIDvarChar(1) = NULL,-- A, B, C, DBased on if it's even or odd number of weeks from refernece date
@strWrkDyShiftsvarChar(2) = NULL,-- Both Shift ID's for this Atmel Workday 19 - 19
@strCrntShiftIDvarChar(1) = NULL,
@str1Shftvarchar(7) = NULL,
@str2Shftvarchar(7) = NULL,
@strEvenOddvarChar(1) = NULL,
@strDayofWeekvarChar(3) = NULL,
@intWkDyint = 0,
@intTempint = -1,
@intWWksint = -1,
@intFYearint = -1
AS
BEGIN
--Setting NULL ( default values )
-- Fixed Date In History ( Must be a Payday ) ( Payday swing shifts are D and A )
SET @FixedPayDay = ISNULL(@FixedPayday,'1/4/2007')
SET @MyDatetime = ISNULL(@MyDatetime,GETDATE())
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Setting Reference Date information ( used
SET @dteRefSunday = DATEADD(Day, 1-DATEPART ( dw , @FixedPayDay ), @FixedPayDay)
SET @strRefSwShft = 'DA'
-- Set the internal date and time for use in calculations
IF (DATEPART(hh, @MyDateTime) >=19) OR (DATEPART(hh, @MyDateTime) < 7)
BEGIN
IF (DATEPART(hh, @MyDateTime) >= 19) SET @dteInternal = DATEADD(Day,1,@MyDateTime)
IF (DATEPART(hh, @MyDateTime) < 7) SET @dteInternal = DATEADD(Day,0,@MyDateTime)
SET @dteInternal = convert(varchar, @dteInternal, 101) + ' 06:00:00'
END
ELSE
BEGIN
SET @dteInternal = DATEADD(Day,0,@MyDateTime)
SET @dteInternal = convert(varchar, @dteInternal, 101) + ' 08:00:00'
END
-- Set Sunday Date for target datetime
SET @dteInternalSun = convert(varchar, DATEADD(Day, 1-DATEPART ( dw , @dteInternal ), @dteInternal), 101)
-- Get day of the week
IF DATEPART(weekday,@dteInternal) = 1 SET @strDayofWeek = 'Sun'
IF DATEPART(weekday,@dteInternal) = 2 SET @strDayofWeek = 'Mon'
IF DATEPART(weekday,@dteInternal) = 3 SET @strDayofWeek = 'Tue'
IF DATEPART(weekday,@dteInternal) = 4 SET @strDayofWeek = 'Wed'
IF DATEPART(weekday,@dteInternal) = 5 SET @strDayofWeek = 'Thu'
IF DATEPART(weekday,@dteInternal) = 6 SET @strDayofWeek = 'Fri'
IF DATEPART(weekday,@dteInternal) = 7 SET @strDayofWeek = 'Sat'
-- Fiscal Date starts 19:00 to 19:00
IF (DATEPART(hh, @dteInternal) < 7)
BEGIN
SET @dteWrkDayBeg= convert(varchar, DATEADD(Day,-1,@dteInternal), 101) + ' 19:00:00'
SET @dteWrkDayEnd= convert(varchar, DATEADD(Day,1,@dteWrkDayBeg), 101) + ' 19:00:00'
SET @dteShiftBeg = @dteWrkDayBeg
SET @dteShiftEnd = DATEADD(hour,12,@dteWrkDayBeg)
SET @strISShift = '1'
END
ELSE
BEGIN
SET @dteWrkDayBeg= convert(varchar, DATEADD(Day,-0,@dteInternal), 101) + ' 19:00:00'
SET @dteWrkDayEnd= convert(varchar, @dteInternal, 101) + ' 19:00:00'
SET @dteShiftBeg = DATEADD(hour,-12,@dteWrkDayEnd)
SET @dteShiftEnd = @dteWrkDayEnd
SET @strISShift = '2'
END
--Determine EVEN/ODD number of weeks from REFERENCE DATE
SET @dteTemp1 = convert(varchar, DATEADD(Day, 1-DATEPART ( dw , @FixedPayDay ), @FixedPayDay), 101)-- Sunday of Fixed Refernece Day
SET @dteTemp2 = convert(varchar, DATEADD(Day, 1-DATEPART ( dw , @dteInternal ), @dteInternal), 101) -- Sunday of Target Day
SET @strEvenOdd = 'O'
SET @str1Shft= 'DBBBDDD'
SET @str2Shft= 'AAACCCC'
IF DATEDIFF(wk, @dteTemp1, @dteTemp2) % 2 = 0
BEGIN
SET @str1Shft= 'BBBBDDD'
SET @str2Shft= 'AAAACCC'
SET @strEvenOdd = 'E'
END
--Calculate current Crnt Shift / #1 Shift / #2 Shift / All Shift strings
SET @intWkDy = DATEPART ( dw , @dteInternal )
IF @strISShift = '1' SET @strCrntShiftID = SUBSTRING(@str1Shft,@intWkDy,1)
IF @strISShift = '2' SET @strCrntShiftID = SUBSTRING(@str2Shft,@intWkDy,1)
SET @strWrkDyShifts = SUBSTRING(@str1Shft,@intWkDy,1) + '' + SUBSTRING(@str2Shft,@intWkDy,1)
--Calculate Payperiod for target date
if @strEvenOdd = 'E'
BEGIN
SET @dtePayPerBeg= DATEADD(day,-1,@dteInternalSun) + ' ' + '19:00:00'
SET @dtePayPerEnd= DATEADD(day,13,@dteInternalSun) + ' ' + '19:00:00'
END
IF @strEvenOdd = 'O'
BEGIN
SET @dtePayPerBeg= DATEADD(day,-8,@dteInternalSun) + ' ' + '19:00:00'
SET @dtePayPerEnd= DATEADD(day,+6,@dteInternalSun) + ' ' + '19:00:00'
END
--Calculate Fiscal WWk information
-- Determine Beginning of Year Date ( 1st Sun of the year )
SET @intTemp = 1
WHILE @intTemp < 4
BEGIN
-- Testing to see the beginning / ending year dates and if tgt date falls between them
IF @intTemp = 1 SET @dteTemp1 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) - 0 AS varchar(4))
IF @intTemp = 2 SET @dteTemp1 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) - 1 AS varchar(4))
IF @intTemp = 3 SET @dteTemp1 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) + 1 AS varchar(4))
IF @intTemp = 1 SET @dteTemp2 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) + 1 AS varchar(4))
IF @intTemp = 2 SET @dteTemp2 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) + 0 AS varchar(4))
IF @intTemp = 3 SET @dteTemp2 = '1/1/' + CAST(CAST(DATEPART ( year , @dteInternal ) AS INT) + 2 AS varchar(4))
-- If the 1st of a year is a sat ( it starts the year ) if NOT ( first saturday of the year begins
IF DATEPART(dw, @dteTemp1) = 7 SET @dteTemp1 = convert(varchar, @dteTemp1, 101) + ' 19:00:00'
IF DATEPART(dw, @dteTemp1) < 7 SET @dteTemp1 = convert(varchar, DATEADD(day,-1 * DATEPART ( dw , @dteTemp1 ),@dteTemp1), 101) + ' 19:00:00'
IF DATEPART(dw, @dteTemp2) = 7 SET @dteTemp2 = convert(varchar, @dteTemp2, 101) + ' 19:00:00'
IF DATEPART(dw, @dteTemp2) < 7 SET @dteTemp2 = convert(varchar, DATEADD(day,-1 * DATEPART ( dw , @dteTemp2 ),@dteTemp2), 101) + ' 19:00:00'
-- SNAFU with accounting 2010 should've started DEC 26, 2009 - Jan 01, 2011 for 53 weeks
-- However, for reasons of stupidity accounting wanted 2009 to have 53 weeks ( somthing to do with matching the French ( whom we were selling ))
-- So 2009 Starts on 27 Dec, 2008 @ 19:00 and Ends on 02 Jan, 2010 19:00 for 53 weeks
-- So 2010 Starts on 02 Jan, 2010 @ 19:00 and Ends on 01 Jan, 2011 19:00 for 52 weeks
IF @dteTemp1 = '12/26/2009 19:00:00' SET @dteTemp1 = DATEADD(day,7,@dteTemp1)
IF @dteTemp2 = '12/26/2009 19:00:00' SET @dteTemp2 = DATEADD(day,7,@dteTemp2)
-- Test if Internal Date is between dteTemp1 and dteTemp2
IF @dteTemp1 <= @dteInternal and @dteInternal < @dteTemp2
BEGIN
SET @dteBegYear = @dteTemp1
SET @dteEndYear = @dteTemp2
SET @intFYear = DATEPART(yy, DATEADD(dd,90,@dteBegYear))
BREAK
END
SET @intTemp = @intTemp + 1
END
--Fiscal Week starts Sat 19:00 to Sat 19:00
SET @dteWrkWkBeg = DATEADD(d,-1, @dteInternalSun) + ' ' + '19:00:00'
SET @dteWrkWkEnd = DATEADD(d,+6, @dteInternalSun) + ' ' + '19:00:00'
SET @intWWks = DATEDIFF(wk, @dteBegYear, @dteInternalSun)
--Fiscal Month ( 52 week years will use 4/4/5 and 53 week years will use 445/445/445/545 )
IF @intWWks <= 52 SET @strMonth = 'DEC'
IF @intWWks <= 47 SET @strMonth = 'NOV'
IF @intWWks <= 43 SET @strMonth = 'OCT'
IF @intWWks <= 39 SET @strMonth = 'SEP'
IF @intWWks <= 34 SET @strMonth = 'AUG'
IF @intWWks <= 30 SET @strMonth = 'JUL'
IF @intWWks <= 26 SET @strMonth = 'JUN'
IF @intWWks <= 21 SET @strMonth = 'MAY'
IF @intWWks <= 17 SET @strMonth = 'APR'
IF @intWWks <= 13 SET @strMonth = 'MAR'
IF @intWWks <= 8 SET @strMonth = 'FEB'
IF @intWWks <= 4 SET @strMonth = 'JAN'
IF DATEDIFF(wk,@dteBegYear,@dteEndYear) = 53
BEGIN
IF @intWWks <= 53 SET @strMonth = 'DEC'
IF @intWWks <= 48 SET @strMonth = 'NOV'
IF @intWWks <= 44 SET @strMonth = 'OCT'
END
--Qtrs ( 1-13/14-26/27-39/40-52 or 1-13/14-26/27-39/40-53 )
IF @intWWks <= 53 SET @strQtr = 'Q4'
IF @intWWks <= 39 SET @strQtr = 'Q3'
IF @intWWks <= 26 SET @strQtr = 'Q2'
IF @intWWks <= 13 SET @strQtr = 'Q1'
Print 'Fiscal Year Summary Info'
PRINT ' For target date of: ' + convert(varchar, @MyDatetime, 101) + ' ' + SUBSTRING(convert(varchar, @MyDatetime, 113),13,8)
PRINT ' The Fiscal Year Starts on: ' + convert(varchar, @dteBegYear, 101) + ' ' + SUBSTRING(convert(varchar, @dteBegYear, 113),13,8)
PRINT ' The Fiscal Year Ends on: ' + convert(varchar, @dteEndYear, 101) + ' ' + SUBSTRING(convert(varchar, @dteEndYear, 113),13,8)
PRINT ' The Fiscal Year Is: ' + convert(varchar, DATEPART(year,DATEADD(day,90,@dteBegYear)))
PRINT ' This many Fiscal Weeks: ' + convert(varchar, DATEDIFF(wk,@dteBegYear,@dteEndYear))
PRINT ' '
PRINT ' This date is in Fiscal Year: ' + convert(varchar, @intFYear)
PRINT ' This date is in Fiscal Week: ' + convert(varchar, @intWWks)
PRINT ' This date is in Fiscal Month:' + @strMonth
PRINT ' This date is in Fiscal Qtr: ' + @strQtr
-- Insert statements for procedure here
SELECTconvert(varchar, @MyDatetime, 101) + ' ' + SUBSTRING(convert(varchar, @MyDatetime, 113),13,8) AS TgtDate,
convert(varchar, @FixedPayDay, 101) As RefDate,
convert(varchar, @dteRefSunday, 101) As RefSunDate,
@strRefSWShft AS RefSwingShifts,
convert(varchar, @dteInternalSun, 101) AS CrntSun,
@strEvenOdd AS EvenOddWks,
@str1Shft AS NightShifts,
@str2Shft AS DayShifts,
convert(varchar, @dteInternal, 101) + ' ' + SUBSTRING(convert(varchar, @dteInternal, 113),13,8) AS InternalDate,
'<->' AS FiscalInfo,
convert(varchar, @dteInternal, 101) AS FiscalDate,
@intFYear AS FiscalYear,
RIGHT('00' + convert(varchar,@intWWks),2) AS WWk,
RIGHT(convert(varchar, @intFYear),2) + RIGHT('00' + convert(varchar,@intWWks),2) AS YYWW,
convert(varchar, @intFYear) + RIGHT('00' + convert(varchar,@intWWks),2) AS YYYYWW,
@strDayofWeek AS WeekDay,
@strISShift AS ISShift,
@strCrntShiftID AS CrntShift,
SUBSTRING(@strWrkDyShifts,1,1) AS Shift_1,
SUBSTRING(@strWrkDyShifts,2,1) AS Shift_2,
@strWrkDyShifts AS Shifts,
convert(varchar, @dteWrkWkBeg, 101) + ' ' + SUBSTRING(convert(varchar, @dteWrkWkBeg, 113),13,8) AS BegWWk,
convert(varchar, @dteWrkWkEnd, 101) + ' ' + SUBSTRING(convert(varchar, @dteWrkWkEnd, 113),13,8) AS EndWWk,
convert(varchar, @dteWrkDayBeg, 101) + ' ' + SUBSTRING(convert(varchar, @dteWrkDayBeg, 113),13,8) AS BegWDay,
convert(varchar, @dteWrkDayEnd, 101) + ' ' + SUBSTRING(convert(varchar, @dteWrkDayEnd, 113),13,8) AS EndWDay,
convert(varchar, @dteShiftBeg, 101) + ' ' + SUBSTRING(convert(varchar, @dteShiftBeg, 113),13,8) AS BegShift,
convert(varchar, @dteShiftEnd, 101) + ' ' + SUBSTRING(convert(varchar, @dteShiftEnd, 113),13,8) AS EndShift,
convert(varchar, @dtePayPerBeg, 101) + ' ' + SUBSTRING(convert(varchar, @dtePayPerBeg, 113),13,8) AS PayPerBegin,
convert(varchar, @dtePayPerEnd, 101) + ' ' + SUBSTRING(convert(varchar, @dtePayPerEnd, 113),13,8) AS PayPerEnd,
convert(varchar, @dteBegYear, 101) + ' ' + SUBSTRING(convert(varchar, @dteBegYear, 113),13,8) as BegFiscalYear,
convert(varchar, @dteEndYear, 101) + ' ' + SUBSTRING(convert(varchar, @dteEndYear, 113),13,8) as EndFiscalYear
END
January 24, 2012 at 2:50 pm
barry.pettis (1/24/2012)
I'm at a loss tho... If I have a table that has a "DATETIME" column... How do I tie in this procedure? Should this be a function? I could want anywhere from 1 to 21 different pieces of information. Help would sure be appreciated.
Yes, it's a function. You want a UDF, or user defined function. However, you need to be aware that inline functions like this are very chewy. iTVFs (Inline Table Value Functions) are usually your best bet, and mTVFs can be incredibly painful.
In a case like this, where all of this calculation and manipulation needs to occur but is always the same depending on the date, a calendar table is probably your best approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 3:07 pm
Evil Kraig F (1/24/2012)
Yes, it's a function. You want a UDF, or user defined function. However, you need to be aware that inline functions like this are very chewy. iTVFs (Inline Table Value Functions) are usually your best bet, and mTVFs can be incredibly painful.In a case like this, where all of this calculation and manipulation needs to occur but is always the same depending on the date, a calendar table is probably your best approach.
Kraig,
Not fully versed in SQL terminology like "Chewy"
Was staying away from a table as I didn't ( at this time and with my level of expertise ) want another table to keep updated manually.
So this is a Function and iTVF is the way to go. I'll look to see how to change from this to a function.. any pointers?
Then ... a quickie how to use it? Maybe??
January 24, 2012 at 4:43 pm
barry.pettis (1/24/2012)
Evil Kraig F (1/24/2012)
Yes, it's a function. You want a UDF, or user defined function. However, you need to be aware that inline functions like this are very chewy. iTVFs (Inline Table Value Functions) are usually your best bet, and mTVFs can be incredibly painful.In a case like this, where all of this calculation and manipulation needs to occur but is always the same depending on the date, a calendar table is probably your best approach.
Kraig,
Not fully versed in SQL terminology like "Chewy"
😀 Sorry, that's just English for the technical term: Really damned painfully expensive in computation and memory.
Was staying away from a table as I didn't ( at this time and with my level of expertise ) want another table to keep updated manually.
Ah, and that's the beauty of it. Setup a job say once a year to fill in the next year (or two years out, or whatever you need). There's a lot of walkthroughs on calendar table building out there.
So this is a Function and iTVF is the way to go. I'll look to see how to change from this to a function.. any pointers?
iTVF means that it's single statement, IE: a single select statement. mTVF can't be optimized inline and thus have a much higher cost. Because of the volume of calculations you're doing here, it'll be mTVF.
Then ... a quickie how to use it? Maybe??
Something like the following:
SELECT t.*, f.FiscalValue
FROM
tblA AS t
CROSS APPLY ( FiscalFunction( t.DateTimeField)) AS f
If you lookup crossapply and funtions, they'll have a number of examples you can use. When I get a chance I'll go through your proc and see what can be done to help optimize a permanent table you could run with.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 5:11 pm
An example of a persisted table:
CREATE TABLE BarrysCalendar
(ReferenceDate DATETIME NOT NULL,
IsNightShiftBIT NOT NULL,
ShiftStartAS CASE IsNightShift
WHEN 0 /*Day*/ THEN DATEADD( hh, 6, ReferenceDate)
ELSE DATEADD( hh, -5, ReferenceDate) -- Start at 7PM yesterday
END PERSISTED NOT NULL,
ShiftEndAS CASE IsNightShift
WHEN 0 /*Day*/ THEN DATEADD( hh, 19, ReferenceDate)
ELSE DATEADD( hh, 6, ReferenceDate)
END PERSISTED NOT NULL,
FixedPayDay DATETIME NULL,
RefSunday AS dateadd(wk, datediff(wk, 0, FixedPayDay), -1) PERSISTED NOT NULL,
dteInternalSun AS
convert(varchar, dateadd(wk, datediff(wk, 0, ReferenceDate), -1), 101)
PERSISTED NOT NULL,
--strDayOfWeek AS LEFT( DATENAME( WEEKDAY, ReferenceDate), 3) PERSISTED NOT NULL,
dteWrkDayBeg AS CASE IsNightShift
WHEN 0 THEN DATEADD( hh, 7, ReferenceDate)
ELSE DATEADD( hh, -5, ReferenceDate)
END PERSISTED NOT NULL,
dteWrkDayEnd AS CASE IsNightShift
WHEN 0 THEN DATEADD( hh, 19, ReferenceDate)
ELSE DATEADD( hh, 7, ReferenceDate)
END PERSISTED NOT NULL
)
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120124', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120123', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120122', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120121', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120120', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120119', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120118', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120117', 0, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120105', 0, '20120115')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120102', 0, '20120115')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120124', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120123', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120122', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120121', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120120', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120119', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120118', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120117', 1, '20120131')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120105', 1, '20120115')
INSERT INTO BarrysCalendar (ReferenceDate, IsNightShift, FixedPayDay) VALUES ('20120102', 1, '20120115')
SELECT * FROM BarrysCalendar
DATENAME is apparently non-deterministic, I'll have to see what's going on there, but in most cases you should be able to convert this process to almost all of what you need. From there, you'll just have to fill in the ReferenceDate and FixedPayDay when you need it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 5:16 pm
Evil Kraig F (1/24/2012)
DATENAME is apparently non-deterministic, I'll have to see what's going on there
Because it might return different results from the same input depending on the SET options Language, DateFormat and DateFirst and also the default language of the login.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 5:19 pm
GilaMonster (1/24/2012)
Evil Kraig F (1/24/2012)
DATENAME is apparently non-deterministic, I'll have to see what's going on thereBecause it might return different results from the same input depending on the SET options Language, DateFormat and DateFirst and also the default language of the login.
Ah! Good point, I'd forgotten. Could do a workaround from day 0 and using the modulo with a case function to enforce determinism I guess.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 5:40 pm
Thanks for all... I'll look at making a calendar table.
My only issue with that is then I'm bound to lookup only dates in the table.. I can't throw in a date and see what values are spit back.
If I have more questions.... I'll be back.
January 25, 2012 at 6:17 am
Ok... I'll go the table route... I'm seeing more information that I could add.
So from the existing result of this "PROC" it always returns a single record... How can I use what I have to create the base table?
Then after the template is made I can add the auto number ID and then eventually other columns.
Thanks
January 25, 2012 at 11:37 am
barry.pettis (1/25/2012)
Ok... I'll go the table route... I'm seeing more information that I could add.So from the existing result of this "PROC" it always returns a single record... How can I use what I have to create the base table?
Then after the template is made I can add the auto number ID and then eventually other columns.
Thanks
Join your DATETIME field in the table you're reviewing to this table, similar to:
FROM
tblA AS a
JOIN
Calendar AS Cal
ON a.DateTimeField >= Cal.ShiftStart AND a.DateTimeField < Cal.ShiftEnd
That'll bring you to the exact shift/day you want to reference.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 26, 2012 at 7:13 am
Here's one way to do it. Try this code and modify to fit your needs.
CREATE Table DimDate
(DateKeyintNOT NULL
CONSTRAINT PK_DimDate_On_DateKey PRIMARY KEY CLUSTERED,
DateToUseDatetimeNULL,
CalYrMthintNULL,
CalYrintNULL,
CalMthintNULL,
CalDayintNULL,
EngDayOfWkvarchar(10)NULL,
DayNumOfWkintNULL,
DayNumOfYrintNULL,
WeekNumOfYrintNULL,
EngMonthNamevarchar(12)NULL,
CalQtrintNULL,
CalYrQtrintNULL,
CalYrHalfintNULL,
CalYrSemesterintNULL,
FiscalQtrintNULL,
FiscalYrintNULL,
FiscalYrQtrintNULL,
FiscalYrHalfintNULL,
FiscalSemesterintNULL
)
GO
CREATE NONCLUSTERED INDEX NI_DimDate_on_DateToUse ON DimDate(DateToUse)
GO
CREATE NONCLUSTERED INDEX NI_DimDate_on_CalYrMth ON DimDate(CalYrMth)
GO
CREATE NONCLUSTERED INDEX NI_DimDate_on_CalMth ON DimDate(CalMth)
GO
CREATE NONCLUSTERED INDEX NI_DimDate_on_CalYrQtr ON DimDate(CalYrQtr)
GO
CREATE NONCLUSTERED INDEX NI_DimDate_on_FiscalYrQtr ON DimDate(FiscalYrQtr)
GO
/* ============================================================================================== */
/* */
/* Create the records for the Date Dimension table */
/* */
/* ============================================================================================== */
DECLARE @dt1 datetime-- (start date)
DECLARE @dt2 datetime-- (end date)
DECLARE @dt3 datetime-- (looping variable)
DECLARE @dkeyint-- (integer version of dt3, in format YYYYMMDD)
DECLARE @yrmthint-- CalYrMth
DECLARE @yrint-- CalYr
DECLARE @mthint-- CalMth
DECLARE @dayofmthint-- CalDay
DECLARE @calyrsemint-- CalYrSemester
DECLARE @dayofwkvarchar(10)-- EngDayOfWk
DECLARE @qtrint-- CalQtr
DECLARE @yrqtrint-- CalYrQtr
DECLARE @yrhalf int-- CalYrHalf
DECLARE @fisqtrint-- FiscalQtr
DECLARE @fisyrqtrint-- FiscalYrQtr
DECLARE @fissemint-- FiscalSemester
DECLARE @fisyrhalfint-- FiscalYrHalf
DECLARE @fisyrint-- FiscalYr
DECLARE @daynumofwkint-- DayNumOfWk
DECLARE @daynumofyrint-- DayNumOfYr
DECLARE @weeknumofyrint-- WeekNumOfYr
DECLARE @engmonthnamevarchar(12)-- EngMonthName
SET @dt1 = CONVERT(datetime,'01/01/2000')--start date
SET @dt2 = CONVERT(datetime,'12/31/2030')--end date
SET @dt3 = @dt1
WHILE @dt3 <= @dt2
BEGIN
SET @yrmth = (DATEPART(year,@dt3) * 100) + DATEPART(month,@dt3)
SET @yr = DATEPART(year,@dt3)
SET @mth = DATEPART(month,@dt3)
SET @qtr = DATEPART(quarter,@dt3)
SET @dayofmth = DATEPART(DAY,@dt3)
SET @yrqtr = (DATEPART(year,@dt3) * 100) + DATEPART(quarter,@dt3)
SET @dkey = (@yr * 10000) + (@mth * 100) + @dayofmth
SET @yrhalf =Case @mth
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 2
WHEN 8 THEN 2
WHEN 9 THEN 2
WHEN 10 THEN 2
WHEN 11 THEN 2
WHEN 12 THEN 2
END
SET @dayofwk = DATENAME(dw,@dt3)
SET @fisqtr = Case @mth
WHEN 1 THEN 3
WHEN 2 THEN 3
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 4
WHEN 6 THEN 4
WHEN 7 THEN 1
WHEN 8 THEN 1
WHEN 9 THEN 1
WHEN 10 THEN 2
WHEN 11 THEN 2
WHEN 12 THEN 2
END
SET @fisyr = Case @fisqtr
WHEN 1 THEN @yr+1
WHEN 2 THEN @yr+1
WHEN 3 THEN @yr
WHEN 4 THEN @yr
END
SET @fisyrqtr = (@fisyr * 100) + @fisqtr
SET @fissem = Case @mth
WHEN 1 THEN 2
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 3
WHEN 5 THEN 3
WHEN 6 THEN 3
WHEN 7 THEN 1
WHEN 8 THEN 1
WHEN 9 THEN 1
WHEN 10 THEN 1
WHEN 11 THEN 2
WHEN 12 THEN 2
END
SET @fisyrhalf = Case @mth
WHEN 1 THEN 2
WHEN 2 THEN 2
WHEN 3 THEN 2
WHEN 4 THEN 2
WHEN 5 THEN 2
WHEN 6 THEN 2
WHEN 7 THEN 1
WHEN 8 THEN 1
WHEN 9 THEN 1
WHEN 10 THEN 1
WHEN 11 THEN 1
WHEN 12 THEN 1
END
SET @daynumofwk = DATEPART(dw,@dt3)
SET @daynumofyr = DATEPART(dy,@dt3)
SET @weeknumofyr = DATEPART(wk,@dt3)
SET @engmonthname = DATENAME(mm,@dt3)
SET @calyrsem = Case @mth
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 2
WHEN 7 THEN 2
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 3
WHEN 11 THEN 3
WHEN 12 THEN 3
END
INSERT INTO DimDate
(DateKey, DateToUse, CalYrMth, CalYr, CalMth, CalDay, EngDayOfWk, DayNumOfWk, DayNumOfYr, WeekNumOfYr, EngMonthName, CalQtr,
CalYrQtr, CalYrHalf, CalYrSemester, FiscalQtr, FiscalYr, FiscalYrQtr, FiscalYrHalf, FiscalSemester)
VALUES
(@dkey, @dt3, @yrmth, @yr, @mth, @dayofmth,@dayofwk, @daynumofwk,@daynumofyr,@weeknumofyr,@engmonthname,@qtr,
@yrqtr, @yrhalf, @calyrsem, @fisqtr, @fisyr, @fisyrqtr, @fisyrhalf, @fissem)
SET @dt3 = dateadd(day,1,@dt3)
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply