CODEWITHSUNDEEP
sqlsql-server
myke
myke

Reputation: 15

Add names to a list of weeks

How to add to the query a list of names for each week from another table with names (repeat in order by id)

Weeknumb is different from id 

SELECT DATEPART(wk,DATEADD(wk,t2.number,'2019')) as Weeknumb     
    , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019')
        + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 4) AS StartOfWeek
    , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019')
        + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 11) AS EndOfWeek
FROM master..spt_values t2
WHERE t2.type = 'P'
AND t2.number <= 255
AND YEAR(DATEADD(wk,t2.number,'2019'))=2019

Result:

| Weeknumb |          StartOfWeek |            EndOfWeek |
|----------|----------------------|----------------------|
|        1 | 2019-01-04T00:00:00Z | 2019-01-11T00:00:00Z |
|        2 | 2019-01-11T00:00:00Z | 2019-01-18T00:00:00Z |
|        3 | 2019-01-18T00:00:00Z | 2019-01-25T00:00:00Z |
|        4 | 2019-01-25T00:00:00Z | 2019-02-01T00:00:00Z |
|        5 | 2019-02-01T00:00:00Z | 2019-02-08T00:00:00Z |
|        6 | 2019-02-08T00:00:00Z | 2019-02-15T00:00:00Z |
|        7 | 2019-02-15T00:00:00Z | 2019-02-22T00:00:00Z |
|        8 | 2019-02-22T00:00:00Z | 2019-03-01T00:00:00Z |
|        9 | 2019-03-01T00:00:00Z | 2019-03-08T00:00:00Z |
|       10 | 2019-03-08T00:00:00Z | 2019-03-15T00:00:00Z |
|       11 | 2019-03-15T00:00:00Z | 2019-03-22T00:00:00Z |

I have another table with names

| id | name |
|----|------|
|  1 |    a |
|  2 |    b |
|  3 |    c |

Need to be:

| Weeknumb |          StartOfWeek |            EndOfWeek | Name
|----------|----------------------|----------------------|-------
|        1 | 2019-01-04T00:00:00Z | 2019-01-11T00:00:00Z | a
|        2 | 2019-01-11T00:00:00Z | 2019-01-18T00:00:00Z | b
|        3 | 2019-01-18T00:00:00Z | 2019-01-25T00:00:00Z | c
|        4 | 2019-01-25T00:00:00Z | 2019-02-01T00:00:00Z | a
|        5 | 2019-02-01T00:00:00Z | 2019-02-08T00:00:00Z | b
|        6 | 2019-02-08T00:00:00Z | 2019-02-15T00:00:00Z | c
|        7 | 2019-02-15T00:00:00Z | 2019-02-22T00:00:00Z | a
|        8 | 2019-02-22T00:00:00Z | 2019-03-01T00:00:00Z | b
|        9 | 2019-03-01T00:00:00Z | 2019-03-08T00:00:00Z | c
|       10 | 2019-03-08T00:00:00Z | 2019-03-15T00:00:00Z | a
|       11 | 2019-03-15T00:00:00Z | 2019-03-22T00:00:00Z | b

Upvotes: 1

Views: 78

Answers (2)

VBoka
VBoka

Reputation: 9083

Is this what you had in mind : 

select a.Weeknumb
       , a.StartOfWeek
       , a.EndOfWeek
       , b.name from (
       SELECT case when ((ROW_NUMBER() OVER (ORDER BY DATEPART(wk,DATEADD(wk,t2.number,'2019'))) % (select count(1) from testTable2))) = 0 then 3
                   else ((ROW_NUMBER() OVER (ORDER BY DATEPART(wk,DATEADD(wk,t2.number,'2019'))) % (select count(1) from testTable2)))
                   end br
              , DATEPART(wk,DATEADD(wk,t2.number,'2019')) as Weeknumb     
              , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019') + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 4) AS StartOfWeek
              , DATEADD(wk, DATEDIFF(wk, 4, '01/01/' + '2019') + (DATEPART(wk,DATEADD(wk,t2.number,'2019'))-1), 11) AS EndOfWeek
       FROM master..spt_values t2
       WHERE t2.type = 'P'
       AND t2.number <= 255
       AND YEAR(DATEADD(wk,t2.number,'2019'))=2019) a
left join ( select ROW_NUMBER() OVER (ORDER BY id ) rn, name from  testTable2)b 
on (a.br = b.rn)

Here is the DEMO

Upvotes: 1

RMorrisey
RMorrisey

Reputation: 7739

You can accomplish this using a database join.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

You can join on the results of a SQL query as well as on a table:

SELECT t1.a, t2.b
FROM (
  SELECT id, a
  FROM table1
) t1
LEFT JOIN table2 t2 ON t2.id = t1.id

Upvotes: 0

Related Questions