Join to Calendar Table

Question

Post reply

Join to Calendar Table

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
June 3, 2013 at 9:23 am

#273484

Hello Everyone
I hope that you are having a very nice monday, and that you had a great weekend.
I am using the Calendar table. The DDL is shown below:
I need to select the FirstDateOfWeek and LastDateOfWeek using the current date, without having to query the table twice. What would be the beast way to set those two values into variables for use later in another query? If I had to query the table twice, that will have to do, but I am sure there is a much more efficient means to perform this task.
I would like to simply join the table from a table that is storing a date, this is also a possibility to select the two dates from the Calendar table.
[dbo].[Calendar]
[CalendarDate] [date] NOT NULL,
[CalendarYear] [int] NOT NULL,
[CalendarMonth] [int] NOT NULL,
[CalendarDay] [int] NOT NULL,
[DayOfWeekName] [varchar](10) NOT NULL,
[FirstDateOfWeek] [date] NOT NULL,
[LastDateOfWeek] [date] NOT NULL,
[FirstDateOfMonth] [date] NOT NULL,
[LastDateOfMonth] [date] NOT NULL,
[FirstDateOfQuarter] [date] NOT NULL,
[LastDateOfQuarter] [date] NOT NULL,
[FirstDateOfYear] [date] NOT NULL,
[LastDateOfYear] [date] NOT NULL,
[BusinessDay] [bit] NOT NULL,
[NonBusinessDay] [bit] NOT NULL,
[Weekend] [bit] NOT NULL,
[Holiday] [bit] NOT NULL,
[Weekday] [bit] NOT NULL,
[CalendarDateDescription] [varchar](50) NULL
Thank You in advance for all your suggestions, assistance and comments
Andrew SQLDBA

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Something like this?

SELECT LoadsOfStuff

FROM AnotherTable a

INNER JOIN [dbo].[Calendar] c

ON a.aDate BETWEEN c.FirstDateOfWeek AND c.LastDateOfWeek

AND c.CalendarDate = CAST(GETDATE() AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thank You

That was really close, but it was my fault. I don't believe that I was able to describe what I needed, with an exact statement. I was able to get the data needed after seeing your query. For some reason, a simple join escaped me.

Thank you for your sample and advice.

Andrew SQLDBA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome.

Can we see your final query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden