January 22, 2018 at 2:27 pm
ScottPletcher - Monday, January 22, 2018 1:50 PMUsing 0 for a date is common enough that it's not "cutesy". But I have no objection to someone who prefers to code '19000101'. EIther is common enough in SQL usage.The "standard", best-practice method for getting a starting time period is:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, <date_value> ), 0)
So for start of today is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE(), 0)
Similarly, for start of year is:
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE(), 0)A consistent formula. Additional integer adds are hardly "complex" and are so little overhead I doubt it can be measured. The standard formula, by your own admission, is the one most people already automatically understand, That clarity and consistency is more valuable than the "overhead" of a couple of integer calcs. Especially on something like this:
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1)
vs
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0))A single computation against a scalar variable? I'll choose the second every time. It follows the automatically-recognized pattern, thus speeding comprehension of the base expression, after which mental on-the-fly adjustments are easy.
At any rate, say if I want to get the 15th of the month, what negative number do I put in your formula to avoid "extra calcs"? Anyone would instantly know in my style that you'd just add 14 days, but I'm interested in hearing how that negative number would be determined.
The reason that '1900-01-01' is such a common reference date is that it has several properties that people often want to preserve. It is the beginning of the year, it is the beginning of the quarter, it is beginning of the month, it is the beginning of the day, and it is the beginning of every unit of time. Note that it is NOT the beginning of the week. Add to that that substituting 0 for 1900-01-01 makes it both easy to remember and easy to type.
Just because something is "standard" does not mean that it is best practice. There was another thread where they wanted a date range from Sunday at 3:00 AM to the following Sunday at 3:00 AM. Using this formula you would pick a reference date that had the properties of Sunday at 3:00 AM and could greatly simplify your formula. Other codes had to make additional changes, because 1900-01-01 00:00:00 does not fall on a Sunday and is not 3:00 AM in the morning.
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '1899-12-31 03:00', GETDATE()), '1899-12-31 03:00')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 22, 2018 at 2:47 pm
drew.allen - Monday, January 22, 2018 2:27 PMScottPletcher - Monday, January 22, 2018 1:50 PMUsing 0 for a date is common enough that it's not "cutesy". But I have no objection to someone who prefers to code '19000101'. EIther is common enough in SQL usage.The "standard", best-practice method for getting a starting time period is:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, <date_value> ), 0)
So for start of today is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE(), 0)
Similarly, for start of year is:
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE(), 0)A consistent formula. Additional integer adds are hardly "complex" and are so little overhead I doubt it can be measured. The standard formula, by your own admission, is the one most people already automatically understand, That clarity and consistency is more valuable than the "overhead" of a couple of integer calcs. Especially on something like this:
DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date), -1)
vs
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0))A single computation against a scalar variable? I'll choose the second every time. It follows the automatically-recognized pattern, thus speeding comprehension of the base expression, after which mental on-the-fly adjustments are easy.
At any rate, say if I want to get the 15th of the month, what negative number do I put in your formula to avoid "extra calcs"? Anyone would instantly know in my style that you'd just add 14 days, but I'm interested in hearing how that negative number would be determined.
The reason that '1900-01-01' is such a common reference date is that it has several properties that people often want to preserve. It is the beginning of the year, it is the beginning of the quarter, it is beginning of the month, it is the beginning of the day, and it is the beginning of every unit of time. Note that it is NOT the beginning of the week. Add to that that substituting 0 for 1900-01-01 makes it both easy to remember and easy to type.
Just because something is "standard" does not mean that it is best practice. There was another thread where they wanted a date range from Sunday at 3:00 AM to the following Sunday at 3:00 AM. Using this formula you would pick a reference date that had the properties of Sunday at 3:00 AM and could greatly simplify your formula. Other codes had to make additional changes, because 1900-01-01 00:00:00 does not fall on a Sunday and is not 3:00 AM in the morning.
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '1899-12-31 03:00', GETDATE()), '1899-12-31 03:00')Drew
I have to admit I still wouldn't use a date before 1900, I'd use 6 (19000107) instead, and still add the extra hours separately, again for clarity. I think it's really easy to overlook that " 03:00", especially if it happens to be " 03:00" in the morning when you have to look at it!
SELECT DATEADD(HOUR, 3, DATEADD(WEEK, DATEDIFF(WEEK, 6, GETDATE()), 6));
/*
I admit I expect most developers to know from usage that date "0" was a Monday.
But if I thought there was any potential mix-up with it, I'd do something like this:
*/
;WITH cte_base_date AS (
SELECT CAST('19000107' AS datetime) AS base_sunday
)
SELECT DATEADD(HOUR, 3, DATEADD(WEEK, DATEDIFF(WEEK, base_sunday, GETDATE()), base_sunday))
FROM cte_base_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 22, 2018 at 7:07 pm
below86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
That's how performance problems start. People treat SQL as if it were "any program language". It's not. And you don't need to write explicit loops because every SELECT is powered by a machine language loop that I call a "pseudo cursor" (R. Barry Young first coined the phrase right here on SSC). You just need to learn about that just like you would learn any other language. Others that need to follow and understand the code need to learn it. If they don't then, just as "in any language", you can abstract (Black Box it) the functionality so that they don't even have to care about it.
If you want to learn more about the proverbial "Swiss Army Knife" of SQL Server, read and understand the following introductory article. It will change your career for the better.
http://qa.sqlservercentral.com/articles/T-SQL/62867/
Also understand that you don't need a physical Tally Table. The method that most other folks that avoided the loop used a "pseudo cursor" built into a "derived table" created by a VALUES clause.
To be sure, "looping correctly" in SQL Server has nothing to do with the explicit loops in a WHILE loop nor in a Recursive CTE (rCTE).
--Jeff Moden
January 22, 2018 at 7:19 pm
drew.allen - Monday, January 22, 2018 2:27 PMNote that it is NOT the beginning of the week.
"It Depends". Day 0 is a Monday, which is the first day of ISO Weeks.
--Jeff Moden
January 23, 2018 at 1:23 am
below86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.
The OP specifically asked for 24 months. Increasing this arbitrary limit, as you can see from other's code, is trivially easy.
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
January 23, 2018 at 7:00 am
ChrisM@Work - Tuesday, January 23, 2018 1:23 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.The OP specifically asked for 24 months. Increasing this arbitrary limit, as you can see from other's code, is trivially easy.
+1. And only a very limited number of months would ever reasonably be needed.
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 23, 2018 at 7:44 am
Jeff Moden - Monday, January 22, 2018 7:07 PMbelow86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
That's how performance problems start. People treat SQL as if it were "any program language". It's not. And you don't need to write explicit loops because every SELECT is powered by a machine language loop that I call a "pseudo cursor" (R. Barry Young first coined the phrase right here on SSC). You just need to learn about that just like you would learn any other language. Others that need to follow and understand the code need to learn it. If they don't then, just as "in any language", you can abstract (Black Box it) the functionality so that they don't even have to care about it.
If you want to learn more about the proverbial "Swiss Army Knife" of SQL Server, read and understand the following introductory article. It will change your career for the better.
http://qa.sqlservercentral.com/articles/T-SQL/62867/Also understand that you don't need a physical Tally Table. The method that most other folks that avoided the loop used a "pseudo cursor" built into a "derived table" created by a VALUES clause.
To be sure, "looping correctly" in SQL Server has nothing to do with the explicit loops in a WHILE loop nor in a Recursive CTE (rCTE).
Thanks for the info Jeff, I'll take a look at it. As I've stated now several times, the loop may not be the right answer for SQL. I only wanted to say even in SQL a loop is not something to be scared of. I would though guess that half the developers at my current shop and more than half at my prior shop would understand or follow the tally or "pseudo cursor". That's why I keep it simple, otherwise I have to maintain the code. Maybe every one else here is working with people that could completely understand the logic, and kudos to you. And again for this simple of a task the performance difference is non existent, it doesn't matter if you are running for 24 months or 3000. AND YES I WOULD USE A DIFFERENT SOLUTION IF WE WERE TALKING ABOUT LARGER AMOUNTS OF DATA. Not mad, just wanted to make sure everyone understands. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 23, 2018 at 7:47 am
ChrisM@Work - Tuesday, January 23, 2018 1:23 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.The OP specifically asked for 24 months. Increasing this arbitrary limit, as you can see from other's code, is trivially easy.
Yes, but in a follow up post it was asked to be able to send in different months needed. The loop code wouldn't need to be modified if the users changed from wanting 24 months to 48 or 3000. And I'm sure we've all seen users change there mind on how much data they need, or what 'reasonable' amount of data would be needed.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 23, 2018 at 7:51 am
This should have been:
" I would though guess that half the developers at my current shop and more than half at my prior shop wouldn't understand or follow the tally or "pseudo cursor". "
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 23, 2018 at 7:52 am
below86 - Tuesday, January 23, 2018 7:47 AMChrisM@Work - Tuesday, January 23, 2018 1:23 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.The OP specifically asked for 24 months. Increasing this arbitrary limit, as you can see from other's code, is trivially easy.
Yes, but in a follow up post it was asked to be able to send in different months needed. The loop code wouldn't need to be modified if the users changed from wanting 24 months to 48 or 3000. And I'm sure we've all seen users change there mind on how much data they need, or what 'reasonable' amount of data would be needed.
"is trivially easy"
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
January 23, 2018 at 8:23 am
below86 - Tuesday, January 23, 2018 7:44 AMThanks for the info Jeff, I'll take a look at it. As I've stated now several times, the loop may not be the right answer for SQL. I only wanted to say even in SQL a loop is not something to be scared of. I would though guess that half the developers at my current shop and more than half at my prior shop would understand or follow the tally or "pseudo cursor". That's why I keep it simple, otherwise I have to maintain the code. Maybe every one else here is working with people that could completely understand the logic, and kudos to you. And again for this simple of a task the performance difference is non existent, it doesn't matter if you are running for 24 months or 3000. AND YES I WOULD USE A DIFFERENT SOLUTION IF WE WERE TALKING ABOUT LARGER AMOUNTS OF DATA. Not mad, just wanted to make sure everyone understands. 🙂
There is no "may". The loop definitely is not the right answer for SQL in this instance. Yes, there are instances where a loop is the best approach, but this is not one of them.
You keep saying that you are keeping it simple, but you're not. You're keeping it familiar. Your code is actually more complex than any of the other variants, especially since you use a GOTO instead of a WHILE loop.
The logic here is not difficult to understand for anybody with any real experience in database development. If your coworkers don't understand the logic after having it explained to them, they should not be doing database development.
It doesn't matter that the size of the data is small. If you don't bother to learn the techniques for small data, you won't have the experience necessary when the data is larger and it does make a difference.
No, you're not trying to make everyone understand. You're trying to justify your laziness so that you don't need to change your coding practices.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2018 at 8:30 am
ChrisM@Work - Tuesday, January 23, 2018 7:52 AMbelow86 - Tuesday, January 23, 2018 7:47 AMChrisM@Work - Tuesday, January 23, 2018 1:23 AMbelow86 - Monday, January 22, 2018 10:58 AMIn a follow up post KGJ-dev wanted to be able to pass any number of months. That's why I tried each for more than 24.In my testing:
ChrisM's - limited to 24 months.
Drew's SQL didn't return correct results, see my original post on this thread.
KGJ-dev's SQL limited to 25 months
Scott's SQL was limited to 100 months returned. Only returned the last 100 months.
My SQL ran for 3000 months in less than a second. (this went back to Feb 1 1768). seems efficient enough to me. . limited only by not being able to go back beyond 01-01-0001.The OP specifically asked for 24 months. Increasing this arbitrary limit, as you can see from other's code, is trivially easy.
Yes, but in a follow up post it was asked to be able to send in different months needed. The loop code wouldn't need to be modified if the users changed from wanting 24 months to 48 or 3000. And I'm sure we've all seen users change there mind on how much data they need, or what 'reasonable' amount of data would be needed.
"is trivially easy"
Agreed it would be a trivial change, but the fact is it would need to be changed.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.†- Gail Shaw
So I did write it the simplest way(IMHO), and performance was more than adequate. With a 0.14 of a second for 3000 rows on a dev server, and 0.070 of a second on a prod server, more than adequate.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 23, 2018 at 8:30 am
KGJ-Dev - Friday, January 19, 2018 9:16 AMHi,I need to get Year, monthname in three letters, startdate and enddate of the month for last two years based on today’s date. Any sample query please
Please read any book on modern programming and pay attention to the concept of a tiered architecture in which the database layer passes data to a presentation layer that would do this this language dependent transform. Or you can get a simple kludge from any SQL forum and re-enforce bad habits.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 23, 2018 at 9:37 am
below86 - Tuesday, January 23, 2018 7:44 AMJeff Moden - Monday, January 22, 2018 7:07 PMbelow86 - Monday, January 22, 2018 12:49 PMI would say using 0 to represent 01-01-1900 or -1 to mean 12-31-1899 is far more 'tricky' of code than coding a loop. Coding a loop is (has been for me) one of the first things you learn how to do when learning any program language(BASIC< PASCAL, Assembler, COBOL, C, ...). And I could argue that if you can't code a loop correctly I have serious doubts on a cross join. An I believe in the 'Keep It Simple' philosophy. And if you don't have people you work with that can easily understand the more complicated logic, then you may be the one that ALWAYS maintains it. Now I'm not saying that using the cross join isn't a good solution for this. I'm just saying it may not be the easiest for others to follow and understand. I would say that anyone with general SQL knowledge would be able to understand/follow/ or modify the loop logic easier than the cross joins.All's I can say is WOW Drew, I ran the loop SQL to generate 3000 months and display them in .14 of a second, if you got it to run in 1/8 that time I'm impressed.
That's how performance problems start. People treat SQL as if it were "any program language". It's not. And you don't need to write explicit loops because every SELECT is powered by a machine language loop that I call a "pseudo cursor" (R. Barry Young first coined the phrase right here on SSC). You just need to learn about that just like you would learn any other language. Others that need to follow and understand the code need to learn it. If they don't then, just as "in any language", you can abstract (Black Box it) the functionality so that they don't even have to care about it.
If you want to learn more about the proverbial "Swiss Army Knife" of SQL Server, read and understand the following introductory article. It will change your career for the better.
http://qa.sqlservercentral.com/articles/T-SQL/62867/Also understand that you don't need a physical Tally Table. The method that most other folks that avoided the loop used a "pseudo cursor" built into a "derived table" created by a VALUES clause.
To be sure, "looping correctly" in SQL Server has nothing to do with the explicit loops in a WHILE loop nor in a Recursive CTE (rCTE).
Thanks for the info Jeff, I'll take a look at it. As I've stated now several times, the loop may not be the right answer for SQL. I only wanted to say even in SQL a loop is not something to be scared of. I would though guess that half the developers at my current shop and more than half at my prior shop would understand or follow the tally or "pseudo cursor". That's why I keep it simple, otherwise I have to maintain the code. Maybe every one else here is working with people that could completely understand the logic, and kudos to you. And again for this simple of a task the performance difference is non existent, it doesn't matter if you are running for 24 months or 3000. AND YES I WOULD USE A DIFFERENT SOLUTION IF WE WERE TALKING ABOUT LARGER AMOUNTS OF DATA. Not mad, just wanted to make sure everyone understands. 🙂
I agree that a loop in SQL isn't something to be scared of but on one and only one condition... it shouldn't be RBAR in nature. The same holds true with an rCTE. If it's not doing RBAR in nature, it can make for a real "Martha Stuart Moment". 😀
As for Developers not understanding code, that's were embedded comments come in. How are they (or you) going to learn if you never give them something different? I have a piece of artwork that I commissioned a friend to create. It's a drawing of a Cheshire cat laying in a litter box with his arms folded on the edge and a big ol' grin on his face with a half-dozen mice pointing and laughing at him. The caption on the drawing says "Before you can think outside of the box, you must first realize... YOU'RE IN A BOX!" 😀
Of course, if you also learn to use iTVFs (Inline Table Valued Functions) to encapsulate and abstract your code (a trick in all languages including T-SQL) instead of Scalar Functions or mTVFs (Multi-statement Table Valued Functions), then you've learned another performance trick-of-the-trade.
As for justifying using RBAR because of a supposed low row count, think of practicing the piano. Unless you're a comedian and part of your act is hitting the wrong notes on the piano, you should practice hitting the right notes all the time so that you don't even have to think of a high performance solution when chips are down and you really need a high performance solution which, once you get the hang of it, is a whole lot simpler and faster to write than writing RBAR.
The worst part about writing RBAR because of a supposed row count is because, like you said, you have Developers that don't yet understand. If they need to do a similar task with something much larger but don't know how, they may find your RBAR solution and model theirs after that and cause some unintentional but very real performance problems. And a low row count isn't all that you need to consider. You also have to consider how many times per second your code may be called in the future, possibly for another low row count usage. Rather than worry about all that (and you should worry to help your career), write it so it's fast even if there is a low row count.
Justifying RBAR due to a low row count also means that you lost the opportunity to teach others how to hit the right notes and, like I said, you might not know how to when you really need to because all you can play really well is "Chop-Sticks". 😉
As you say, I'm not mad and I'm not trying to be critical of your talents... we all bust a hump the best way we know how for the people we work for. I just want to make sure that YOU understand that we're trying to help you and that low-row-count justifications for RBAR cause seriously missed opportunities all the way around for you and your career. 😉 As Red-Green says, "We're all in this together and I'm pullin' for ya".
--Jeff Moden
January 23, 2018 at 9:47 am
jcelko212 32090 - Tuesday, January 23, 2018 8:30 AMPlease read any book on modern programming and pay attention to the concept of a tiered architecture in which the database layer passes data to a presentation layer that would do this this language dependent transform. Or you can get a simple kludge from any SQL forum and re-enforce bad habits.
BWAAAA-HAAAAA-HAAAAA!!!! Listen to you, mister "I really like the MySQL ability to store months as YYYY-MM-00 and years as YYYY-00-00", which is also non-ANSI/ISO compliant and a proprietary kludge, BTW.
--Jeff Moden
Viewing 15 posts - 31 through 45 (of 104 total)
You must be logged in to reply to this topic. Login to reply