July 8, 2008 at 10:27 am
Hey people, I am kind of new to sql but i am very interested in it. I am trying to create a temporary table that will contain all workdays and also non workdays (weekends and holidays) so that i can do calculations on the fly for reporting. I think i have the theory down but i do not know how to even begin.i would like to
a)pass a start and end date
b)create the temp table which has its dates starting and ending at the dates passed above.
c)Create the functions that determine whether it is a workday or non work day
d) sum the values
d) return the result.
I am facing difficulty as i am a novice.
Please help
July 8, 2008 at 10:55 am
Go ahead and work on this as a temp table exercise; however, you might also want to consider making this a permanent table. Here is a link that discusses the use of a calendar table:
You might want to do a search at this forum for "calendar table." There are also many posts at the MSDN Transact SQL forum related to the use of a calendar table. I think Louis Davidson's blog along with many others also has useful information.
July 8, 2008 at 11:00 am
As KENT suggested, it will be easier for you if you have a permanent Calendar TABLE insted of TEMP Table.
the link KENT provided is all you need and adding to it : http://omnibuzz-sql.blogspot.com/2006/07/generating-temporary-calendar-tables.html
Maninder
www.dbanation.com
July 8, 2008 at 12:23 pm
Guys, along with one of my workmates, we had created a table and made it permanent but we were told to delete this table because someone thought it would hinder performance and also create a need for backups in the database if another change was being made. A temp table was thus what i was instructed to create. I will look at the link and see what it contains.
July 8, 2008 at 12:25 pm
Amazing; obviously not your fault, but amazing.
July 24, 2008 at 9:40 am
So i have looked at the solutions and found something usable that i have been modifying. My error occurs at the case statement in the begining. Anyone able to tell me what i am doing wrong in the case statement or the code generally?
--Calling the work function
-- I am getting the following error for this step: Msg 102, Level 15,
--State 1, Line 21
--Incorrect syntax near ','.
SELECT MyDays(BeginDate, EndDate, DayCount)
CASE when DayCount =1
THEN
DATEDIFF(dd,@StartDate, @EndDate)
ELSE
SET NoCount On
--If Calendar table exists drop it
IF Object_id('#Calendar','U') IS NOT NULL
DROP TABLE #Calendar
--Create Calendar table
CREATE TABLE #Calendar (
DATE DATETIME PRIMARY KEY,
Workday BIT NOT NULL,
YEAR AS datepart(year, date),
MONTH AS datepart(month, date),
DAY AS datepart(day, date),
Weekday AS datepart(weekday, date),
CONSTRAINT ck_Date_NotiMe CHECK ( DATE = CAST(CAST(CAST(DATE AS FLOAT) AS INT) AS DATETIME) ))
GO
--Creare temporary Numbers table
DECLARE @Numbers TABLE(
num_Id INT
)
-- Fill Numbers table with difference in rows between the End date and the Start date
DECLARE @number AS INT,
@StartDate as Datetime,
@EndDate as Datetime
SET @number = 1
WHILE @number <= DATEDIFF(dd,@StartDate, @EndDate)
BEGIN
INSERT INTO @Numbers
(num_Id)
VALUES (@number)
SET @number = @number + 1
END
--add the difference in rows between the Start Date and the End Date to Calenddar table
--Set weekends as non work days
INSERT INTO #Calendar
(DATE,
Workday)
SELECT Dateadd(DAY,num_Id,'@StartDate'),
CASE
WHEN Datepart(Weekday,Dateadd(DAY,num_Id,'@StartDate')) BETWEEN 2 AND 6 THEN 1
ELSE 0
END
FROM @Numbers
GO
--update January 1st as New Years Day
UPDATE #Calendar
SET Workday = 0
WHERE MONTH = 1
AND DAY = 1
GO
--update July 4th as independance Day
UPDATE #Calendar
SET Workday = 0
WHERE MONTH = 7
AND DAY = 4
GO
--update December 25th as Christmas Day
UPDATE #Calendar
SET Workday = 0
WHERE MONTH = 12
AND DAY = 25
GO
--Create temporary table of unique years
CREATE TABLE #CalendarYear (
[Year] INT)
--insert unique years into temporary year table
INSERT INTO #CalendarYear
SELECT DISTINCT YEAR
FROM #Calendar
ORDER BY YEAR
--noncursor
-- declare all variables
DECLARE @iReturnCode INT,
@iNextRowId INT,
@iCurrentRowId INT,
@iLoopControl INT
-- Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(YEAR)
FROM #CalendarYear
-- Make sure the table has data.
IF Isnull(@iNextRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END
-- Retrieve the first row
SELECT @iCurrentRowId = YEAR
FROM #CalendarYear
WHERE YEAR = @iNextRowId
WHILE @iLoopControl = 1
BEGIN
-- update Memorial Day as non working day
UPDATE #Calendar
SET Workday = 0
WHERE DATE = (SELECT MAX(DATE)
FROM #Calendar
WHERE MONTH = 5
AND YEAR = @iCurrentRowId
AND Weekday = 2)
-- update Labor Day as non working day
UPDATE #Calendar
SET Workday = 0
WHERE DATE = (SELECT MIN(DATE)
FROM #Calendar
WHERE MONTH = 9
AND YEAR = @iCurrentRowId
AND Weekday = 2)
-- update Thanksgiving Day as non working day
UPDATE #Calendar
SET Workday = 0
WHERE DATE = (SELECT MAX(DATE)
FROM #Calendar
WHERE MONTH = 11
AND YEAR = @iCurrentRowId
AND Weekday = 5)
-- update Day after Thanksgiving Day as non working day
UPDATE #Calendar
SET Workday = 0
WHERE DATE = (SELECT MAX(DATE)
FROM #Calendar
WHERE MONTH = 11
AND YEAR = @iCurrentRowId
AND Weekday = 6)
-- Reset looping variables.
SELECT @iNextRowId = NULL
-- get the next year
SELECT @iNextRowId = MIN(YEAR)
FROM #CalendarYear
WHERE YEAR > @iCurrentRowId
-- did we get a valid next row id?
IF Isnull(@iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = YEAR
FROM #CalendarYear
WHERE YEAR = @iNextRowId
--Inserting the return values fot the statement:
END
SELECT COUNT (*)
FROM #Calendar
WHERE WorkDay=1
AND date Between '@Startdate' and '@EndDate'
DROP TABLE #CalendarYear
DROP TABLE #Calendar
RETURN
July 25, 2008 at 7:00 am
There would appear to be any of a number of problems. You start with a SELECT that appears to be calling a function, which will return a result set of some kind, and then there's a CASE statement which has a specific value in mind for DayCount = 1, but then wants to be procedural for the other situation. Somehow I think that's not going to produce what you're looking for. If you're trying to create a function or a stored procedure, you'll need a CREATE FUNCTION or CREATE PROCEDURE statement to start with, which will identify the parameters to be passed in. I would dump the initial CASE statement in favor of an IF statement. You should check the syntax for these in BOL so you can learn more about how code works. Also, you probably don't want a GO statement until the end of the procedure, as temporary variables go out of scope following one, which could cause all kinds of problems. I don't have time for more detail right now, but in short, a function has to use a RETURN statement to return a value, whereas a procedure should ultimately return a result set via a SELECT of some kind. Hope that helps.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 25, 2008 at 10:28 am
daverugz (7/8/2008)
Guys, along with one of my workmates, we had created a table and made it permanent but we were told to delete this table because someone thought it would hinder performance and also create a need for backups in the database if another change was being made. A temp table was thus what i was instructed to create. I will look at the link and see what it contains.
Create the calendar table in a separate database on the same server. I have a database I call "Common" (for common functions and data), where I keep a Numbers table and a Calendar table, as well as a few others.
That way, it doesn't end up in your main database's backups, and so on.
Performance from it will be better than other options.
If you are still required to create a temp table: Create the permanent table in another database, then create the temp table by selecting from the permanent table. Won't be quite as efficient as just using the perm table, but will be better than trying to create the temp table on-the-fly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 10:55 am
I love the argument a backup is needed?
I think your instructors are perhaps lacking some understanding of how SQL Server should be run.
July 26, 2008 at 12:40 am
Here Is Stored procedure
Assuming only sunday as holiday.
Create FUNCTION [dbo].[GetWorkingDays]
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 6 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply