January 28, 2016 at 12:32 pm
I'm using a query (from the forum) that works for getting all the mondays between two dates.
Here it is:
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET@start_date = '2005-01-01'
SET@end_date = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)
WITH CTE_Dates(Monday) AS
(
SELECT
CASE WHEN DATEPART(dw, @start_date) <= 2 THEN DATEADD(dd, 2 - DATEPART(dw, @start_date), @start_date)
ELSE DATEADD(dd, 9 - DATEPART(dw, @start_date), @start_date)
END
AS Monday
UNION ALL
SELECT
DATEADD(dd, 7, Monday)
FROM CTE_Dates
WHERE
DATEADD(dd, 7, Monday) <= @end_date
)
SELECT
Monday
--,YEAR(Monday) AS tty
--,ROW_NUMBER() OVER(PARTITION BY tty ORDER BY (SELECT YEAR(Monday) FROM CTE_Dates) AS rnum
FROM CTE_Dates
OPTION (maxrecursion 0)
Two commented lines are my atempt to print the Year that each date belongs to and also a row number for that specific record, so it would be between 1 and 52 or 53 (depending of the weeks in that year), but I cant manage to get the count, I figured I could sub-query the CTE and do it from the outside but its not working...
Any help is appretiated
January 28, 2016 at 12:40 pm
January 28, 2016 at 2:09 pm
Two things to note:
1. You're not using the correct value to order and your weeks might come in a wrong order.
2. You shouldn't use recursive CTEs to generate rows. An alternative is to use cross joins to build a tally table. Read more about it in here: http://qa.sqlservercentral.com/articles/T-SQL/74118/
Here's an example with a different formula that profits the fact that date 0 (1900-01-01) is a Monday.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteDates(Monday) AS(
SELECT TOP(DATEDIFF(dd, @start_date, @end_date)/7)
DATEADD(ww, (DATEDIFF(dd, 0, @start_date)/7) + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 0) Monday
FROM E a, E b, E c, E d
)
SELECT Monday
,YEAR(Monday) AS tty
,ROW_NUMBER() OVER(PARTITION BY YEAR(Monday) ORDER BY Monday)
FROM cteDates;
January 28, 2016 at 5:06 pm
Im trying to sort out the SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n) could you explain the nature of this method, please.
Regards.
January 28, 2016 at 6:21 pm
nhernandez 63958 (1/28/2016)
Im trying to sort out the SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n) could you explain the nature of this method, please.Regards.
Sure. This is called a Table Value Constructor. It's a feature introduced in 2008 to generate sets of values. In this case, the set is one column with 10 rows and the value is always zero. The value has no relevance, as I only need the rows to generate proper values later.
Here's an article which gives more detail and different examples: https://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/
You can also find information in Books On Line: https://msdn.microsoft.com/en-us/library/dd776382.aspx
I hope that's clear enough, but if it's not, feel free to continue to ask questions.
January 28, 2016 at 7:33 pm
Luis Cazares (1/28/2016)
nhernandez 63958 (1/28/2016)
Im trying to sort out the SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n) could you explain the nature of this method, please.Regards.
Sure. This is called a Table Value Constructor. It's a feature introduced in 2008 to generate sets of values. In this case, the set is one column with 10 rows and the value is always zero. The value has no relevance, as I only need the rows to generate proper values later.
Here's an article which gives more detail and different examples: https://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/
You can also find information in Books On Line: https://msdn.microsoft.com/en-us/library/dd776382.aspx
I hope that's clear enough, but if it's not, feel free to continue to ask questions.
nhernandez, if you haven't seen this technique before, this might help. Luis isn't using the values of the rows, but he's using the presence of rows. This is a very key point.
In the FROM clause, he's got a CROSS JOIN, so he's generating 10 * 10 * 10 * 10 (or 1000 rows) in the set.
He's then using the ROW_NUMBER() function on the set to generate a table of numbers from 1 to 1000 and does date math with it.
The whole approach is a very efficient way of generating a set of data to deal with instead of doing things row by row.
BTW, nice job, Luis.
January 29, 2016 at 4:30 am
January 29, 2016 at 4:53 am
Of course u can like this.
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET@start_date = '2005-01-01'
SET@end_date = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)
;
WITH
E(n) AS(
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n unionall
Select 0 AS n
),
cteDates(Monday) AS(
SELECT TOP(DATEDIFF(dd, @start_date, @end_date)/7)
DATEADD(ww, (DATEDIFF(dd, 0, @start_date)/7) + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 0) Monday
FROM E a, E b, E c, E d
)
SELECT Monday
,YEAR(Monday) AS tty
,ROW_NUMBER() OVER(PARTITION BY YEAR(Monday) ORDER BY Monday)
FROM cteDates;
January 29, 2016 at 9:11 am
Exactly as twin.devil posted, maybe with less characters.
DECLARE @start_date DATETIME;
DECLARE @end_date DATETIME;
SET@start_date = '2005-01-01';
SET@end_date = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1);
WITH
E(n) AS(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteDates(Monday) AS(
SELECT TOP(DATEDIFF(dd, @start_date, @end_date)/7)
DATEADD(ww, (DATEDIFF(dd, 0, @start_date)/7) + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 0) Monday
FROM E a, E b, E c, E d
)
SELECT Monday
,YEAR(Monday) AS tty
,ROW_NUMBER() OVER(PARTITION BY YEAR(Monday) ORDER BY Monday)
FROM cteDates;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply