SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary Table


Temporary Table

Author
Message
daverugz
daverugz
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
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
Kent Waldrop
Kent Waldrop
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 467
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:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

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.
MannySingh
MannySingh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1129 Visits: 787
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
daverugz
daverugz
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
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.
Kent Waldrop
Kent Waldrop
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 467
Amazing; obviously not your fault, but amazing.
daverugz
daverugz
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
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
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 Visits: 4375
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)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 9730
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62464 Visits: 19102
I love the argument a backup is needed?

I think your instructors are perhaps lacking some understanding of how SQL Server should be run.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dolly-829405
dolly-829405
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 23
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search