Temporary Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Temporary Table

Temporary Table

Rate Topic

Display Mode

Topic Options

Author

Message

daverugz

Posted Tuesday, July 08, 2008 10:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 3:03 PM
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

Post #530192

Kent Waldrop

Posted Tuesday, July 08, 2008 10:55 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:30 AM
Points: 480, Visits: 461

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.

Post #530219

MannySingh

Posted Tuesday, July 08, 2008 11:00 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646, Visits: 729

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

Post #530224

daverugz

Posted Tuesday, July 08, 2008 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 3:03 PM
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.

Post #530294

Kent Waldrop

Posted Tuesday, July 08, 2008 12:25 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:30 AM
Points: 480, Visits: 461

Amazing; obviously not your fault, but amazing.

Post #530298

daverugz

Posted Thursday, July 24, 2008 9:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 3:03 PM
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

Post #540299

sgmunson

Posted Friday, July 25, 2008 7:00 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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)
Smile

Weight Loss Tips

Post #540868

GSquared

Posted Friday, July 25, 2008 10:28 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

daverugz (7/8/2008)

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

Post #541120

Steve Jones - SSC Editor

Posted Friday, July 25, 2008 10:55 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406, Visits: 13,723

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

Post #541154

dolly-829405

Posted Saturday, July 26, 2008 12:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 06, 2008 6:39 AM
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

Post #541361

« Prev Topic | Next Topic »

Permissions