using results of cte (common table expression) in a query

Question

question

JTrafton asked Jul 24, '18

using results of cte (common table expression) in a query

DECLARE @year date = dateadd(year, datediff(year, 0, '2017-01-01'), 1);
 WITH weeks(WeekNumber) AS
 (
 SELECT 0
 UNION ALL
 SELECT WeekNumber + 1 
 FROM weeks
 WHERE WeekNumber < 51
 )
SELECT  dateadd(week, WeekNumber, @year) FROM weeks AS WorkWeek

The payday is each monday of the week, the results of this cte is returning those dates for the year I have attemptd to write the results of this cte to a temp table:

declare @tempTable table(WeekNumber date)
DECLARE @year date = dateadd(year, datediff(year, 0, '2017-01-01'), 1);
 WITH weeks(WeekNumber) AS
 (
 SELECT 0
 UNION ALL
 SELECT WeekNumber + 1 
 FROM weeks
 WHERE WeekNumber < 51
 )
 insert into @tempTable
SELECT  dateadd(week, WeekNumber, @year) FROM weeks AS WorkWeek

I have the following query, where I want to user the values from @tempTable

select hrlyrate, reghrs, othrs from tableb
 where loginId in (select distinct empid from tableb where workday between date and '2017-12-31'
 and payday > '2017-01-01'

note: where date is an iteration of the dates returned from the query select * from @tempTable
I have tried using a while loop to no avail. Is there a better way to get this information and to perform the query?

cte temporary-table

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Jul 25, 2018 at 03:08 PM

@JTrafton The CTE is completely wrong, it just accidentally happens to work for 2017 because in 2017 January 2nd happened to be Monday. If you use any other year then it might not return the set of all Mondays. The variable named year (the way it is declared) will always return January 2nd of whatever date is passed as the third parameter to the datediff. For example, if you pass 2018-01-01 then your CTE will return 52 records: Jan 2, Jan 9, Jan 16 of 2018, but these days are NOT Mondays, they are Tuesdays.

Additionally, you are using recursive CTE to get the set of dates. Why do you do that? It does not make any sense whatsoever to use the recursive CTE for something this simple. Please clarify whether you need the list of Mondays for any given year or not. By the way, some years will have 53 Mondays, not 52.

Thank you.

1 ·

ThomasRushton ♦♦ commented · Jul 25, 2018 at 09:02 AM

CROSS APPLY? JOIN?

0 ·

JTrafton commented · Jul 25, 2018 at 07:11 PM

Thanks, Oleg for the response, I have replaced the CTE with a query to get the Mondays in any given year

0 ·

Oleg JTrafton commented · Jul 25, 2018 at 07:56 PM

@JTrafton Yes, but your query to get all Mondays in any given year is incorrect (as written in your question). It only happens to work by accident for 2017. In some years, like 2018, there are 53, not 52 Mondays. This is because every non-leap year always ends on the same week day it started, and since Jan 1 2018 was Monday, so is Dec 31 2018. There are 51 Mondays sandwiched between these 2 so the total number of Mondays in 2018 is 53. Please let me know if you would like me to post the query which correctly returns all Mondays for any given year. Thank you.

0 ·

question