Date Span - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date Span

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-06-07 : 14:56:41

I am looking for good articles on dealing with Date Spans in SQL.

Here is what I have. I am giving this info so that someone might be able to recommend a good resource for this kind of thing.

I have to calculate availability for multiple pieces of equipment.
I have a stored procedure for the report which has a @StartDate and @EndDate Parameter. Datediff(n,@StartDate, @EndDate) determines the total of minutes the equipment should be available for the month.

The equipment has a In Service and out Of Service Date, I verify that the equipment was in service for the date span selected. I use a temporary table with an Identity(1,1) and a while loop to subtract any minutes during the period that the equipment either was not in service yet or already out of service.

Here is the complicated part, I also have a one to many relationship between the equipment table and a schedule table. If the equipment is scheduled down for maintenence it can't be held accountable for those minutes. an Entry is made in the schedule table for each scheduled maintenence item. There can be more than one scheduled maintenence for the equipment in a reporting period. I need to know how to deal with that.

Thanks,

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-07 : 15:03:51

quote:
Originally posted by TSQLMan

I am looking for good articles on dealing with Dates in SQL.

I've never really tried dealing with dates using SQL. In fact, I normally try to avoid telling my dates that I am a DBA. If you don't, then you have to explain what that means, which is usually pretty tough.

If you do go down that foolish path, chances are you'll get that deer in the headlights look. if you do, well you might as well just call it a night (and a cab).

-ec

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-06-07 : 15:32:59

quote:
Originally posted by eyechart

I've never really tried dealing with dates using SQL. In fact, I normally try to avoid telling my dates that I am a DBA. If you don't, then you have to explain what that means, which is usually pretty tough.

If you do go down that foolish path, chances are you'll get that deer in the headlights look. if you do, well you might as well just call it a night (and a cab).

-ec

Not what I was looking for, but good advice non the less.

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-07 : 16:25:20

Well, for your 100th post(hurray)...post the DDL of the tables, some sample data, and what the expected results should be...

Sure we could figure something out.....

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-06-07 : 16:48:57

Here it is. The DDL and the sproc, I am working on. I have to add, this is not my database, it was an inheritance.

--DDL for Tables

CREATE TABLE [Tbl_Codes_Belt] (
[BeltCode] [int] NOT NULL ,
[BeltName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DoNOTshowONlist] [bit] NOT NULL ,
[BeltStartDateTime] [smalldatetime] NOT NULL ,
[BeltEndDateTime] [smalldatetime] NULL ,
CONSTRAINT [PK_Tbl_Codes_Belt] PRIMARY KEY CLUSTERED
(
[BeltCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO

--

CREATE TABLE [Schedule] (
[ScheduleID] [int] NOT NULL ,
[BeltCode] [int] NOT NULL ,
[ScheduleStartDate] [datetime] NULL ,
[ScheduleEndDate] [datetime] NULL ,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ScheduleID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Schedule_Tbl_Codes_Belt] FOREIGN KEY
(
[BeltCode]
) REFERENCES [Tbl_Codes_Belt] (
[BeltCode]
)
) ON [PRIMARY]
GO

---Sproc for Reporting, what I have so far.

ET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE GetBeltAvailability

@START DATETIME,
@END DATETIME

AS

--Create the temporary table to store belt data, so that an incremental ID can be added for while
--loop, this keeps you from having to use a cursor.

CREATE TABLE #TEMPBELT
(
TID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
BELTCODE INT,
BELTNAME VARCHAR(55),
DURATION INT,
BELTSTARTDATE DATETIME,
BELTENDDATE DATETIME
)

--DECLARE AND SET KNOWN VARIABLES

DECLARE @Y INT
DECLARE @BELTCOUNT INT
DECLARE @DURATION INT
DECLARE @TEMPDURATION INT
DECLARE @BELTSTARTDATE DATETIME
DECLARE @BELTENDDATE DATETIME

SET @BELTCOUNT = (SELECT COUNT(BELTCODE) AS BELTCOUNT FROM TBL_CODES_BELT)
SET @DURATION = DATEDIFF(N,@START,@END)
SET @Y = 0

--INSERT TBL_CODES_BELT DATA INTO TEMP TABLE WITH INDEX FOR WHILE LOOP

INSERT INTO #TEMPBELT
(BELTCODE, BELTNAME,BELTSTARTDATE,BELTENDDATE)
SELECT BELTCODE, BELTNAME, STARTDATE =

CASE
WHEN BELTSTARTDATETIME > 0 THEN BELTSTARTDATETIME
WHEN BELTSTARTDATETIME <= 0 THEN 0
ELSE 0
END
, ENDDATE =
CASE
WHEN BELTENDDATETIME >0 THEN BELTENDDATETIME
WHEN BELTENDDATETIME <=0 THEN 0
ELSE 0
END

FROM TBL_CODES_BELT

--LABEL FOR GOTO TO ITERATE THROUGH BELTS

BELTLOOP:

SET @Y = @Y + 1

WHILE @Y <= @BELTCOUNT
BEGIN

--TEMPORARILY STORE DURATION BETWEEN THE @START (REPORT PERIOD START) AND @END (REPORT PERIOD END)

SET @TEMPDURATION = (SELECT DATEDIFF(N,BELTENDDATE,BELTSTARTDATE)
FROM #TEMPBELT WHERE TID = @Y)

--SET TBL_CODES_BELT START DATE, AND END DATE TO BEGIN DURATION CALCULATION. DURATION IS THE
--AMOUNT OF MINUTES THE BELT IS EXPECTED TO OPERATE DURING HTE REPORT PERIOD

SET @BELTSTARTDATE = (SELECT BELTSTARTDATE FROM #TEMPBELT WHERE TID = @Y)
SET @BELTENDDATE = (SELECT BELTENDDATE FROM #TEMPBELT WHERE TID = @Y)

--THE FOLLOWING IF ELSE STATEMENTS DETERMINE BASED ON DATE THE PROPER CALCULATION OF THE EXPECTED
--BELT UPTIME

IF @BELTSTARTDATE >= @START AND @BELTSTARTDATE <= @END AND @BELTENDDATE >= @START AND @BELTENDDATE <= @END
BEGIN

set @DURATION = 0
SET @DURATION = DATEDIFF(N,@BELTSTARTDATE, @BELTENDDATE)
PRINT CHAR(@DURATION)

UPDATE #TEMPBELT

SET DURATION = @DURATION

WHERE TID = @Y

GOTO BELTLOOP
END

ELSE

IF @BELTSTARTDATE < @START AND @BELTENDDATE >= @START AND @BELTENDDATE <= @END
BEGIN
SET @DURATION = 0
SET @DURATION = DATEDIFF(N,@START,@BELTENDDATE)

UPDATE #TEMPBELT

SET DURATION = @DURATION

WHERE TID = @Y

GOTO BELTLOOP
END

ELSE

IF @BELTSTARTDATE < @START AND @BELTENDDATE > @END
BEGIN
SET @DURATION = 0
SET @DURATION = DATEDIFF(N,@START,@END)

UPDATE #TEMPBELT

SET DURATION = @DURATION

WHERE TID = @Y

GOTO BELTLOOP
END

ELSE

IF @BELTSTARTDATE <= @START AND @BELTENDDATE <= @END
BEGIN
SET @DURATION = 0
SET @DURATION = DATEDIFF(N,@START,@BELTENDDATE)

UPDATE #TEMPBELT

SET DURATION = @DURATION

WHERE TID = @Y

GOTO BELTLOOP
END

ELSE

IF @BELTSTARTDATE >= @START AND @BELTENDDATE >= @END
BEGIN
SET @DURATION = 0
SET @DURATION = DATEDIFF(N,@BELTSTARTDATE,@END)

UPDATE #TEMPBELT

SET DURATION = @DURATION

WHERE TID = @Y

GOTO BELTLOOP
END

END
SELECT BELTCODE,BELTNAME,DURATION,BELTSTARTDATE,BELTENDDATE FROM #TEMPBELT
DROP TABLE #TEMPBELT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources