October 6, 2017 at 3:03 pm
TheCTEGuy - Friday, October 6, 2017 6:50 AMPhil Parkin - Friday, October 6, 2017 6:43 AMTheCTEGuy - Friday, October 6, 2017 4:53 AMHey Luis - Please review the code below - will this perform better from your code -
declare @st datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);;with cte as (
; is a terminator, not an initiator.
Are you trying to say that the code should like the below code ?
declare @st datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);with cte as (
Select @st as dte,year(@st) as yr,month(@st) as mnth
union all
Select dte+1 as dte2,year(dte),month(dte) from cte where dte<getdate()
)select *,min(dte)over (partition by yr,mnth) as MinDate ,max(dte)over (partition by yr,mnth) as MAXDate
from
cte
order by dte
option(maxrecursion 0)
Lordy, I hope not. That uses an rCTE that counts incrementally. Please see the following article for why that's so bad. I can even write WHILE loops that will beat it.
Hidden RBAR: Counting with Recursive CTE's
Also, (I certainly could be incorrect) but I believe that the code is also producing incorrect answers near the beginning and end of each month.
--Jeff Moden
October 6, 2017 at 11:35 pm
First solve the problem then write the code !
October 7, 2017 at 11:07 am
TheCTEGuy - Friday, October 6, 2017 11:35 PMJeff - Thanks for that article. It seems rCTEs are a performance bottlenecks. But the code above doesn't give wrong results.
Luis - i don't have scripts of functions that u used in your script. Can u compare it with my query?
Thanks 🙂
I could be totally wrong about what the requirements are but I'm seeing what I consider to be 3 incorrect items during the first of every month except the very first month. Run the following code and see. It uses your code to create the #Results table and then it searches for where the month of the "dte" column don't match what's in the "mnth" column. I'm also seeing that the MinDate column isn't the first of the month.except for the first month of the result set and both the min and max dates aren't for the current month... they're for the next month.
--===== Your exact code except that I added INTO #MyHead to preserve the results for validation.
declare @st datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);
with cte as (
Select @st as dte,year(@st) as yr,month(@st) as mnth
union all
Select dte+1 as dte2,year(dte),month(dte) from cte where dte<getdate()
)
select *,min(dte)over (partition by yr,mnth) as MinDate ,max(dte)over (partition by yr,mnth) as MAXDate
INTO #MyHead
from
cte
order by dte
option(maxrecursion 0)
;
--==== Demonstrate the flawed results for the first of every month.
-- All rows but the very first row have the same flaws even when
-- not on the first of the month.
SELECT * FROM #MyHead WHERE DATEPART(dd,dte) = 1
;
Results from that code...
dte yr mnth MinDate MAXDate
----------------------- ---- ---- ----------------------- -----------------------
1997-01-01 00:00:00.000 1997 1 1997-01-01 00:00:00.000 1997-02-01 00:00:00.000 --This is the only correct row.
1997-02-01 00:00:00.000 1997 1 1997-01-01 00:00:00.000 1997-02-01 00:00:00.000
1997-03-01 00:00:00.000 1997 2 1997-02-02 00:00:00.000 1997-03-01 00:00:00.000
1997-04-01 00:00:00.000 1997 3 1997-03-02 00:00:00.000 1997-04-01 00:00:00.000
1997-05-01 00:00:00.000 1997 4 1997-04-02 00:00:00.000 1997-05-01 00:00:00.000
1997-06-01 00:00:00.000 1997 5 1997-05-02 00:00:00.000 1997-06-01 00:00:00.000
1997-07-01 00:00:00.000 1997 6 1997-06-02 00:00:00.000 1997-07-01 00:00:00.000
1997-08-01 00:00:00.000 1997 7 1997-07-02 00:00:00.000 1997-08-01 00:00:00.000
1997-09-01 00:00:00.000 1997 8 1997-08-02 00:00:00.000 1997-09-01 00:00:00.000
1997-10-01 00:00:00.000 1997 9 1997-09-02 00:00:00.000 1997-10-01 00:00:00.000
1997-11-01 00:00:00.000 1997 10 1997-10-02 00:00:00.000 1997-11-01 00:00:00.000
1997-12-01 00:00:00.000 1997 11 1997-11-02 00:00:00.000 1997-12-01 00:00:00.000
1998-01-01 00:00:00.000 1997 12 1997-12-02 00:00:00.000 1998-01-01 00:00:00.000
1998-02-01 00:00:00.000 1998 1 1998-01-02 00:00:00.000 1998-02-01 00:00:00.000
1998-03-01 00:00:00.000 1998 2 1998-02-02 00:00:00.000 1998-03-01 00:00:00.000
1998-04-01 00:00:00.000 1998 3 1998-03-02 00:00:00.000 1998-04-01 00:00:00.000
1998-05-01 00:00:00.000 1998 4 1998-04-02 00:00:00.000 1998-05-01 00:00:00.000
1998-06-01 00:00:00.000 1998 5 1998-05-02 00:00:00.000 1998-06-01 00:00:00.000
1998-07-01 00:00:00.000 1998 6 1998-06-02 00:00:00.000 1998-07-01 00:00:00.000
1998-08-01 00:00:00.000 1998 7 1998-07-02 00:00:00.000 1998-08-01 00:00:00.000
1998-09-01 00:00:00.000 1998 8 1998-08-02 00:00:00.000 1998-09-01 00:00:00.000
1998-10-01 00:00:00.000 1998 9 1998-09-02 00:00:00.000 1998-10-01 00:00:00.000
1998-11-01 00:00:00.000 1998 10 1998-10-02 00:00:00.000 1998-11-01 00:00:00.000
1998-12-01 00:00:00.000 1998 11 1998-11-02 00:00:00.000 1998-12-01 00:00:00.000
1999-01-01 00:00:00.000 1998 12 1998-12-02 00:00:00.000 1999-01-01 00:00:00.000
1999-02-01 00:00:00.000 1999 1 1999-01-02 00:00:00.000 1999-02-01 00:00:00.000
1999-03-01 00:00:00.000 1999 2 1999-02-02 00:00:00.000 1999-03-01 00:00:00.000
--Jeff Moden
October 9, 2017 at 10:38 am
Luis Cazares - Thursday, October 5, 2017 10:39 AMThis migh also work.
DECLARE @Start datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteCalendar(calDate) AS(
SELECT TOP (DATEDIFF( DD, @Start, GETDATE()) + 1)
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @Start) calDate
FROM E4
)
SELECT CAST( CAST( YEAR(calDate) AS char(4)) AS datetime),
calDate
FROM cteCalendar
WHERE EXISTS((SELECT * FROM report.fnReportDealCore_original( CAST( YEAR(calDate) AS char(4)), calDate, -1)
EXCEPT
SELECT * FROM report.fnReportDealCore( CAST( YEAR(calDate) AS char(4)), calDate, -1))
UNION ALL
(SELECT * FROM report.fnReportDealCore( CAST( YEAR(calDate) AS char(4)), calDate, -1)
EXCEPT
SELECT * FROM report.fnReportDealCore_original( CAST( YEAR(calDate) AS char(4)), calDate, -1))) ;
Luis - Apologies as I was out on an emergency and couldn't respond. Could you please let me know if this would suffice my task or is there any changes that I should make in the script in order to make it work. I haven't tested your scripts yet but will give it a shot once you confirm. Also, below is the code that I came up with. However, I am not a 100% sure if this would work or am I missing something.
declare @StartDate date, @EndDate date, @counter int = (select datediff(mm, '1/1/1997', '9/30/2017'));
while @counter >= 0
begin
set @StartDate = dateadd(mm, @counter, '1/1/1997');
set @EndDate = dateadd(mm, @counter, '1/31/1997');
--Finding results in the original that are not in the new /*
Select x, y, z FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
EXCEPT Select x, y, z FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
*/ --Finding results in the new that are not in the original
/* Select x, y, z FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
EXCEPT Select x, y, z FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
*/
set @counter -= 1;
end
October 9, 2017 at 10:42 am
TheCTEGuy - Friday, October 6, 2017 4:53 AMHey Luis - Please review the code below - will this perform better from your code -
declare @st datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);;with cte as (
Select @st as dte
union all
Select dte+1 as dte2 from cte where dte<getdate()
)select *,min(dte)over (partition by year(dte),month(dte)) as MinDate ,max(dte)over (partition by year(dte),month(dte)) as MAXDate
from
cte
order by dte
option(maxrecursion 0)
Hi CTEGuy,
Could you please post a complete solution for me based on the task I outlined in my description from above so I can compare the result set and also look into if your code performs better than the code provided by Luis.
October 9, 2017 at 10:46 am
ffarouqi - Monday, October 9, 2017 10:42 AMTheCTEGuy - Friday, October 6, 2017 4:53 AMHey Luis - Please review the code below - will this perform better from your code -
declare @st datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);;with cte as (
Select @st as dte
union all
Select dte+1 as dte2 from cte where dte<getdate()
)select *,min(dte)over (partition by year(dte),month(dte)) as MinDate ,max(dte)over (partition by year(dte),month(dte)) as MAXDate
from
cte
order by dte
option(maxrecursion 0)Hi CTEGuy,
Could you please post a complete solution for me based on the task I outlined in my description from above so I can compare the result set and also look into if your code performs better than the code provided by Luis.
Neither a WHILE Loop or a recursive CTE will perform well or adequately over time and that has been proven over and over. Take the time to Test Luis' code because it contains the code for the fastest generation of dates there is for T-SQL.
--Jeff Moden
October 9, 2017 at 10:51 am
p.s. I also asked if you would post both functions that you're testing so that we might be able to 1) understand more of your testing requirements so that we can provide you with the best solution and 2) possibly suggest improvements in your functions... even the one you optimized. Be sure to let us know which one you optimized.
--Jeff Moden
October 9, 2017 at 11:33 am
Jeff Moden - Monday, October 9, 2017 10:51 AMp.s. I also asked if you would post both functions that you're testing so that we might be able to 1) understand more of your testing requirements so that we can provide you with the best solution and 2) possibly suggest improvements in your functions... even the one you optimized. Be sure to let us know which one you optimized.
PFA. I hope you can help me out find a solution to the task.
October 9, 2017 at 12:38 pm
Luis Cazares - Thursday, October 5, 2017 10:39 AMThis migh also work.
DECLARE @Start datetime = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 20, 0);WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteCalendar(calDate) AS(
SELECT TOP (DATEDIFF( DD, @Start, GETDATE()) + 1)
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @Start) calDate
FROM E4
)
SELECT CAST( CAST( YEAR(calDate) AS char(4)) AS datetime),
calDate
FROM cteCalendar
WHERE EXISTS((SELECT * FROM report.fnReportDealCore_original( CAST( YEAR(calDate) AS char(4)), calDate, -1)
EXCEPT
SELECT * FROM report.fnReportDealCore( CAST( YEAR(calDate) AS char(4)), calDate, -1))
UNION ALL
(SELECT * FROM report.fnReportDealCore( CAST( YEAR(calDate) AS char(4)), calDate, -1)
EXCEPT
SELECT * FROM report.fnReportDealCore_original( CAST( YEAR(calDate) AS char(4)), calDate, -1))) ;
Luis - Can you provide me the same for a month to month date range instead.
October 10, 2017 at 6:46 am
ffarouqi - Monday, October 9, 2017 12:38 PMLuis - Can you provide me the same for a month to month date range instead.
I already posted a code to get monthly ranges.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply