Calculate weekend

Question

Calculate weekend

Viewing 14 posts - 16 through 28 (of 28 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Jeff is right...

I dont have any knowledge of Tally Table..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

kapil_kk (12/3/2012)
Jeff is right...
I dont have any knowledge of Tally Table..

Please see the following article. A Tally Table (or Numbers table, as some call it) is a simple table of sequential integers used to replace a WHILE loop.

http://www.sqlservercentral.com/articles/T-SQL/62867/

Joe is correct though. A Calendar table (pretty obvious what that is) would do better here. I'm on my way to work so don't have the time to demo that kind of solution now but I'll check on this post when I get home.

--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)

Pete Cox SSChasing Mays Points: 636 More actions · Answer 3

Using Joe's Calendar table, this might provide a way for the kapil to get to the answer he wants.

It removes the need to recursively check if you added another sunday in the extension of the date period

SELECT cal_date FROM Calendar

WHERE julian_business_nbr = (SELECT julian_business_nbr + 30 FROM Calendar WHERE cal_date = '2007-04-05')

bleroy Hall of Fame Points: 3052 More actions · Answer 4

I'm with CELKO - use a calendar table, then you can count your Sundays, weekends, Easter Mondays and whatever else you need between two dates - much much easier, and kind on processing too.

For an example calendar, you can check out:

http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/data-warehouse-books/booksmdwt/ (go to Chapter 7—Design and Develop the ETL System; date dimension) - Direct Link for DDL and sample data:

http://www.kimballgroup.com/wp-content/uploads/2012/07/Ch07_Date_Dim_2000-2020.xlsx

HTH,

B

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

kapil_kk (12/3/2012)
in this case it will be '2012-02-03'

Care to answer all the other questions I asked? I really don't feel like retyping them.

Pete Cox SSChasing Mays Points: 636 More actions · Answer 6

Given a Calendar table something like this one

(I only allow NULLS for the contruction phase of the table)

CREATE TABLE [dbo].[Calendar](

[CalendarDate] [smalldatetime] NOT NULL,

...

[DayName] [varchar](9) NULL,

[ShortDayName] [char](3) NULL,

...

[EpochSundayCount] [int] NULL,

[EpochBusinessDayCount] [int] NULL,

PRIMARY KEY CLUSTERED

(

[CalendarDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

the following T-SQL would work to set the quasi-Julian dates.

UPDATE Calendar

SET EpochBusinessDayCount =

DATEDIFF(day, (SELECT MIN(CalendarDate) from Calendar), CalendarDate)

+ 1

- (SELECT COUNT(CalendarDate) FROM Calendar c2 WHERE ShortDayName = 'Sun' and c2.CalendarDate <= c1.CalendarDate)

,

EpochSundayCount =

(SELECT COUNT(CalendarDate) FROM Calendar c2 WHERE ShortDayName = 'Sun' and c2.CalendarDate <= c1.CalendarDate)

FROM Calendar c1

I've chosen to start from the earliest date in the calendar table and just keep rolling, so as to avoid the problem of crossing year boundaries.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

I will try with Tally table and tally calender which Jeff has suggested....

Thnks for the suggestion 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ScottPletcher SSC Guru Points: 101103 More actions · Answer 8

CELKO (12/1/2012)
Please learn to use the ISO-8601 date formats; it is the only one allowed in Standard SQL and the other ISO Standards. Use the DATEADD() function for this.

Or, for your own sake, DON'T.

The safe format to use in SQL Server is 'YYYYMMDD', which is always interpreted correctly, rather than 'YYYY-MM-DD', which can cause abends.

When an error occurs at 3AM because of this, I don't think anyone will be happy just because you followed some arbitrary theoretical standard, rather than using an error-proof method for the actual db you are using.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Pete Cox SSChasing Mays Points: 636 More actions · Answer 9

YYYYMMDD is in fact the Basic date format proposed by ISO8601.

YYYY-MM-DD is named the Extended format.

Using either is OK by the standard.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

kapil_kk (12/3/2012)
I will try with Tally table and tally calender which Jeff has suggested....
Thnks for the suggestion 🙂

To be clear and to give credit where credit is due, I wasn't the first on this thread to suggest either. I was just providing a little concurrence for those that did.

I believe that a Calendar table will proably suite you the best for these types of thigs in the long run. If you have difficulty in building one or using it, c'mon back with another post and we'll give you a leg up.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Pete Cox (12/4/2012)
YYYYMMDD is in fact the Basic date format proposed by ISO8601.
YYYY-MM-DD is named the Extended format.
Using either is OK by the standard.

True, but YYYY-MM-DD doesn't always work depending on the setting of DATEFORMAT, whereas YYYYMMDD will always be correctly converted to datetime values (at least for now).

ScottPletcher SSC Guru Points: 101103 More actions · Answer 12

Pete Cox (12/4/2012)
YYYYMMDD is in fact the Basic date format proposed by ISO8601.
YYYY-MM-DD is named the Extended format.
Using either is OK by the standard.

Excellent point, and quite correct (I just researched it).

And it means that Celko's oft-repeated claim that 'yyyy-mm-dd' is the "only" valid ISO format is 100% false.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Pete Cox SSChasing Mays Points: 636 More actions · Answer 13

Pete Cox

SSChasing Mays

Points: 636

December 4, 2012 at 1:19 pm

#1565479

Thanks Lynn, got it. Safer is Better,

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 14

ScottPletcher (12/3/2012)
CELKO (12/1/2012)
Please learn to use the ISO-8601 date formats; it is the only one allowed in Standard SQL and the other ISO Standards. Use the DATEADD() function for this.
Or, for your own sake, DON'T.
The safe format to use in SQL Server is 'YYYYMMDD', which is always interpreted correctly, rather than 'YYYY-MM-DD', which can cause abends.
When an error occurs at 3AM because of this, I don't think anyone will be happy just because you followed some arbitrary theoretical standard, rather than using an error-proof method for the actual db you are using.

+1!!!!

--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)