T-SQL Help needed (Date / Time)

Question

T-SQL Help needed (Date / Time)

gareth.thompson

SSC Enthusiast

Points: 123
More actions
July 19, 2011 at 9:00 am

#241670

Hi there Guys / Girls.
Im having a issue here, I have the following defined in a Script (report) that pulls data for the last 30 Days.
What I would like to do is add a restriction to exclude SAT and SUN and only pull between times 8AM and 5PM.
I just cant seem to win with this one.
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
Set @StartDate = dateadd(day, datediff(day, 0 ,getdate())-30, 0)
SET @EndDate = getdate()
set nocount on
create table #tmpJoin (fromDate datetime, toDate datetime)
insert into #tmpJoin values(@StartDate,@EndDate)
set nocount off
SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95
FROM Nodes
INNER JOIN Interfaces
ON (Nodes.NodeID = Interfaces.NodeID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_IN
ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_OUT
ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
INNER JOIN
(
SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate
) AS AA
) as RESULT_MAX
ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
ORDER BY NodeName, Interface_Caption
Drop table #tmpJoin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

The only way i can think of to make that work is to use a Calendar table, and also require a table scan because you'd have to check each datetime to be between 8am and 5pm.

those functions to do date add/datediff will require a tablescan....that'll slow things down a bit.

a rough example:

SELECT DISTINCT A.InterfaceID

FROM dbo.InterfaceTraffic A

INNER JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <= #tmpJoin.toDate

INNER JOIN TallyCalendar On DATEADD(dd, DATEDIFF(dd,0,A.datetime), 0) = TallyCalendar.TheDate

WHERE TallyCalendar.IsWeekDay = 1 --

AND A.DateTime BETWEEN DATEADD(hh,8,DATEADD(dd, DATEDIFF(dd,0,A.datetime), 0)) --8am of the date

AND DATEADD(hh,17,DATEADD(dd, DATEDIFF(dd,0,A.datetime), 0))--5pm/17:00 of the date

if you don't already have one, here is a link to the script i'm using for a TallyCalendar.

TallyCalendar_Complete_With_DST.txt

My Version has dates from SQL Zero date of 1900-01-01 to 100 years + of today(getdate() when you run the script)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kramaswamy SSCoach Points: 18181 More actions · Answer 2

found this, might be useful for you:

WHERE ((DATEPART(dw, [Your_Date]) + @@DATEFIRST) % 7) NOT IN (0, 1)

that excludes weekends from your query. the @@DATEFIRST part is necessary to adjust for your system settings

chellweg SSC Enthusiast Points: 109 More actions · Answer 3

kramaswamy (7/19/2011)
found this, might be useful for you:
WHERE ((DATEPART(dw, [Your_Date]) + @@DATEFIRST) % 7) NOT IN (0, 1)
that excludes weekends from your query. the @@DATEFIRST part is necessary to adjust for your system settings

I would agree with kramaswamy. I have used DATEPART before to count hours worked by week and it works rather well. another thing I noticed in your SQL is that there is really no need to create the tmpJoin table, you can just place you @DateFrom @DateThru variables into the where clause of each subquery.

gareth.thompson SSC Enthusiast Points: 123 More actions · Answer 4

Good Day all thanks to everyone's assist here, Looking at how to get the thing working 😛

And CELKO thanks for the comments, To maybe clarify why some of these things are written the way they are this script is pulling from multiple Cisco devices (network devices) utilizing SQL databases, the scripting happens to be exceptionally close to the MSSQL 2008 TSQL scripting. very few differances. Its why i thought to post in here, though it may have been a bad idea in retrospect.

Thanks though for all the insight, I'm still a, to coing a term from gamming, ULTA NOOB when it comes to T-SQL scripting and sql in general, but I am learning slowly. 🙂