August 30, 2019 at 4:14 am
IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2019 at 4:32 am
IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".
Not that I doubt you Jeff (I've learned never to do that! 🙂 ) but I don't follow you in reference to the term "Correlated". Table A (with 100 rows) cross joined with Table B (with 15 rows) will produce a result set with 1500 rows without any join criteria or limitation of values from one table to the other. Doesn't correlation mean a reference or relationship between the two tables? Could you let me in on your rationale?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 30, 2019 at 4:46 am
Just for giggles and grins today, I wrote a version of Jonathan's function that is similar but does not use the CROSS JOIN nor the IIF/CASE statements:
CREATE FUNCTION dbo.fnDateTimeRange(@StartDate datetime2, @EndDate datetime2, @DatePart varchar(3)='dd', @Interval int=1)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
,e4(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b CROSS JOIN e1 c CROSS JOIN e1 d) -- 16^4 or 65,536 records (256*256)
,cteTally(seqnbr, direction) AS (
SELECT TOP(ABS(CASE @DatePart WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
ELSE ABS(DATEDIFF(dd, @EndDate, @StartDate))/@Interval
END) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1,
CASE WHEN @StartDate < @EndDate THEN 1 ELSE -1 END
FROM e4 a CROSS JOIN e4 b-- 16^8 or 4,294,967,296 records (65,536*65,536)
)
SELECT CASE @DatePart WHEN 'ns' THEN DATEADD(ns, direction * seqnbr * @interval, @StartDate)
WHEN 'mcs' THEN DATEADD(mcs,direction * seqnbr * @interval, @StartDate)
WHEN 'ms' THEN DATEADD(ms, direction * seqnbr * @interval, @StartDate)
WHEN 'ss' THEN DATEADD(ss, direction * seqnbr * @interval, @StartDate)
WHEN 'mi' THEN DATEADD(mi, direction * seqnbr * @interval, @StartDate)
WHEN 'hh' THEN DATEADD(hh, direction * seqnbr * @interval, @StartDate)
WHEN 'dd' THEN DATEADD(dd, direction * seqnbr * @interval, @StartDate)
WHEN 'ww' THEN DATEADD(ww, direction * seqnbr * @interval, @StartDate)
WHEN 'mm' THEN DATEADD(mm, direction * seqnbr * @interval, @StartDate)
WHEN 'qq' THEN DATEADD(qq, direction * seqnbr * @interval, @StartDate)
WHEN 'yy' THEN DATEADD(yy, direction * seqnbr * @interval, @StartDate)
ELSE DATEADD(dd, direction * seqnbr * @interval, @StartDate)
END AS DateTimeValue
FROM cteTally;
GO
I'd appreciate any feedback on it.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 30, 2019 at 11:57 am
Hi Aaron, Yes, good idea to remove the cross apply and put the logic in the CTE. Also, changing the IIF to CASE means it should work on older versions of SQL Server. I've amended my code a bit with your ideas, it's made the code a little bit shorter and processing very slightly faster.
ALTER FUNCTION [dbo].[DateRange]
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)='dd',
@Interval int=1
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
T(AddAmount) AS (SELECT TOP(ABS(CASE @DatePart
WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
ELSE DATEDIFF(dd, @EndDate, @StartDate)/@Interval
END) + 1)
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * CASE WHEN @StartDate < @EndDate THEN 1 ELSE -1 END * @Interval
FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
SELECT CASE @DatePart
WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
ELSE DATEADD(dd, T.AddAmount, @StartDate)
END [Value]
FROM T
August 30, 2019 at 1:29 pm
Jeff Moden wrote:IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".
Not that I doubt you Jeff (I've learned never to do that! 🙂 ) but I don't follow you in reference to the term "Correlated". Table A (with 100 rows) cross joined with Table B (with 15 rows) will produce a result set with 1500 rows without any join criteria or limitation of values from one table to the other. Doesn't correlation mean a reference or relationship between the two tables? Could you let me in on your rationale?
The "correlation" is that for every row in Table A, something (Action A) will be done with it (more like an OUTER APPLY because of the "every row" comment). You can easily accomplish the same thing through the use of a Sub-Query in a SELECT list, which frequently has a lookup in another table and correlates that lookup between the current row and the table being used for the lookup. If it's not a table that you're looking up for every row, but a formula instead, it's still very much the same as CROSS APPLY with the only real difference being that a correlated sub-query in a SELECT list must be constrained to return a single value rather than multiple values as you can do in an APPLY (CROSS or OUTER).
An example is that you can actually use an iTVF in the SELECT list of a query using a correlated sub-query if the iTVF returns only one value (implied iSF). The iTVF can contain a formula or a reference to another table. It could just be a SELECT though.
Another example is that (as you're seriously aware), that we used to join Tally Tables in the from clause and it had a correlation between some value in Table A (such as the length of the column) and which range of values would be returned from the Tally Table to get the necessary Relational Muliplication.
I actually don't like the term CROSS APPLY because too many people related it to cross JOINs. They should have named it INNER APPLY because that's what it really boils down to. And, yes, you can get an accidental (or intentional) CROSS JOIN out of it just like you can with a correlated sub-query or even a simple bit of bad criteria in an INNER JOIN.
As a bit of a sidebar, when I first found out what CROSS APPLY did, my first thought was "COOL! Now I don't have to write correlated sub-queries in the SELECT list anymore".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2019 at 2:45 pm
The "correlation" is that for every row in Table A, something (Action A) will be done with it (more like an OUTER APPLY because of the "every row" comment). You can easily accomplish the same thing through the use of a Sub-Query in a SELECT list, which frequently has a lookup in another table and correlates that lookup between the current row and the table being used for the lookup. If it's not a table that you're looking up for every row, but a formula instead, it's still very much the same as CROSS APPLY with the only real difference being that a correlated sub-query in a SELECT list must be constrained to return a single value rather than multiple values as you can do in an APPLY (CROSS or OUTER).
That's where I don't follow you. A correlated sub-query in a SELECT list, as you pointed out, must return a single value so therefore has no effect on the number of rows returned.
Another example is that (as you're seriously aware), that we used to join Tally Tables in the from clause and it had a correlation between some value in Table A (such as the length of the column) and which range of values would be returned from the Tally Table to get the necessary Relational Muliplication.
This is the part that I'm much more familiar with and even count upon. In fact, I've used Tally Tables more often as an INNER JOIN so that I can apply constraints to the Tally Table (such as string length). However, I've also used them as unbounded to produce a row where otherwise there would not be (such as finding missing dates) and that's where the CROSS APPLY becomes handy.
I actually don't like the term CROSS APPLY because too many people related it to cross JOINs. They should have named it INNER APPLY because that's what it really boils down to. And, yes, you can get an accidental (or intentional) CROSS JOIN out of it just like you can with a correlated sub-query or even a simple bit of bad criteria in an INNER JOIN.
I look at CROSS APPLY as to mean "apply every row of this table to every row of the other table(s)". In that sense, it seems to me that an OUTER APPLY would be more applicable than INNER APPLY since INNER implies some sort of restriction. Therefore, what I said earlier about a CROSS APPLY being like an INNER JOIN ON 1=1 is not correct.
As always Jeff, I appreciate your insight!!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
October 8, 2019 at 12:25 pm
is this compatible with SQL2000?
October 8, 2019 at 12:48 pm
damn, that scuppers that idea then.
as i have a problem that this might have been a solution for.
October 8, 2019 at 2:39 pm
damn, that scuppers that idea then.
as i have a problem that this might have been a solution for.
I believe that there is a solution. I'll need a bit of time to test my theory, so have a bit of patience.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
October 8, 2019 at 2:52 pm
many thanks!
just for reference - this is thread explaining what i am trying to achieve
October 8, 2019 at 3:17 pm
It's been a while since I've worked on SQL 2000 so I'm a bit rusty on what's allowable and what's not. I also realized that I couldn't set my 2017 database to 2000 compatibility mode!
I'm thinking that perhaps the best thing to do is to create a DATE_DIM table like what's used in data warehouses. We may have to use some ugly T-SQL that works in SQL 2000, but once created then it's available. According to the post you reference time was not an issue, correct?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
October 8, 2019 at 3:19 pm
yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the important ones.
I'll drop you a PM as i may be able to give you access to an SQL 2000 database if you need it.
March 25, 2020 at 9:48 pm
yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the important ones.
I'll drop you a PM as i may be able to give you access to an SQL 2000 database if you need it.
Did you ever solve your problem for 2000? If not, post the problem in the "2000" forum on this site and PM me with the link to the post.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2021 at 12:02 am
Jeff Moden wrote:Nice function, Jonathan. Well done!
Thank you Jeff, that's a great compliment coming from someone as experienced as you.
I just used the function to hammer out a solution to a forum question. It made stuff wicked easy. Again, nicely done, Mr. Roberts! You should have done this as a lightning -round for PASS Summit this year! Maybe next year?
Here's the link for the forum question...
https://www.sqlservercentral.com/forums/topic/tsql-create-dynamic-partition-datarange#post-3944333
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply