March 11, 2012 at 8:57 am
siva 20997 (3/11/2012)
The last date of the previous month compared to 4/1/2012 is 3/31/2012 which is a Saturday. According to the OP's requirements, it should return the next weekday after that which, of course, should be Monday, 4/2/2012 and not the 4/1/2012 that your function returns.
On my system 31st March 2012 returns 2nd April 2012
Uh-huh... Perhaps it's a matter of interpretation on my part, but since 31st March 2012 is in March, the function should actually return 29th February 2012 for that date. It should also return 2nd April 2012 if you use 1st April 2012.
--Jeff Moden
March 11, 2012 at 12:09 pm
J Livingston SQL (3/11/2012)
Jeff Moden (3/10/2012)
Ok... my turn for a question, folks. Does anyone have a way to do this particular task using a Calendar Table? It doesn't even have to include holidays. I just want to see someone do it because I typically wouldn't use a Calendar Table for such a thing.And, no... I'm not bad mouthing anyone's suggestions to use a Calendar Table for this. I'd just like to see it done using one.
assuming (dangerous :-)) the "calendar" table had columns pre populated with identifieers for weekday and holidays, then couldnt something like this work?
DECLARE @DateEnq AS DATETIME
SELECT MIN(calendar_date) AS result
FROM Calendar
WHERE (is_weekday = 1)
AND (is_holiday = 0)
AND (calendar_date >= (Dateadd(mm, Datediff(mm, 0, @dateenq), 0)) -1)
-- calendar date greater than or equal to last day of previous month
Now that's more like it. There are just too many posts that say "use a Calendar" table without actually solving the problem on a given post with Calendar Table code. Well done and thanks a million, Graham. Of course, rising to the occasion isn't something new for you. You're always there "with a good one".
--Jeff Moden
March 11, 2012 at 12:32 pm
for any one interested...here is a quickly mocked up Calendar table and resultant code to answer OP's original question.....by using the calendar table approach.
Obviously without pre populating the Calendar table with "weekdays" and "holidays" this code cannot work.
Dependent upon your own needs, this may or may not be useful. The alternative is to process the data "on the fly".
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--PLEASE NOTE THAT THIS WILL DELETE ANY EXG TABLES WITH SAME NAME IN database tempdb....please amend if required
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
use [tempdb]
GO
--====Conditionally delete tables from [tempdb}
IF OBJECT_ID('tempdb..Tally', 'U') IS NOT NULL DROP TABLE tempdb..Tally ;
IF OBJECT_ID('tempdb..Calendar', 'U') IS NOT NULL DROP TABLE tempdb..Calendar ;
--==== Create a Tally table and a Calendar table
SELECT TOP 55001 IDENTITY(INT, 0, 1) AS N --- enough for 150 years
INTO dbo.Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
SET DATEFORMAT DMY
SET DATEFIRST 1 --- Monday = 1 Sunday = 7
DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME
SET @Date_Start = '01/01/2011'
SET @Date_End = '31/12/2012'
CREATE TABLE dbo.Calendar
(
calendar_date_ID INT IDENTITY(1, 1) NOT NULL,
calendar_week_ID INT,
calendar_date DATETIME PRIMARY KEY CLUSTERED,
calendar_year SMALLINT,
calendar_month TINYINT,
calendar_day TINYINT,
calendar_quarter TINYINT,
first_day_in_month DATETIME,
last_day_in_month DATETIME,
day_of_week TINYINT,
week_of_year TINYINT,
days_in_month TINYINT,
day_of_year SMALLINT,
is_weekday INT,
is_holiday INT default (0),
day_name VARCHAR (10),
month_name VARCHAR (10),
iso_date CHAR (8),
fiscal_year SMALLINT,
fiscal_month TINYINT
);
INSERT INTO dbo.Calendar
(calendar_date)
SELECT t.N + @Date_Start
FROM dbo.Tally t
WHERE t.N + @Date_Start <= @Date_End
UPDATE dbo.Calendar
SET calendar_week_ID = calendar_date_id / 7 + 1,
calendar_year = Datepart (YEAR, calendar_date),
--- fiscal year starts 01 OCT
fiscal_year = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1
ELSE Datepart (YEAR, calendar_date)
END,
calendar_month = Datepart (MONTH, calendar_date),
fiscal_month = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9
ELSE Datepart(M, calendar_date) + 3
END,
calendar_day = Datepart (DAY, calendar_date),
calendar_quarter = Datepart (QUARTER, calendar_date),
first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0),
last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1,
day_of_week = Datepart (WEEKDAY, calendar_date),
week_of_year = Datepart (WEEK, calendar_date),
day_of_year = Datepart (DAYOFYEAR, calendar_date),
is_weekday = CASE
WHEN Datepart (WEEKDAY, calendar_date) IN (6,7) THEN 0
ELSE 1
END ,
day_name = Datename (WEEKDAY, calendar_date),
month_name = Datename (MONTH, calendar_date),
iso_date = CONVERT(CHAR(8), calendar_date, 112),
days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 ))
select * from calendar
--===== I am looking for a function to identify whether last day of the previous month is weekday or a weekend.
-- If it is a weekend then it should return the next weekday.
-- For example. 31/12/2011 was saturday.
-- The function should return 02/01/2012 as it was the next working day(Monday).
-- If it was a weekday then it should return the date.
DECLARE @DateEnq AS DATETIME
SET @DateEnq = '05/01/2012'
SELECT MIN(calendar_date) AS result
FROM Calendar
WHERE (is_weekday = 1)
AND (is_holiday = 0)
AND (calendar_date >= (Dateadd(mm, Datediff(mm, 0, @dateenq), 0)) -1)
-- calendar date greater than or equal to last day of previous month
--- update Calendar table with holidays as required
UPDATE Calendar
SET is_holiday = 1
WHERE (calendar_date = 'YOURHOLIDATES')
-- rerun and test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 11:18 pm
Ok... I can't hold it back any longer. I've been holding back a bit (a lot, actually). I've used calendar tables on the job since I first learned how to use a Tally Table which was many years ago. In fact, it was an article on a Calendar Table that led me to some code that used a Tally Table (Numbers table, actually).
I hated that article on Calendar Tables. The code to do simple things like finding the 3rd Wednesday of July (for example) was comparatively complicated, difficult to read, and performed a lot worse than I expected it to even when properly indexed. And trying to solve the problem of being given a date and then finding something like 3 business days later worked well for a single date but absolutely dragged when it came to running large batches of such "delivery" dates.
That was all way back in SQL Server 2000 long before the joys of ROW_NUMBER(). Right after I discovered how to use Quirky Updates to make up for the missing ROW_NUMBER() function, I applied it to make two very special columns in the Calendar Table to make the previously mentioned problems a whole lot simpler and a whole lot faster. I've put off posting or writing an article about my version of the Calendar Table because it still requires the use of a Quirky Update to build one of the columns (at least until 2012 actually hits the streets) and I frankly didn't want to go through all the negativity from a lot of folks every time I post such a solution.
One reason why I wanted to see someone else post their solution was to see if anyone else might have been using a (as Celko now calls it) "Julianized Workday Number" to more quickly solve these types of problems. There may be others but Celko is the only other person I've seen use such a column.
To make a longer story shorter, Quirky Update or not, I guess it's high time for me to write an article on the subject of building and using a Calendar Table with these two very special columns.
Graham... you're the best. Not only did you provide a solution that used a Calendar Table, but you followed up with the construction of one. Well done. You've shown me that I shouldn't have allowed my trepidation's about posting another Quirky Update solution interfere. With that thought in mind, I'll post the version of the Calendar Table I'm going to use in the article along with some comparisons of methods for the problem in this post tomorrow night.
--Jeff Moden
March 12, 2012 at 2:00 am
I like being contrary so I have to ask, why are you using a calendar table?
DECLARE @YourDate DATETIME
SET @YourDate = '2012-01-12'
SELECT COALESCE(
CASE WHEN DATEPART(weekday,DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, @YourDate), 0))) IN (1,7)
THEN NULL ELSE DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, @YourDate), 0)) END
,CASE WHEN DATEPART(weekday,DATEADD(month, DATEDIFF(month, 0, @YourDate), 0)) IN (1,7)
THEN NULL ELSE DATEADD(month, DATEDIFF(month, 0, @YourDate), 0) END
,CASE WHEN DATEPART(weekday,DATEADD(day,1,DATEADD(month, DATEDIFF(month, 0, @YourDate), 0))) IN (1,7)
THEN NULL ELSE DATEADD(day,1,DATEADD(month, DATEDIFF(month, 0, @YourDate), 0)) END
)
I like calendar tables just as much as the next guy, but not for what I understand this question to be.
Note that the solution depends on DATEPART returning 1 or 7 for the weekend days (i.e., based on SET DATEFIRST).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2012 at 2:23 am
dwain.c (3/12/2012)
I like being contrary so I have to ask, why are you using a calendar table?
cos of this q from Jeff
Jeff Moden (3/10/2012)
Ok... my turn for a question, folks. Does anyone have a way to do this particular task using a Calendar Table? It doesn't even have to include holidays. I just want to see someone do it because I typically wouldn't use a Calendar Table for such a thing.And, no... I'm not bad mouthing anyone's suggestions to use a Calendar Table for this. I'd just like to see it done using one.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 12, 2012 at 2:40 am
Indeed, my bad for only skimming through the thread!
Jeff's was a valid question.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 12, 2012 at 4:51 am
oops, didn't see the thread end...
But still, want to mention that it's not a good idea to use scalar-values user defined SQL function for doing it.
In-line-sql, may look a bit bulky, will outperform such udf easily.
Also, cannot see benefits of using of Calendar Table for this particular case...
March 12, 2012 at 5:04 am
Eugene
Would like to know if there is lot of differnce in the perfomance bitween
User Defined Functions (like what I have posted)
and system defined functions like DateAdd and DateDiff
and if So why ?
March 12, 2012 at 5:26 am
siva 20997 (3/12/2012)
EugeneWould like to know if there is lot of differnce in the perfomance bitween
User Defined Functions (like what I have posted)
and system defined functions like DateAdd and DateDiff
and if So why ?
There is a huge difference in performance between user-defined-functions and system functions.
One of the main reasons: System functions are implemented in C++...
SQL Language is not procedural language, and it's not the best performer when it's used as such. It designed for set-based operations, that is why if you implement the logic "in-line-SQL" (in a select query), it will outperform many other methods.
At the end, if you really need a scalar-valued function, implement it as CLR function in C#.
In terms of performance you would have:
1. in-line-SQL
2. CLR function (if done properly)
3. Table-valued user defined SQL function
4. Scalar-valued user defined SQL function
I can see some people would argue place 2 and 3, but from my experience, it is so. The key for CLR function implementation - it should be done properly by the skilled c# man 🙂
March 12, 2012 at 5:45 am
I do agree with most of your points. But still fail to see couple of things from my understanding of how computers function.
Wheather the code is inLine or scalar function both are code written by humans. They have to be translated to Assembler/Machine code before execution. It is true of code written in C as well. Becasue the systems functions are Compiled and held at the system level will perfom better. also they are copiled and held most probably in Machine code.
I am not sure if line code and scalar functions are compiled once and executed many times or it compiled during every cycle( the differnce bitween Compilers and interpreters)
Other than that my understanding tells me inline and scalar valued functions should perform similarly. The differnce in speed will entirely dependent on how many CPU cycles it takes to execute that code
I would like to know if I am missing something in undertanding how SQL works ?
March 12, 2012 at 6:00 am
siva 20997 (3/12/2012)
I do agree with most of your points. But still fail to see couple of things from my understanding of how computers function.Wheather the code is inLine or scalar function both are code written by humans. They have to be translated to Assembler/Machine code before execution. It is true of code written in C as well. Becasue the systems functions are Compiled and held at the system level will perfom better. also they are copiled and held most probably in Machine code.
I am not sure if line code and scalar functions are compiled once and executed many times or it compiled during every cycle( the differnce bitween Compilers and interpreters)
Other than that my understanding tells me inline and scalar valued functions should perform similarly. The differnce in speed will entirely dependent on how many CPU cycles it takes to execute that code
I would like to know if I am missing something in undertanding how SQL works ?
Very valid points!
If you really need to understand it from "of how computers function" point of view, which is basically down to "how many CPU cycles it takes to execute that code", the answer is:
It's takes CPU considerable more number of cycles to execute user-defined scalar functions than system-defined functions in in-line-SQL code. The difference in a number of CPU cycles is so huge, that you are better not use SQL user-defined scalar-valued function when you can do it in in-line-sql. Also, it's big enough difference in CPU cycles compare to CLR (c#) function implementation, to make CLR implementation your second choice.
You need to analyse the Assembler code for the SQL Server execution to fully and deeply understand the reason behind of such bazaar behaviour :w00t:
Try this for the starter:
http://www.crews.org/curriculum/ex/compsci/articles/howcomput.htm
March 12, 2012 at 6:04 am
dwain.c (3/12/2012)
I like being contrary so I have to ask, why are you using a calendar table?
I'm with you and try to avoid having to reference a table if I don't have to. We actually have a couple of fast, simple, non-Calendar solutions for this. The reason why the Calendar table came up was because it's difficult to properly account for holidays without one. The OP didn't specify such a thing but it seems a logical thing to do.
--Jeff Moden
March 12, 2012 at 6:14 am
Eugene Elutin (3/12/2012)
siva 20997 (3/12/2012)
EugeneWould like to know if there is lot of differnce in the perfomance bitween
User Defined Functions (like what I have posted)
and system defined functions like DateAdd and DateDiff
and if So why ?
There is a huge difference in performance between user-defined-functions and system functions.
One of the main reasons: System functions are implemented in C++...
SQL Language is not procedural language, and it's not the best performer when it's used as such. It designed for set-based operations, that is why if you implement the logic "in-line-SQL" (in a select query), it will outperform many other methods.
At the end, if you really need a scalar-valued function, implement it as CLR function in C#.
In terms of performance you would have:
1. in-line-SQL
2. CLR function (if done properly)
3. Table-valued user defined SQL function
4. Scalar-valued user defined SQL function
I can see some people would argue place 2 and 3, but from my experience, it is so. The key for CLR function implementation - it should be done properly by the skilled c# man 🙂
For the most part, I agree but there are some grand exceptions. There are many places where T-SQL code will beat SQLCLR simply because the T-SQL doesn't have to go through an API. An example of such a thing is some simple cases of RegEx. There are also places where Scalar Functions will handily beat Inline code and iTVFs. A good example of such a thing would be a function to replace the first letter of every word with a capitalized version. Oddly enough, not only does the Scalar function win but it also uses a While Loop.
Rules of thumb are great guides but they should only inspire, not necessarily restrict because for nearly every rule, there's an high performance exception. I've seen lot's of people miss out on some very high performance solutions because their "Rules of thumb" prevented them from even trying alternate ideas.
--Jeff Moden
March 12, 2012 at 6:19 am
J Livingston SQL (3/12/2012)
dwain.c (3/12/2012)
I like being contrary so I have to ask, why are you using a calendar table?cos of this q from Jeff
Jeff Moden (3/10/2012)
Ok... my turn for a question, folks. Does anyone have a way to do this particular task using a Calendar Table? It doesn't even have to include holidays. I just want to see someone do it because I typically wouldn't use a Calendar Table for such a thing.And, no... I'm not bad mouthing anyone's suggestions to use a Calendar Table for this. I'd just like to see it done using one.
I asked the question because someone posted that you could solve this problem with a Calendar Table and pointed to an article that didn't answer the question at hand. :laugh:
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply