January 25, 2018 at 7:43 am
It does not matter for the optimiser in a join.
Range definitions don't affect the choice of plans.
I wrote pretty complicated reports where different products had different aggregation periods.
My joins looked like this:
ON EventDate => PeriodStart and EventDate <
Case PeriodType when 'Week' then DATEADD(wk, NoOfPeriods, PeriodStart)
When 'Month' then DATEADD(mm, NoOfPeriods, PeriodStart)
... Etc.
Not a hick up on a compilation of a report aggregating 100 mil records.
And no change of code required if a product definition(s) changed.
For some products BOM counted from the date when, say, a mortgage was taken.
So, even PeriodStart is individual for every customer.
Therefore it cannot a value from a table, it must be a calculation based on product initiation date and a calendar applied to that product.
And you know - it all works. Done it, surprisingly successfully.
_____________
Code for TallyGenerator
January 25, 2018 at 8:03 am
I can't believe the number of people hung up on February not always having the same last day. Some simple SQL with get the correct date for you.
Not everyone here is working for a global company or needs to worry about transactions from multiple time zones. When you work in an industry where you simply run nightly batch jobs to accumulate the 'days' transactions, you aren't worried about time zones or transactions at 12:15 AM. Each transaction is loaded into the warehouse with the transaction date and time but we also add a date only EOM date to that record. So anything process during the day 01/24/2018 would have that transaction date along with the time the transaction took place. That record would also contain the EOM date of 01/31/2018. So when we need to find everything that was processed in January 2018 we only need to worry about one date, the EOM date of 01/31/2018. And you are ONLY hard coding that date for ADHOC queries. So I wouldn't need to worry once I need to look for February data, that date logic would have been taken care of if it is a leap year or not. And if I am running an ADHOC query for February 2016, and I put 02/28/2016 and get no results guess what, then it's 02/29/2016. Again not rocket science here people.
So again 'it depends' applies. If you are in a global or a company where transaction happen in different time zones you may want to use the BOM method. If not EOM may be all you need.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 25, 2018 at 8:28 am
Below86
The only thing I can say is that low scale of the project is no excuse for lame design implemented in your system.
Every piece you just described is a design error.
Having EOM date for each transaction is one of them.
And all the processes are very manual, as I can understand.
I'm not sure if you'd even listen somebody who'd start explaining you how to correct the wrongs.
If everybody at your place is happy about how things are going - so be it.
_____________
Code for TallyGenerator
January 25, 2018 at 8:34 am
below86 - Thursday, January 25, 2018 8:03 AMI can't believe the number of people hung up on February not always having the same last day. Some simple SQL with get the correct date for you.
Not everyone here is working for a global company or needs to worry about transactions from multiple time zones. When you work in an industry where you simply run nightly batch jobs to accumulate the 'days' transactions, you aren't worried about time zones or transactions at 12:15 AM. Each transaction is loaded into the warehouse with the transaction date and time but we also add a date only EOM date to that record. So anything process during the day 01/24/2018 would have that transaction date along with the time the transaction took place. That record would also contain the EOM date of 01/31/2018. So when we need to find everything that was processed in January 2018 we only need to worry about one date, the EOM date of 01/31/2018. And you are ONLY hard coding that date for ADHOC queries. So I wouldn't need to worry once I need to look for February data, that date logic would have been taken care of if it is a leap year or not. And if I am running an ADHOC query for February 2016, and I put 02/28/2016 and get no results guess what, then it's 02/29/2016. Again not rocket science here people.So again 'it depends' applies. If you are in a global or a company where transaction happen in different time zones you may want to use the BOM method. If not EOM may be all you need.
Correct. The February thing is "just" a symptom that is being used as a simple example of the larger problem.
You said above that each "record" contains a transaction date. You also say that each transaction record also has an EOM (hardcoded) in it. Some may store the BOM or even both in the row.
Even in a data warehouse, both of those notions are wrong because they don't, in fact, support things that you spoke of, such as someone deciding to change to 14 day periods instead of monthly periods or, maybe, someone finally gets "religious" and shifts to ISO months or weeks, or (God forbid) a fiscal year that starts in an "off month" such as June. Further, storing EOM or BOM in the transaction "record" IS a form of hardcoding that must be avoided because you're mixing a date dimension with the data itself and then, like you said, people write hundreds of thousand of lines of code to support that hardcoding in the data and when corporate commands a change, then people have long nights ahead of them and heads begin to roll because it's impossible to make the change quickly and without error.
Designed correctly, all someone would need to do is make a simple change to a FROM/TO date dimension table (which is what I believe the OP was asking for). If you use the concept of somedatecol >= someintervalstart and somedatecol < somenextintervalstart, you can easily, quickly, and with no code changes (avoiding any chance of inducing errors there) handle any and all required changes and ad hoc queries, whether they contain a time element or not.
--Jeff Moden
January 25, 2018 at 8:48 am
Sergiy - Thursday, January 25, 2018 8:28 AMBelow86The only thing I can say is that low scale of the project is no excuse for lame design implemented in your system.Every piece you just described is a design error.Having EOM date for each transaction is one of them.And all the processes are very manual, as I can understand.I'm not sure if you'd even listen somebody who'd start explaining you how to correct the wrongs.If everybody at your place is happy about how things are going - so be it.
Sergei, again, what if the interval of each range is different? Then you need an ending range value directly in the row (Or get the next row AND deal with the issue of how to deal with the last range).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 25, 2018 at 10:17 am
Sergiy - Thursday, January 25, 2018 8:28 AMBelow86The only thing I can say is that low scale of the project is no excuse for lame design implemented in your system.Every piece you just described is a design error.Having EOM date for each transaction is one of them.And all the processes are very manual, as I can understand.I'm not sure if you'd even listen somebody who'd start explaining you how to correct the wrongs.If everybody at your place is happy about how things are going - so be it.
The code I proposed was just that a suggestion of an answer, this isn't my question nor what I've implemented in warehouse. And as far as the use of the EOM date this is a warehouse that has been in place for 20 years. I don't have the time or resources to go redesigning it.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 25, 2018 at 10:24 am
Jeff Moden - Thursday, January 25, 2018 8:34 AMbelow86 - Thursday, January 25, 2018 8:03 AMI can't believe the number of people hung up on February not always having the same last day. Some simple SQL with get the correct date for you.
Not everyone here is working for a global company or needs to worry about transactions from multiple time zones. When you work in an industry where you simply run nightly batch jobs to accumulate the 'days' transactions, you aren't worried about time zones or transactions at 12:15 AM. Each transaction is loaded into the warehouse with the transaction date and time but we also add a date only EOM date to that record. So anything process during the day 01/24/2018 would have that transaction date along with the time the transaction took place. That record would also contain the EOM date of 01/31/2018. So when we need to find everything that was processed in January 2018 we only need to worry about one date, the EOM date of 01/31/2018. And you are ONLY hard coding that date for ADHOC queries. So I wouldn't need to worry once I need to look for February data, that date logic would have been taken care of if it is a leap year or not. And if I am running an ADHOC query for February 2016, and I put 02/28/2016 and get no results guess what, then it's 02/29/2016. Again not rocket science here people.So again 'it depends' applies. If you are in a global or a company where transaction happen in different time zones you may want to use the BOM method. If not EOM may be all you need.
Correct. The February thing is "just" a symptom that is being used as a simple example of the larger problem.
You said above that each "record" contains a transaction date. You also say that each transaction record also has an EOM (hardcoded) in it. Some may store the BOM or even both in the row.
Even in a data warehouse, both of those notions are wrong because they don't, in fact, support things that you spoke of, such as someone deciding to change to 14 day periods instead of monthly periods or, maybe, someone finally gets "religious" and shifts to ISO months or weeks, or (God forbid) a fiscal year that starts in an "off month" such as June. Further, storing EOM or BOM in the transaction "record" IS a form of hardcoding that must be avoided because you're mixing a date dimension with the data itself and then, like you said, people write hundreds of thousand of lines of code to support that hardcoding in the data and when corporate commands a change, then people have long nights ahead of them and heads begin to roll because it's impossible to make the change quickly and without error.
Designed correctly, all someone would need to do is make a simple change to a FROM/TO date dimension table (which is what I believe the OP was asking for). If you use the concept of somedatecol >= someintervalstart and somedatecol < somenextintervalstart, you can easily, quickly, and with no code changes (avoiding any chance of inducing errors there) handle any and all required changes and ad hoc queries, whether they contain a time element or not.
You make valid points, but again are missing the mark. This has morphed from what the original post was and more of a BOM vs. EOM discussion. As I replied to sergly, this is the way the warehouse was designed. And as I stated earlier, maybe in and ideal situation we would have the time and resource to change it. But that's not going to happen. So we will use EOM and continue to use it. In the insurance industry I seriously doubt there ever will be a time that the normal 'end of month' financials and reporting would change to part of a month or change when a reporting period start and end.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 25, 2018 at 4:07 pm
below86 - Thursday, January 25, 2018 10:24 AMJeff Moden - Thursday, January 25, 2018 8:34 AMbelow86 - Thursday, January 25, 2018 8:03 AMI can't believe the number of people hung up on February not always having the same last day. Some simple SQL with get the correct date for you.
Not everyone here is working for a global company or needs to worry about transactions from multiple time zones. When you work in an industry where you simply run nightly batch jobs to accumulate the 'days' transactions, you aren't worried about time zones or transactions at 12:15 AM. Each transaction is loaded into the warehouse with the transaction date and time but we also add a date only EOM date to that record. So anything process during the day 01/24/2018 would have that transaction date along with the time the transaction took place. That record would also contain the EOM date of 01/31/2018. So when we need to find everything that was processed in January 2018 we only need to worry about one date, the EOM date of 01/31/2018. And you are ONLY hard coding that date for ADHOC queries. So I wouldn't need to worry once I need to look for February data, that date logic would have been taken care of if it is a leap year or not. And if I am running an ADHOC query for February 2016, and I put 02/28/2016 and get no results guess what, then it's 02/29/2016. Again not rocket science here people.So again 'it depends' applies. If you are in a global or a company where transaction happen in different time zones you may want to use the BOM method. If not EOM may be all you need.
Correct. The February thing is "just" a symptom that is being used as a simple example of the larger problem.
You said above that each "record" contains a transaction date. You also say that each transaction record also has an EOM (hardcoded) in it. Some may store the BOM or even both in the row.
Even in a data warehouse, both of those notions are wrong because they don't, in fact, support things that you spoke of, such as someone deciding to change to 14 day periods instead of monthly periods or, maybe, someone finally gets "religious" and shifts to ISO months or weeks, or (God forbid) a fiscal year that starts in an "off month" such as June. Further, storing EOM or BOM in the transaction "record" IS a form of hardcoding that must be avoided because you're mixing a date dimension with the data itself and then, like you said, people write hundreds of thousand of lines of code to support that hardcoding in the data and when corporate commands a change, then people have long nights ahead of them and heads begin to roll because it's impossible to make the change quickly and without error.
Designed correctly, all someone would need to do is make a simple change to a FROM/TO date dimension table (which is what I believe the OP was asking for). If you use the concept of somedatecol >= someintervalstart and somedatecol < somenextintervalstart, you can easily, quickly, and with no code changes (avoiding any chance of inducing errors there) handle any and all required changes and ad hoc queries, whether they contain a time element or not.
You make valid points, but again are missing the mark. This has morphed from what the original post was and more of a BOM vs. EOM discussion. As I replied to sergly, this is the way the warehouse was designed. And as I stated earlier, maybe in and ideal situation we would have the time and resource to change it. But that's not going to happen. So we will use EOM and continue to use it. In the insurance industry I seriously doubt there ever will be a time that the normal 'end of month' financials and reporting would change to part of a month or change when a reporting period start and end.
Nope... the point that your warehouse already exists isn't lost on me. We have, IMHO, three different but very related subjects here.
1. What the OP asked for... and that leads to 2. and 3. below...
2. What's the right way to do such a thing?
3. For those things that are existing, what should be done?
My original question to the OP about 1. was to ask what the overriding task actually is so that we know whether or not 2. or 3. would be the best answer. I've not gone through all of the most recent posts but it appears that the OP has left the building on the subject. He might be too busy trying to figure out how to handle Feb 29th. 😉
The fact that we keep talking about your problem is because it's a good example as to why the OP should get back to us with what the real need is so that we can help them avoid future problems. Please don't take that as a slam. Your company solved a problem in a particular manner and that may be all they need. Some of us are identifying the considerations we would have made and are also identifying some of the potential problems that people can have if they're not aware of those considerations at design time. As you say, you've been doing it this way for 20 years with no problems. I can't speak for anyone else but I've been using the methods I speak of for about that long and not only haven't had any problems but have easily weathered changes in things like company calendar requirements and changes to interval requirements at both the processing and reporting levels and those weatherings required nearly no code changes never mind changing thousand of lines of code.
To that latter point, when I spoke of making the change on future code, I should have used the word "project" instead of "code" because it's not worth the risk in changing all of that code unless you believe that it will become a necessary eventuality.
--Jeff Moden
January 25, 2018 at 4:11 pm
As a bit of a side-bar, ALL of the discussions have been great, IMHO. We got everything from speculation to "here's my experience" to "my stuff is pretty much encased in concrete and couldn't change it if I wanted to". You've just gotta love this community.
--Jeff Moden
January 26, 2018 at 8:31 am
But if only the start date is in the row, how do you handle "changes in interval"? And especially a variable interval, say, where some rows apply for 2 weeks and others for 6 weeks?
Don't you either have to "peek" at the next start date or build an interval control value into the row. Those are far move complex, and involve every process that uses the data adding complex processes that can get bugs in them, rather than simply putting an ending date there.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 26, 2018 at 8:41 am
Wasn't it answering your question?
https://qa.sqlservercentral.com/Forums/FindPost1920045.aspx
_____________
Code for TallyGenerator
January 26, 2018 at 4:58 pm
Sergiy - Friday, January 26, 2018 8:41 AMWasn't it answering your question?https://qa.sqlservercentral.com/Forums/FindPost1920045.aspx
No. You need to put some thought into the actual issue/problem if the intervals become variable and/or you just don't want every later process to have to include the logic to determine end date.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 27, 2018 at 5:21 am
Scott, it seems you forgot what is the controversy we're discussing.
I'm saying that EOM (end of period) date is absolutely useless, and the date of the beginning of next month (next period) should be used instead.
You're saying EOM can be useful because you might want to have a value in the same row, don't want to use calculations, and so on.
Your argument does not prove your point. At all.
You prefer to have both ends of a date range to be stored in a table - you can do it.
But those 2 dates must be BOM and BOnextM, not BOM and EOM.
And WHERE/ON conditions must be
>= BOM AND < BOnextM
Only this approach can deliver reliable and trustful results.
What is the better way of maintaining BOnextM dates - that's totally different topic, irrelevant to this discussion.
Whatever could be an outcome of that discussion - EOM still remains useless.
_____________
Code for TallyGenerator
January 27, 2018 at 5:45 am
Sergiy - Saturday, January 27, 2018 5:21 AMScott, it seems you forgot what is the controversy we're discussing.I'm saying that EOM (end of period) date is absolutely useless, and the date of the beginning of next month (next period) should be used instead.You're saying EOM can be useful because you might want to have a value in the same row, don't want to use calculations, and so on.Your argument does not prove your point. At all.You prefer to have both ends of a date range to be stored in a table - you can do it.But those 2 dates must be BOM and BOnextM, not BOM and EOM.And WHERE/ON conditions must be >= BOM AND < BOnextMOnly this approach can deliver reliable and trustful results.What is the better way of maintaining BOnextM dates - that's totally different topic, irrelevant to this discussion.Whatever could be an outcome of that discussion - EOM still remains useless.
Just not true. One can use BETWEEN for such limits, as long as you accurately set the ending value. Yes, I very strongly prefer the >= and < style as well, because it's far less prone to issues if the range data type or exact margins change, but it's not impossible to make a fixed ending value work properly.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 27, 2018 at 8:34 am
No, It is true.
You mentioned issues EOM brings with it, but there is nothing to show as it's advantage against BOnextM which could compensate for those issues.
_____________
Code for TallyGenerator
Viewing 15 posts - 91 through 104 (of 104 total)
You must be logged in to reply to this topic. Login to reply