http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/%5B/url%5D

If your Tally table is big enough, you can do without the days & hours combination and go for hours directly.

SELECTCOUNT(*)

FROMmaster..spt_values AS h

WHEREh.Type = 'p'

AND h.Number < DATEDIFF(HOUR, @Date1, @Date2)

AND...

Sure. No problems. This is not one of the most efficient queries, but it gets the work done and it was fast to write.

DECLARE@Date1 DATETIME,

@Date2 DATETIME

SELECT@Date1 = '20080610',

@Date2 = '20080711'

SELECTCOUNT(*)

FROMmaster..spt_values...

Excellent!

Nice work and faster than my suggestion.

Michaels improved version of Jeffs original query also works with dates only (time set to 00:00:00).

This is a tool I'll keep. Thanks.

I found a...

If speed it of importance, you may not want to use SET ANSI_WARNINGS OFF.

Why? Beacuse all settings altering the current connection triggers a recompile.

http://www.novicksoftware.com/Articles/minimizing-stored-procedure-recompilation-page2.htm

Quote from Experts-Exchange "performance killer"

In SQL 2000...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Somewhat shorter code

SELECTCOUNT(*)

FROMmaster..spt_values AS d

INNER JOINmaster..spt_values AS h ON h.Type = 'p'

WHEREd.Type = 'p'

AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)

AND h.Number BETWEEN 0 AND 23

AND DATEADD(HOUR, h.Number, d.Number + @Date1) <...

Here is something that only uses the "JOIN" once.

INSERT[Log]

SELECTy.theDateTime,

y.theSP,

y.theNULL,

CASE

WHEN x.theIdentifier = 0 THEN 'Record Not Found'

ELSE CAST(x.theCount AS VARCHAR(12)) + ' calculation done.'

END AS theMessage

FROM(

SELECTGETDATE() AS theDateTime,

#pfm.sp AS theSP,

NULL AS...

Thank you. You admit they produce the same result.

Now we can move forward.

Michael, what if starttime and endtime is NOT on weekend as op wrote originally?

Jeff, what if starttime AND endtime is ON weekend as op wrote originally?

DECLARE@Date1 DATETIME,

@Date2 DATETIME

SELECT@Date1 = '20061018...

Try this first to see if it produces the wanted result, THEN we can talk about optimization.

INSERT[Log]

SELECTGETDATE(),

#pfm.sp,

NULL,

CAST(COUNT(#r1.sp) AS VARCHAR(12)) + ' calculation done.'

FROM#pfm

LEFT JOIN#r1 ON #r1.sp = #pfm.sp

WHERE#pfm.Flag = 'S'

GROUP...

Wait a minute... Your title is Senior Software Engineer and this is the zillionth question you have?

I don't think DBCC PINTABLE is available in SQL Server 2005, so that approach is a dead end.

And with integers as parameter values

SELECTDATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastOfPreviousMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) AS LastOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), 0) AS FirstOfNextMonth

SELECTDATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS LastOfPreviousMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') AS FirstOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '18991231', GETDATE()), '18991231') AS LastOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '18991231', GETDATE()), '19000101') AS FirstOfNextMonth