February 25, 2019 at 3:31 am
Hello,
it would be great if someone could help me with the following problem.
I need to write a query, i guess that recursive one, that will union several query results into one table. Each query differs from another only by a month value passed to WHERE clause. Month values increases by 1 from start date till present date month.
So it could look somehow like below query:DECLARE @StartDate As Date = '20180101'
;WITH CTE_Recursive AS
(
SELECT *
FROM dbo.SomeTable
UNION ALL
SELECT *
FROM CTE_Recursive As S
JOIN dbo.Sometable as M ON S.Id = M.id
)
SELECT *
FROM CTE_Recursive
WHERE DATEADD(Month,1,@StartDate) <= Month(GetDate())
What improvments do i need to make to run this query successfully?
Best regards
Lukas
February 25, 2019 at 4:12 am
I don't think your query will do what you want. It's selecting the same results in the recursive part of the query that its selecting in the anchor part.
It would be better if you could supply details of what you want the query to do in more detail with an example.
You should be able to get the results in one query with no union, you just need something to generate a range of dates differing by 1 month.
Here is a table valued function that you can use to generate a range of dates:/*-- **********************************************************************
-- FUNCTION: DateRange
-- Returns a table of datetime values based on the parameters
-- Parameters:
-- @StartDate :Start date of the series
-- @EndDate :End date of the series
-- @DatePart :The time unit for @interval
-- ns : nanoseconds
-- mcs : microseconds
-- ms : milliseconds
-- ss : seconds
-- mi : minutes
-- hh : hours
-- dd : days
-- ww : weeks
-- mm : months
-- qq : quarters
-- yy : years
-- @Interval :The number of dateparts between each value returned
--
-- Sample Calls:
-- SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
-- SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
-- SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default)
-- SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7)
-- SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
-- **********************************************************************/
ALTER FUNCTION [dbo].[DateRange]
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)='dd',
@Interval int=1
)
RETURNS TABLE 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)),
B(RowNum) 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 DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
END) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
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 B
CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
GO
If you install this function then you can generate a range of months like this:DECLARE @StartDate As Date = '20180101'
SELECT * FROM [dbo].[DateRange](@StartDate, GETDATE(),'mm',1)
You could then join this tvf you your table wth a query something like:SELECT *
FROM [dbo].[DateRange](@StartDate, GETDATE(),'mm',1) d
INNER JOIN dbo.SomeTable s ON s.DATE BETWEEN d.Value and DATEADD(mm,1,d.Value)
But as you haven't supplied enough details I can't go any further.
February 25, 2019 at 7:05 am
Thank you very much for a response.
Unfortunatelly, i can't alter or add any functions on the server.
February 25, 2019 at 7:14 am
lukaszpiech - Monday, February 25, 2019 7:05 AMThank you very much for a response.
Unfortunatelly, i can't alter or add any functions on the server.
Then you can add the SQL that's in the function directly into your query:DECLARE @StartDate As Date = '20180101';
;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)),
B(RowNum) AS (SELECT TOP(ABS(DATEDIFF(mm, GETDATE(), @StartDate)/1)+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
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 DATEADD(mm, T.AddAmount, @StartDate) [Date]
FROM B
CROSS APPLY(VALUES ((RowNum))) T(AddAmount)
INNER JOIN dbo.SomeTable s ON s.DATE BETWEEN b.[Date] and DATEADD(mm,1,b.[Date])
February 25, 2019 at 8:35 am
lukaszpiech - Monday, February 25, 2019 3:31 AMHello,
it would be great if someone could help me with the following problem.
I need to write a query, i guess that recursive one, that will union several query results into one table. Each query differs from another only by a month value passed to WHERE clause. Month values increases by 1 from start date till present date month.
So it could look somehow like below query:DECLARE @StartDate As Date = '20180101';WITH CTE_Recursive AS
(
SELECT *
FROM dbo.SomeTableUNION ALL
SELECT *
FROM CTE_Recursive As S
JOIN dbo.Sometable as M ON S.Id = M.id
)
SELECT *
FROM CTE_Recursive
WHERE DATEADD(Month,1,@StartDate) <= Month(GetDate())What improvments do i need to make to run this query successfully?
Best regards
Lukas
There are some serious problems with your query that prevent us from understanding what you need.
1. The semicolon is a statement terminator, not a statement beginator
2. Your anchor select has half the columns that your recursive part. That's hidden because you used a star (*) instead of a column list with qualified names.
3. Your recursive query will hit the max recursion settings because you're joining on the same column.
4. Your WHERE clause doesn't have any columns included.
5. DATEADD returns a datetime value and MONTH returns an integer value from 1 to 12. That's like comparing the grapes and watermelons (not even apples and oranges).
All these problems show that you have no idea of what you're doing with your query and should take a step back and think again before using things that you don't understand their usage. This is rough, but is like grabbing a chainsaw to cut paper.
That said, you're not new to the forums. You should know by now that is a good courtesy to post sample data and expected results in a consumable way. If you still don't know how to do it, read the articles on my signature and post again sharing all possible details that will help someone to guide you to a solution. I'm sorry for the Celko-like post, but some "tough love" is needed at times.
February 25, 2019 at 10:37 am
Luis Cazares - Monday, February 25, 2019 8:35 AMThere are some serious problems with your query that prevent us from understanding what you need.
1. The semicolon is a statement terminator, not a statement beginator
While most of your statement is true, I have to disagree with the above. This is neither a serious problem nor will it prevent anyone from understanding the query. It is simply a difference in coding style. Also, the parser has absolutely no problem understanding the meaning. Instead of recognizing the fact that the English is an imprecise description of the actual syntax, you are trying to arbitrarily force the syntax into a specific form.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 25, 2019 at 10:41 am
drew.allen - Monday, February 25, 2019 10:37 AMLuis Cazares - Monday, February 25, 2019 8:35 AMThere are some serious problems with your query that prevent us from understanding what you need.
1. The semicolon is a statement terminator, not a statement beginatorWhile most of your statement is true, I have to disagree with the above. This is neither a serious problem nor will it prevent anyone from understanding the query. It is simply a difference in coding style. Also, the parser has absolutely no problem understanding the meaning. Instead of recognizing the fact that the English is an imprecise description of the actual syntax, you are trying to arbitrarily force the syntax into a specific form.
Drew
I've got to say I have no problem with wherever semicolons are put. I like to think of them just as statement separators.
But I know that if you don't begin a CTE with a semicolon on a forum, someone will paste the statement into their code in SSMS and then not be able to figure out why it doesn't work.
February 25, 2019 at 10:56 am
drew.allen - Monday, February 25, 2019 10:37 AMLuis Cazares - Monday, February 25, 2019 8:35 AMThere are some serious problems with your query that prevent us from understanding what you need.
1. The semicolon is a statement terminator, not a statement beginatorWhile most of your statement is true, I have to disagree with the above. This is neither a serious problem nor will it prevent anyone from understanding the query. It is simply a difference in coding style. Also, the parser has absolutely no problem understanding the meaning. Instead of recognizing the fact that the English is an imprecise description of the actual syntax, you are trying to arbitrarily force the syntax into a specific form.
Drew
While I accept that the problem won't prevent anyone from understanding the query, it's still an indicator that the coder is not aware of what the semicolon does. That's not a difference in coding style, it's simply a bad coding habit. The semicolon belongs to the previous statement as clearly stated on BOL.
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
If it was simple formatting, you could create a view like
CREATE VIEW SomeView
AS
;WITH CTE AS(
SELECT * FROM SomeTable
)
SELECT *
FROM CTE
The previous example will obviously fail because the semicolon is no a simple format utility and actually defines the end of a statement.
February 25, 2019 at 12:35 pm
lukaszpiech - Monday, February 25, 2019 3:31 AM. If you still don't know how to do it, read the articles on my signature and post again sharing all possible details that will help someone to guide you to a solution. I'm sorry for the Celko-like post, but some "tough love" is needed at times.
LOL! Now you're getting a taste of how I feel about people that ask rude stupid questions without bothering to do any research. My favorite the doesn't happen so much anymore, was Russians who would tell me they had downloaded bootleg copies of my books and now expected me to do their homework for them. Later, it was Indians doing exactly the same things the Russians had done!
In spite of the fact that we have had netiquette for SQL forums established for over 30 years, nobody bothers to read or follow it. Originally, I try to be super polite to newbies. It literally did not work. They never heard what I was trying to tell them about the basics of RDBMS, SQL and programming.No matter what I said to them, they would assume that I told them what they were doing was just fine. My wife is a Soto Zen priest, and they had a little thing that when people are meditating they whack the hell out of them with a stick to get their attention and make them focus.
Obviously, I cannot put a stick on people doing dumb questions, so I settled for being brutal about what they're doing wrong.. When I'm talking about doing something "my way" I'm pretty good about being honest about it being my opinion, but when it's a matter of Dr. Codd, ANSI/ISO standards, etc. I also label that explicitly is something that a professional should do.
Would you really like to go to a hospital where the doctors did not know the basic principles of their trade?
Please post DDL and follow ANSI/ISO standards when asking for help.
February 25, 2019 at 12:47 pm
>> it would be great if someone could help me with the following problem. <<
Where is the DDL? Did you read the basic netiquette of this forum or you simply expect everybody to serve you by reading your mind?
>> need to write a query, I know know guess that recursive one, that will UNION several query results into one table. Each query differs from another only by a month value passed to WHERE clause. ..<< Query
Your approach is still based on procedural code. We don't UNION several queries, because SQL is a set oriented language. You're still thinking in terms of Fortran or COBOL or some other procedural language. What you want to do is create a view or a table that includes the entire domain that you're looking at. Once that is established in your schema, you can then pick out any subset by the monthA
Please post DDL and follow ANSI/ISO standards when asking for help.
February 25, 2019 at 1:27 pm
Luis Cazares - Monday, February 25, 2019 10:56 AMdrew.allen - Monday, February 25, 2019 10:37 AMLuis Cazares - Monday, February 25, 2019 8:35 AMThere are some serious problems with your query that prevent us from understanding what you need.
1. The semicolon is a statement terminator, not a statement beginatorWhile most of your statement is true, I have to disagree with the above. This is neither a serious problem nor will it prevent anyone from understanding the query. It is simply a difference in coding style. Also, the parser has absolutely no problem understanding the meaning. Instead of recognizing the fact that the English is an imprecise description of the actual syntax, you are trying to arbitrarily force the syntax into a specific form.
Drew
While I accept that the problem won't prevent anyone from understanding the query, it's still an indicator that the coder is not aware of what the semicolon does. That's not a difference in coding style, it's simply a bad coding habit. The semicolon belongs to the previous statement as clearly stated on BOL.
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
If it was simple formatting, you could create a view like
CREATE VIEW SomeView
AS
;WITH CTE AS(
SELECT * FROM SomeTable
)
SELECT *
FROM CTE
The previous example will obviously fail because the semicolon is no a simple format utility and actually defines the end of a statement.
Technically the semicolon IS following the previous statement %^)
February 25, 2019 at 1:39 pm
patrickmcginnis59 10839 - Monday, February 25, 2019 1:27 PMTechnically the semicolon IS following the previous statement %^)
Exactly, following the previous statement, not leading the next one.
February 25, 2019 at 2:33 pm
Luis Cazares - Monday, February 25, 2019 1:39 PMpatrickmcginnis59 10839 - Monday, February 25, 2019 1:27 PMTechnically the semicolon IS following the previous statement %^)Exactly, following the previous statement, not leading the next one.
No, the semicolon in the discussed post both follows the previous statement, and precedes the statement containing the CTE. The carriage return in this situation is whitespace. All other arguments are formatting, they do not concern syntax except in cases when carriage returns are themselves used to separate statements, which in the case of the rule of CTE's having to have the previous statement terminated by a semicolon does not prevent that semicolon from being separated from the previous statement by a carriage return. This should be obvious by now because we are familiar enough with the construct that it must obviously get successfully parsed by the SQL Server program.
I have no argument with it being characterized as a formatting thing, but using it to berate some fellow on his knowledge well, its just too SSC-like for me to not post my objections. Here's a more clear example using your very own text:
...
CREATE VIEW SomeView
AS
;WITH CTE AS(
SELECT * FROM SomeTable
)
SELECT *
FROM CTE
Lets say the semicolon is a statement terminator.
The above is invalid because the semicolon is a statement terminator. It terminates the "ATTEMPTED" statement "CREATE VIEW SomeView AS", which is not a legal statement in SQL. We could probably argue the same thing if we called the semicolon a statement separator. Since it is only mandatory in certain cases, using the semicolon as a statement SEPARATOR only IS PERFECTLY LEGAL. The following is also incorrect by the same reasoning although it in this case follows your formatting preferences:
CREATE VIEW SomeView
AS;
WITH CTE AS(
SELECT * FROM SomeTable
)
SELECT *
FROM CTE
I suspect the above will not work either although you sure seem to like it.
Here's another example with two different styles that produce equivalent parsed trees:
SELECT COLUMN1 FROM TABLE1;
SELECT
COLUMN1
FROM
TABLE1
;
I have no argument with formatting styles, but lets call this what it is, a particular formatting style that you don't use or like, but to imply it informs of the OP's knowledge well lets just say I disagree.
February 25, 2019 at 2:48 pm
patrickmcginnis59 10839 - Monday, February 25, 2019 2:33 PMHere's another example with two different styles that produce equivalent parsed trees:
SELECT COLUMN1 FROM TABLE1;
SELECT
COLUMN1
FROM
TABLE1
;I have no argument with formatting styles, but lets call this what it is, a particular formatting style that you don't use or like, but to imply it informs of the OP's knowledge well lets just say I disagree.
Those are formatting styles. Both use the semicolon to define the end of the SELECT statement.
.If you believe that writing like this is ridiculous .Then maybe you can see my point .You don't start sentences with a period .You end them with one
February 25, 2019 at 3:01 pm
The "WITH" from a CTE can cause errors if not preceded by a ; if an actual statement preceded the WITH. Therefore it's safer to prepend a ; so that if someone adds another statement before it without the ; that errors don't occur. If you code an extra ;, so what, the time to parse just a ; is so small you can't accurately measure it.
How is that worth so much caviling about? Get some program to add the ;s if you're really that hyper about them, but I'm not spending my time worrying about it: it's just not practical.
Btw, in the code below, does the END after if begin else take a ;? Does the BEGIN itself? Are you sure without checking? If you're gonna be hyper enough to insist that all semicolons be coded, then shouldn't you code all semicolons?
declare @var1 int
set @var1 = 5
if @var1 < 9
begin
print 'abc'
select case when @var1 = 9
then 'abc' else 'def' end as q1
end /is *; "needed" here to end statement, or would that cause the else to give an error because the if is ended? */
else
begin
print 'xyz'
select case when @var1 = 11
then 'xyz' else 'uvw' end as q2
end
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply