Extract 7 days of the week as a date from a combination of the year and week number?

Question

I have a table (SQL Server 2017) where the data is stored at the level of the year number and week number.

    +---------+-------------+---------+----------+-----+
    | year_id | week_number | good_id | store_id | qty |
    +---------+-------------+---------+----------+-----+
    | 2019    | 42          | 113466  | 41       | 7   |
    +---------+-------------+---------+----------+-----+

I need to get a similar table, but at the day level, where the quantity (qty) will be divided into 7 parts evenly for each day.

+---------+-------------+---------+----------+-----+------------+
| year_id | week_number | good_id | store_id | qty | date_id    |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-14 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-15 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-16 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-17 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-18 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-19 |
+---------+-------------+---------+----------+-----+------------+
| 2019    | 42          | 113466  | 41       | 1   | 2019-10-20 |
+---------+-------------+---------+----------+-----+------------+

I found a way to get a date from the year and week number, but how do I get 7 rows from one at once?

Does this answer your question? [Generating Rows Based on Column Value](https://stackoverflow.com/questions/52744203/generating-rows-based-on-column-value) — Digvijay S, Apr 07 '20 at 07:12
Date-related problems become a *lot* easier if you create a [Calendar table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/). With a Calendar table, all you'd have to do would be join with it on the Year, WeekNumber columns to get one row per day — Panagiotis Kanavos, Apr 07 '20 at 07:17

score 2 · Accepted Answer · answered Apr 07 '20 at 07:26

2

You can create a calendar table as shown here and join the original table as shown below to get the desired output.

Select 
      CalendarDate
      , year_id
      , week_number
      , good_id
      , store_id
      , 1 as qty
from dbo.RunningNumbers
inner join ToBeGenerated on CalendarYear = year_id and week_number = CalendarWeek

Here is the live db<>fiddle demo.

answered Apr 07 '20 at 07:26

Suraj Kumar

5,547
8
20
42

1

Downvoter, why did you downvote the **correct** answer? This is used in **all** reporting databases and star schemas for a reason – Panagiotis Kanavos Apr 07 '20 at 07:32

score 1 · Answer 2 · answered Apr 07 '20 at 07:17

1

There you go. You need to join a table with all the dates and then divide the qty by the value you want:


use tempdb

CREATE TABLE tbl1
(
   year_id INT, week_number INT, good_id INT, store_id INT, qty INT
)

INSERT INTO tbl1
VALUES (2019, 42, 113466, 41, 7)
GO
WITH sample AS(
   SELECT CAST('2019-10-01' AS DATE) as DT
   UNION ALL
   SELECT DATEADD(dd,1,dt)
    FROM sample
   WHERE DATEADD(dd,1,dt) < CAST('2019-12-31' AS DATE))

SELECT dt, YEAR(dt) AS [Year], DATEPART(WEEK,dt) AS Week
INTO wks
FROM sample

SELECT t.year_id, t.week_number, t.good_id, t.store_id, qty/7 AS [Qty], dt AS [Date]
  FROM tbl1 t
 INNER JOIN wks S on s.Week = t.week_number

answered Apr 07 '20 at 07:17

Even better - don't generate the dates, use a Calendar table instead. The CTE generates such a table at runtime without taking advantage of any indexes, which makes this very slow – Panagiotis Kanavos Apr 07 '20 at 07:18
We are talking max 100 rows with the recursion limit. So performance will not be an issue. – Apr 07 '20 at 07:26
It is, if you use that for concurrent calls or event modest number of target rows. Without the indexes, you have to scan the entire target table. That's why Calendar tables are used everywhere, with CTEs only used to generate the date data. Why not create a properly indexed table and reuse it? – Panagiotis Kanavos Apr 07 '20 at 07:33
Why overanalyze this? Question is Answered. – Apr 07 '20 at 07:34
No it's not. There's a good, performant way to do something and a bad one. In databases, performance matters so this answer is *not* a good one. In fact, the question has been asked 100s of times already with multiple good answers that all boil down to "Use a Calendar Table" – Panagiotis Kanavos Apr 07 '20 at 07:36
Not sure if you checked my code that actually uses the CTE to create a calendar table. – Apr 07 '20 at 07:37
I did, and I was going to comment "why not create a proper permanent Calendar table?" – Panagiotis Kanavos Apr 07 '20 at 07:38
1

@Rabbit . . . I don't think this calculates the dates correctly -- or at least, it hardcodes them in a non-generalizable way. – Gordon Linoff Apr 07 '20 at 11:48

Extract 7 days of the week as a date from a combination of the year and week number?

2 Answers2