## Calculating sum of days/weeks

 Author Message astrid 69000 SSCrazy Group: General Forum Members Points: 2270 Visits: 337 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`--days in a month datediff(day, [Date], dateadd(month, 1, [Date])), [Date] --days in a quarter datediff(day, [Date], dateadd(quarter, 1, [Date])) --days in a year datediff(day, [Date], dateadd(year, 1, [Date])) --weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))`Ideas please pietlinden SSC Guru Group: General Forum Members Points: 52741 Visits: 16815 Lynn Pettis wrote a quick article on a bunch of useful date functions. It's here:http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ astrid 69000 SSCrazy Group: General Forum Members Points: 2270 Visits: 337 thanks, but that is not what i was looking for drew.allen SSC Guru Group: General Forum Members Points: 64216 Visits: 17068 +xastrid 69000 - Tuesday, February 6, 2018 11:22 AMHi, 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`--days in a month datediff(day, [Date], dateadd(month, 1, [Date])), [Date] --days in a quarter datediff(day, [Date], dateadd(quarter, 1, [Date])) --days in a year datediff(day, [Date], dateadd(year, 1, [Date])) --weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))`Ideas please 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?Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. astrid 69000 SSCrazy Group: General Forum Members Points: 2270 Visits: 337 I know where my problem is :, but whatever I coded didn't give me the right answer. that is why I asked for help. Jeff Moden SSC Guru Group: General Forum Members Points: 873095 Visits: 47520 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs astrid 69000 SSCrazy Group: General Forum Members Points: 2270 Visits: 337 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) astrid 69000 SSCrazy Group: General Forum Members Points: 2270 Visits: 337 oki, I have almost figured it our, I have a table with a columns date, so here is what I did (I am not a 100% about the weeks in a quarter)`with firstdayoffas(select [date], FirstDayOfMonth = FirstOfMonth, LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]), FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]), LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]), FirstDayOfYear = FirstOfYear, LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]), FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth), FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear) from #dim)selectdd.[Date],datediff(day, ff.FirstDayOfMonth, dateadd(month, 0, dateadd(dd,1,ff.LastDayOfMonth))) as [DaysinMonth], datediff(day, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter))) [DaysinQuarter], datediff(day, ff.FirstDayOfYear, dateadd(year, 0, dateadd(dd,1,ff.LastDayOfYear))) [DaysinYear], datediff(WEEK, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter)))from #dim dd inner join firstdayoff ff on dd.[Date] = ff.[date]order by dd.[Date]` ScottPletcher SSC Guru Group: General Forum Members Points: 84467 Visits: 9532 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. drew.allen SSC Guru Group: General Forum Members Points: 64216 Visits: 17068 +xastrid 69000 - Wednesday, February 7, 2018 11:52 AMoki, I have almost figured it our, I have a table with a columns date, so here is what I did (I am not a 100% about the weeks in a quarter)`with firstdayoffas(select [date], FirstDayOfMonth = FirstOfMonth, LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]), FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]), LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]), FirstDayOfYear = FirstOfYear, LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]), FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth), FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear) from #dim)selectdd.[Date],datediff(day, ff.FirstDayOfMonth, dateadd(month, 0, dateadd(dd,1,ff.LastDayOfMonth))) as [DaysinMonth], datediff(day, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter))) [DaysinQuarter], datediff(day, ff.FirstDayOfYear, dateadd(year, 0, dateadd(dd,1,ff.LastDayOfYear))) [DaysinYear], datediff(WEEK, ff.FirstDayOfQuarter, dateadd(QUARTER, 0, dateadd(dd,1,ff.LastDayOfQuarter)))from #dim dd inner join firstdayoff ff on dd.[Date] = ff.[date]order by dd.[Date]`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?Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post.