July 28, 2014 at 7:56 am
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
Heh... 1) because they normally don't do such a thing and 2) when they do, it's usually wrong. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2014 at 8:11 am
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.
100% agree. Plus it allows for different areas to calculate their own holidays. Every client is different.
July 28, 2014 at 8:28 am
paul.pennington (7/28/2014)
As written, your code would leave out any transactions datestamped for the start time of the period:WHERE
d.DateFirstIssued > fy.FYQ2
Shouldn't the operator be >= to include the start date and time?
This is what I was just coming here to say. Glad I'm not the only one who caught it.
I'm used to working with databases where the dates are all saved to a fiscal date table, but I otherwise found the article to be interesting for the cases when you're not provided those dates.
July 28, 2014 at 8:51 am
This got me a few times in queries where I've used BETWEEN @FromDate AND @ToDate in the WHERE clause, and the data type may be either smalldatetime or datetime. If you don't calculate the end date correctly, you may miss anything on the last day of a quarter where the time is included, or may include the first day of the next quarter, if you don't calculate the end time correctly.
/* IMPORTANT: Uses 23:59 and NOT 23:59:59, because when compared with smalldatetime,
which is only accurate to the nearest minute, 23:59 will remain as 23:59,
but 23:59:59 will be rounded up to midnight on the first day of the the next quarter.
*/
SET @ToDate = CASE @Quarter
WHEN 1 THEN CONVERT(datetime,'3/31/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 2 THEN CONVERT(datetime,'6/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 3 THEN CONVERT(datetime,'9/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 4 THEN CONVERT(datetime,'12/31/'+CONVERT(varchar(4),@Year)+' 23:59')
END
July 28, 2014 at 10:09 am
Jeff Moden (7/28/2014)
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
Heh... 1) because they normally don't do such a thing and 2) when they do, it's usually wrong. 😛
1) never had a issue with this. Once I explained the purpose and what I needed, it was readily supplied. Guess I've been lucky so far...
2) if they get it wrong, *they* get it wrong...not me. As a DBA I'm always uncomfortable being responsible for the content of a database (that is, the actual data values). I'll gladly take on integrity, backups, performance etc. and work with the business to ensure proper constraints, but after that I leave the content to the business people.
July 28, 2014 at 5:13 pm
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
Heh... 1) because they normally don't do such a thing and 2) when they do, it's usually wrong. 😛
1) never had a issue with this. Once I explained the purpose and what I needed, it was readily supplied. Guess I've been lucky so far...
2) if they get it wrong, *they* get it wrong...not me. As a DBA I'm always uncomfortable being responsible for the content of a database (that is, the actual data values). I'll gladly take on integrity, backups, performance etc. and work with the business to ensure proper constraints, but after that I leave the content to the business people.
Understood and thanks for the feedback. I'm just not one to let people get things wrong where data is concerned if I can help it. If it goes in wrong, it's their fault for a bad design and my fault for not catching it. It's also much easier for me to just generate things like this according to the rules they would use to create a spreadsheet than to convert their spreadsheet.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2014 at 5:22 pm
bpatin (7/28/2014)
This got me a few times in queries where I've used BETWEEN @FromDate AND @ToDate in the WHERE clause, and the data type may be either smalldatetime or datetime. If you don't calculate the end date correctly, you may miss anything on the last day of a quarter where the time is included, or may include the first day of the next quarter, if you don't calculate the end time correctly.
/* IMPORTANT: Uses 23:59 and NOT 23:59:59, because when compared with smalldatetime,
which is only accurate to the nearest minute, 23:59 will remain as 23:59,
but 23:59:59 will be rounded up to midnight on the first day of the the next quarter.
*/
SET @ToDate = CASE @Quarter
WHEN 1 THEN CONVERT(datetime,'3/31/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 2 THEN CONVERT(datetime,'6/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 3 THEN CONVERT(datetime,'9/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 4 THEN CONVERT(datetime,'12/31/'+CONVERT(varchar(4),@Year)+' 23:59')
END
Shouldn't be done that way either because it doesn't catch the last second of the day and I DO have a lot happening then.
I strongly recommend what most folks consider to be the best practice of calculating (or pulling from a Calendar table) midnight of the next day and take everything before then. Then it truly doesn't matter what the date type is whether you use the low resolution of the DATE datatype or the high resolution of some forms the DATETIME2 datatype.
Similar to this (assuming the variables contain "whole" dates of one form or another)...
WHERE SomeDateColumn >= @StartDate
AND SomeDateColumn < DATEADD(dd,1,@EndDate)
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2014 at 5:31 pm
Jeff Moden (7/28/2014)
sslyle-1091060 (7/28/2014)
What if your period are 5-4-4?Good question and would make for a good article to exploit the ISO_Week datepart (came out with 2008) of the datetime functions. The question is, what do you want done with years that have 53 fiscal weeks in them? One company I worked at used 6-4-4, 5-4-4, 5-4-4, 5-4-4 and another used 5-4-4, 5-4-4, 5-4-4, 5-4-5.
Of course, I've also seen some non-US companies that started their work-week on Thursdays instead of Mondays and a lot of US companies that start their work week on Sundays instead of Mondays (working for one right now), which always makes things a bit more interesting in calculating 5-4-4 quarters.
To be honest, I think calculating things by quarter is a bit archaic now. It was done to make reporting by accountants a whole lot easier in the long gone days before computers.
I worked for a company whose fiscal year was 4-4-5, 4-4-5, 4-4-5, 4-4-5 and one year was a 4-4-5, 4-4-5, 4-4-5, 4-4-6. Interesting fiscal year (and also the periods) started on a Sunday (not necessarily the first of a month) and ended on a Saturday. Luckily at the time I was only a computer operator and didn't have to worry about how this was calculated.
July 28, 2014 at 5:44 pm
Jeff Moden (7/28/2014)
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
gbritton1 (7/28/2014)
Jeff Moden (7/28/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
Heh... 1) because they normally don't do such a thing and 2) when they do, it's usually wrong. 😛
1) never had a issue with this. Once I explained the purpose and what I needed, it was readily supplied. Guess I've been lucky so far...
2) if they get it wrong, *they* get it wrong...not me. As a DBA I'm always uncomfortable being responsible for the content of a database (that is, the actual data values). I'll gladly take on integrity, backups, performance etc. and work with the business to ensure proper constraints, but after that I leave the content to the business people.
Understood and thanks for the feedback. I'm just not one to let people get things wrong where data is concerned if I can help it. If it goes in wrong, it's their fault for a bad design and my fault for not catching it. It's also much easier for me to just generate things like this according to the rules they would use to create a spreadsheet than to convert their spreadsheet.
I hear you! Though sometimes the rules are pixie dust and alchemy. That's why I try to stay out of it.
Gerald Britton, Pluralsight courses
July 28, 2014 at 6:18 pm
Jeff Moden (7/28/2014)
bpatin (7/28/2014)
This got me a few times in queries where I've used BETWEEN @FromDate AND @ToDate in the WHERE clause, and the data type may be either smalldatetime or datetime. If you don't calculate the end date correctly, you may miss anything on the last day of a quarter where the time is included, or may include the first day of the next quarter, if you don't calculate the end time correctly.
/* IMPORTANT: Uses 23:59 and NOT 23:59:59, because when compared with smalldatetime,
which is only accurate to the nearest minute, 23:59 will remain as 23:59,
but 23:59:59 will be rounded up to midnight on the first day of the the next quarter.
*/
SET @ToDate = CASE @Quarter
WHEN 1 THEN CONVERT(datetime,'3/31/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 2 THEN CONVERT(datetime,'6/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 3 THEN CONVERT(datetime,'9/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 4 THEN CONVERT(datetime,'12/31/'+CONVERT(varchar(4),@Year)+' 23:59')
END
Shouldn't be done that way either because it doesn't catch the last second of the day and I DO have a lot happening then.
I strongly recommend what most folks consider to be the best practice of calculating (or pulling from a Calendar table) midnight of the next day and take everything before then. Then it truly doesn't matter what the date type is whether you use the low resolution of the DATE datatype or the high resolution of some forms the DATETIME2 datatype.
Similar to this (assuming the variables contain "whole" dates of one form or another)...
WHERE SomeDateColumn >= @StartDate
AND SomeDateColumn < DATEADD(dd,1,@EndDate)
Sorry... my previous example didn't match what your CASE statement did. Here's the code that would do the same thing and it would also allow for high-performance iTVFs because there would be no need to declare separate variables for a start or end date for the quarter. It also doesn't have to do any slightly slower character based conversions.
Note also that this example is based solely on calendar years. It would be a pretty simple matter to make it based on other fiscal years.
--===== Simulate the parameters of a stored procedure
DECLARE @pYear SMALLINT
,@pQuarter TINYINT
;
SELECT @pYear = 2014
,@pQuarter = 3
;
--===== Example of how to return data for the given quarter including the
-- calculations for the boundary dates of the quarter. No matter the
-- datatype, not even 100 nano-seconds would be lost.
SELECT SomeColumnList
FROM dbo.SomeTable
WHERE SomeDateColumn >= DATEADD(qq,@pQuarter-1,DATEADD(yy,@pYear-1900,0)) --Start of desired quarter.
AND SomeDateColumn < DATEADD(qq,@pQuarter ,DATEADD(yy,@pYear-1900,0)) --Start of quarter after that.
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 29, 2014 at 2:52 pm
I was thinking they should've used a between clause instead of the greater than and less than clauses.
July 29, 2014 at 3:40 pm
Brett Phipps (7/29/2014)
I was thinking they should've used a between clause instead of the greater than and less than clauses.
BETWEEN uses the second operand which would include too much.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2014 at 9:56 am
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.
This is fine if you have one set of dates for an entire company. If, however, you have many clients/customers who EACH could have their own fiscal year/quarters, needing to calculate on the fly is extremely important.
August 7, 2014 at 11:31 am
danielk1 (8/7/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.This is fine if you have one set of dates for an entire company. If, however, you have many clients/customers who EACH could have their own fiscal year/quarters, needing to calculate on the fly is extremely important.
And such a table could be setup that includes this information for each of the clients/customers.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply