Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How many more Mondays until I retire? Expand / Collapse
Author
Message
Posted Thursday, June 22, 2006 3:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

 




N 56°04'39.16"
E 12°55'05.25"
Post #289513
Posted Wednesday, July 19, 2006 2:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:09 AM
Points: 381, Visits: 332

Nice article,  alternatively...

SET DATEFIRST 1 -- force monday as the beginning of the week

DECLARE @bdy datetime,
 @RetiralAge smallint


SET @bdy='24-jul-1971'
SET @RetiralAge=65


SELECT  datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7  as "Mondays till you retire"

-Ally

Post #295418
Posted Wednesday, July 19, 2006 3:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419

True, and it works too. But maybe not that versatile?




N 56°04'39.16"
E 12°55'05.25"
Post #295426
Posted Wednesday, July 19, 2006 4:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

Calculating the number of Mondays between two dates was the subject of the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258968

My suggestion was the following:

select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

I still believe this is the fastest method to calculate the number of Mondays between two dates. On the other hand, it is not very flexible...

 

Post #295439
Posted Wednesday, July 19, 2006 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419

Yes, it works well if not more than one of the two dates already is a monday. Using this code

select (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7

reports 1 monday to me, where it should report 2 mondays.

But your query is very fast.




N 56°04'39.16"
E 12°55'05.25"
Post #295462
Posted Wednesday, July 19, 2006 6:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

No, no, use the formula above:

select datediff(d, '19000101', '20060724')/7 - datediff(d, '19000102', '20060717')/7

returns 2, i.e 2 Mondays between the 17th and the 24th.

Post #295467
Posted Wednesday, July 19, 2006 6:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419

Ok, ok ok

Still don't get proper result with date range 1899-12-29 and 1900-01-03.

Running

select datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7

gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900?




N 56°04'39.16"
E 12°55'05.25"
Post #295474
Posted Wednesday, July 19, 2006 7:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

Do you really need dates that old?

You could use

select datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7

instead. Note that

select datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7

gives you one Monday.

My formula needs two reference dates, a Monday (e.g. 1900-01-01 or 1800-01-06) and the following Tuesday (e.g. 1900-01-02 or 1800-01-07). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point

 

Post #295486
Posted Wednesday, July 19, 2006 8:13 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124

In the first code sample, I suppose that this portion should return each number between 0 and 32, right ?

SELECT a.i + b.j + c.k
FROM
 (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2) a,
 (SELECT 0 j UNION ALL SELECT 3 UNION ALL SELECT 6) b,
 (SELECT 0 k UNION ALL SELECT 12 UNION ALL SELECT 24) c
ORDER BY 1

Well... it doesn't. It generate some numbers between 0 and 32, but not all of them. The above query only generates 27 values. If you want to generate each number between 0 and 31, you can use this query:

SELECT a.i + b.j + c.k
FROM
 
(SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a,
 
(SELECT 0 j UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12) b,
 
(SELECT 0 k UNION ALL SELECT 16) c
ORDER BY 1

Razvan




Post #295517
Posted Wednesday, July 19, 2006 8:43 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124

I modified your function as follows:

CREATE FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)
RETURNS @Dates TABLE (SeqDate DATETIME
)
AS
BEGIN
DECLARE @Temp DATETIME


IF
@LowDate > @HighDate
 
SELECT @Temp = @LowDate
,
 
@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0
),
 
@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0
)
 
ELSE
 
SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0
),
 
@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0
)

INSERT
@Dates (SeqDate) VALUES (@LowDate)

DECLARE @TotalRows int, @RowCnt
int
SET @TotalRows=
1
SET @RowCnt=
1

WHILE @RowCnt > 0
BEGIN
 
INSERT @Dates (SeqDate
)
 
SELECT DATEADD(dd, @TotalRows, d.SeqDate
)
 
FROM
@Dates d
 
WHERE DATEADD(dd, @TotalRows, d.SeqDate) <=
@HighDate

 
SET @RowCnt=
@@ROWCOUNT
 
SET @TotalRows=@TotalRows+
@RowCnt
END

RETURN
END

The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this:

DECLARE @t datetime

SET
@t=GETDATE()
SELECT COUNT(*) FROM dbo.fnSeqDates('20060101','23070131'
)
PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+
' ms'

SET @t=GETDATE
()
SELECT COUNT(*) FROM dbo.MySeqDates('20060101','23070131'
)
PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).

Razvan




Post #295535
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse