Calculating sum of days/weeks

Calculating sum of days/weeks

Hi, I have a calendar table that have the days, I need to add the following columns Days in a month Days in a quarter Days in a year Weeks in a month Weeks in a quarter I am finding the same issue with calculating how long to someone birthday. Here is what I wrote

Hi, I have a calendar table that have the days, I need to add the following columns Days in a month Days in a quarter Days in a year Weeks in a month Weeks in a quarter I am finding the same issue with calculating how long to someone birthday. Here is what I wrote

You're on the right track for days in a quarter and days in a year, but you need to use the first day in a quarter/year for the comparison. You've already been pointed to a website of useful date functions which includes finding the first day in a period.

Weeks in a quarter is more problematic, because week could be defined several different ways, and each of those ways would have a slightly different coding. For instance, what day does your week start? How do you want to handle quarters that start/end in the middle of the week?

To be able to answer this question correctly, you need to tell us what a week, month, and quarter is.

What day of the week does a week start on?What do you want done with months that don't have a whole week, especially the first and last weeks of any given year?Since years don't have a whole number of weeks, how do you want to handle that?What's a month? Does it start on the 1st and end the day before the 1st of the next month or does it start and end based on your definition of a week?What's a quarter? We all know the classic definition based on calendar days but that and the definition of what a month is all go flying out the window if you start talking about whole weeks.

Or, does it even matter if weeks and months and quarters don't actually start on the same day of the week?

Or, have they really messed things up with a fiscal calendar?

So, how do you want to define what week, month, and quarter is?

--Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Basically we are talking about a fiscal year.The year starts Jan 1stthe months start on each month. as the quarters do too and last but not least the weekday stars on Sunday (I don't even know why I did that)

It's wasteful to store monthly, quarterly and yearly data in a daily table (actually, it's wasteful to store days in a year anywhere, since your year is a calendar year). Create a separate table and store the values by month.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

There is absolutely NO REASON to use Windowed functions here. The last day of this month is the first day of next month - 1. All of the dates here can be calculated by a similar method as is outlined in the link provided very early in this thread. Did you even bother to read that link?