How to calculate the Bi-weekly in SQL

Question

How to calculate the Bi-weekly in SQL

doss.tychicus

Old Hand

Points: 360
More actions
December 26, 2008 at 9:32 am

#96869

is there any way i can calculate bi weekly in sql
from ex:
from 1/1/2009 to 12/31/2009
I need to know all the fridays biweekly
ex 1/2/2009, 1/16/2009,1/30/2009
Thanks

Viewing 15 posts - 1 through 15 (of 49 total)

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

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

doss.tychicus (12/26/2008)
is there any way i can calculate bi weekly in sql
from ex:
from 1/1/2009 to 12/31/2009
I need to know all the fridays biweekly
ex 1/2/2009, 1/16/2009,1/30/2009
Thanks

It's actually pretty easy to do that. You already know two things... that the first Friday is on 1/2/2009 and that there can't be more than 27 biweekly Fridays in a year...

;WITH

cteTally AS

(

SELECT TOP 27

ROW_NUMBER() OVER (ORDER BY ID)-1 AS N

FROM Master.sys.SysColumns

)

SELECT DATEADD(dd,t.n*14,'20090102') AS BiFriday

FROM cteTally t

WHERE DATEADD(dd,t.n*14,'20090102') < '20100101'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 2

Jeff you are amazing ...I'm going to delete my answers ah :hehe:

but can you explain to me this t.n*14 where did you find it ???

SELECT DATEADD(dd,t.n*14,'20090102') AS BiFriday

FROM cteTally t

WHERE DATEADD(dd,t.n*14,'20090102') < '20100101'

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

doss.tychicus Old Hand Points: 360 More actions · Answer 3

doss.tychicus

Old Hand

Points: 360

December 26, 2008 at 10:18 am

#917855

Thanks Jeff . that's really cool

Dugi SSCoach Points: 17998 More actions · Answer 4

Dugi (12/26/2008)
Jeff you are amazing ...I'm going to delete my answers ah :hehe:
but can you explain to me this t.n*14 where did you find it ???
SELECT DATEADD(dd,t.n*14,'20090102') AS BiFriday
FROM cteTally t
WHERE DATEADD(dd,t.n*14,'20090102') < '20100101'

ok I find it ....t CTE Tab, n is coming form ROw_Number() * 14 is BiWeekly!

Nice tricky calculation!!!

Really Amazing!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

doss.tychicus (12/26/2008)
Thanks Jeff . that's really cool

You bet, Doss... thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

Dugi (12/26/2008)
Dugi (12/26/2008)
Jeff you are amazing ...I'm going to delete my answers ah :hehe:
but can you explain to me this t.n*14 where did you find it ???
SELECT DATEADD(dd,t.n*14,'20090102') AS BiFriday
FROM cteTally t
WHERE DATEADD(dd,t.n*14,'20090102') < '20100101'
ok I find it ....t CTE Tab, n is coming form ROw_Number() * 14 is BiWeekly!
Nice tricky calculation!!!
Really Amazing!

Bingo! 😀 Works the same as if I'd used an actual Tally table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

One more query

select dateadd(day,number,'20090102') as BiFriday from master..spt_Values

where type='p' and dateadd(day,number,'20090102')<'20100101' and number%14=0

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 8

For anyone that reads this that may still be using SQL Server 2000, the system table called spt_Values that Madhivanan used has a bunch of different things in it depending on the "Type" column. For example, it has a list of numbers, much like a Tally table does, where the "Type" column contains a "P".

In SQL Server 2000, Type "P" has numbers from 0 to 255

In SQL Server 2005, Type "P" has numbers from 0 to 2047

In SQL Server 2000, Type "P" doesn't have enough numbers to do the fine solution that Madhivanan suggests. So, you'd need to change things just a bit to get something similar to work in SQL Server 2000...

select dateadd(day,number*14,'20090102') as BiFriday from master..spt_Values

where type='p' and number < (DATEDIFF(dd,'20090102','20100101')+1)/14

The reason why that works is it uses the "Number" column as the number of bi-weekly Fridays instead of the number of days. There are only 26 bi-weekly Fridays in 2009 so it fits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 9

Just for grins, here's the CTE version without TOP...

;WITH

cteTally AS

(

SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS N

FROM Master.sys.SysColumns

)

SELECT DATEADD(dd,(t.n)*14,'20090102') AS BiFriday

FROM cteTally t

WHERE t.n < (DATEDIFF(dd,'20090102','20100101')+1)/14

Of course, the Tally table version is still faster than the CTE version and works in 2k as well...

SELECT DATEADD(dd,(t.n-1)*14,'20090102') AS BiFriday

FROM dbo.Tally t

WHERE t.n <= (DATEDIFF(dd,'20090102','20100101')+1)/14

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 10

Jeff Moden (12/27/2008)
Just for grins, here's the CTE version without TOP...
;WITH
cteTally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS N
FROM Master.sys.SysColumns
)
SELECT DATEADD(dd,(t.n)*14,'20090102') AS BiFriday
FROM cteTally t
WHERE t.n < (DATEDIFF(dd,'20090102','20100101')+1)/14
Of course, the Tally table version is still faster than the CTE version and works in 2k as well...
SELECT DATEADD(dd,(t.n-1)*14,'20090102') AS BiFriday
FROM dbo.Tally t
WHERE t.n <= (DATEDIFF(dd,'20090102','20100101')+1)/14

Yea great explanation Jeff! :hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

Jeff Moden (12/27/2008)
Just for grins, here's the CTE version without TOP...
;WITH
cteTally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS N
FROM Master.sys.SysColumns
)
SELECT DATEADD(dd,(t.n)*14,'20090102') AS BiFriday
FROM cteTally t
WHERE t.n < (DATEDIFF(dd,'20090102','20100101')+1)/14
Of course, the Tally table version is still faster than the CTE version and works in 2k as well...
SELECT DATEADD(dd,(t.n-1)*14,'20090102') AS BiFriday
FROM dbo.Tally t
WHERE t.n <= (DATEDIFF(dd,'20090102','20100101')+1)/14

Hi Jeff,

I don't want to be too picky, but the semicolon in front of the WITH is rather odd.

The semicolon would belong to a potential previous statement within the same batch and should be positioned at the end of that statement instead. Otherwise you should omit the semicolon.

Of course the solution to the problem is still excellent as usual:-)

Best Regards,

Chris Büttner

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 12

Thanks, Chris. Yeah, I know it looks unusual... WITH is the only place where a semi-colon is actually required before something and since I don't use semi-colons anywhere else in my code, I just include it as part of the WITH even if it's a standalone.

Lordy, I hope they never make SQL Server require semi-colons for everything like {gasp} Oracle and some of the programming languages do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90254 More actions · Answer 13

Christian - the semi-colon is required before a CTE WITH statement. I personally prefer prefixing the WITH instead of relying upon the previous statement ending with a semi-colon. I have found that others will not include the ending semi-colon, no matter how much I tell them it is going to cause them problems.

I have had several individuals already ask with the CTE is failing after they have made a minor change. In every case, it came down to that developer inserting code before the CTE and not ending it with a semi-colon.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 14

Jeff Moden (12/28/2008)
Thanks, Chris. Yeah, I know it looks unusual... WITH is the only place where a semi-colon is actually required before something and since I don't use semi-colons anywhere else in my code, I just include it as part of the WITH even if it's a standalone.
Lordy, I hope they never make SQL Server require semi-colons for everything like {gasp} Oracle and some of the programming languages do.

Hm I kind of like that approach with semicolon after each statement, but that's probably personal taste 🙂

The reason they implemented this now is that the WITH has more than one meaning. And I assume that there might follow some more keywords in the future that will also require semicolons to distinguish between keywords that start a new statement and keywords that are part of a statement other than in the beginning.

Conventions aid us in that they remove decision processes from us. Instead of having do decide for each statement whether a semicolon is necessary, you just use it all the time and don't have to waste your brainpower. The unfortunate thing is only that we have learned not to use these semicolons. So it requires us "oldies" reprogramming our brains which is always tedious.

But for new SQL programmers, i would always recommend to start using the semicolon for all statements.

By the way, the semicolon is also required for the SEND ON CONVERSATION (service broker).

Best Regards,

Chris Büttner