How to loop SQL statement multiple times

Question

I try to loop one sql statement multiple times to retrieve the weekly revenue of on particular store. Here is my best guess which does not work.

SELECT * 
    DECLARE @i int = 0
    WHILE @i < 52 BEGIN
        SET @i = @i + 1
        FROM dbo.revenue
        WHERE DATEPART(WW, date) = @i
        AND storenumber = '005'
    END

This won't work, what is your actual problem? Looks like meta.stackexchange.com/questions/66377/what-is-the-xy-problem — HoneyBadger
– HoneyBadger, Commented Nov 2, 2015 at 10:44
Don't use loops but set based approaches sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 — Tim Schmelter
– Tim Schmelter, Commented Nov 2, 2015 at 10:45
Can't you just group by DATEPART(WW, date), sum and ask for the relevant store — ehh
– ehh, Commented Nov 2, 2015 at 10:46
@HoneyBadger this is not on purpose, just not knowing how to think set-based — greg121
– greg121, Commented Nov 2, 2015 at 14:17

Tomalak · Accepted Answer · 2015-11-02 12:02:13Z

5

You actually never want to write loops in SQL.

SELECT
  SUM(earnings) weekly_earnings
FROM
  dbo.revenue
WHERE
  storenumber = '005'
  AND date >= '2015-01-01'
  AND date < '2016-01-01'
GROUP BY
  DATEPART(WW, date)

Left join that against a helper table that contains 52 rows (1 through 52) representing the weeks to fill in the blanks (weeks with no revenue).

Note that the date >= '2015-01-01' AND date < '2016-01-01' exists because:

you must limit the query to one year, or the week number becomes ambiguous
it is superior to DATEPART(YY, date) = 2015 because, being a calculation, that expression would not be able to use an index on the date column, whereas >= and < can use an index

EDIT: Instead of a temporary table you can use a recursive CTE on SQL Server:

WITH WeekNumbers (WkNum) AS (
  SELECT 1 AS WkNum
  UNION ALL
  SELECT w.WkNum + 1 FROM WeekNumbers w WHERE w.WkNum <= 52
)
SELECT
  w.WkNum,
  SUM(r.earnings) weekly_earnings
FROM
  WeekNumbers w
  LEFT JOIN dbo.revenue r ON w.WkNum = DATEPART(WW, r.date) 
WHERE
  r.storenumber = '005'
  AND r.date >= '2015-01-01'
  AND r.date < '2016-01-01'
GROUP BY
  w.WkNum

edited Nov 2, 2015 at 12:02

answered Nov 2, 2015 at 10:47

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

codingbadger Over a year ago

The calendar table is an excellent point to cater for weeks with no revenue.

greg121 Over a year ago

Thank you. I'm not used to think like this.

Tomalak Over a year ago

@greg121 Yes, the set-based approach takes some getting used to, but learning it is absolutely inevitable.

greg121 Over a year ago

@Tomalak also great you added the recursive CTE version. But when looking at it, I see a CTE with 52 entries which is joined on the actual table. where is the recursion?

Tomalak Over a year ago

The recursion is happening in the CTE, it's self-referential. That's how it generates the 52 rows.

codingbadger · Accepted Answer · 2015-11-02 10:46:49Z

2

You could just group your data and use some aggregation?

Select Sum(Revenue) AS [TotalRevenuePerWeek],
       DatePart(ww, RevenueDate) as [WeekNumber]

From dbo.Revenue
Where StoreNumber = '005'
And DatePart(year, RevenueDate) = 2015
Group By DatePart(ww, RevenueDate)

answered Nov 2, 2015 at 10:46

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Collectives™ on Stack Overflow

How to loop SQL statement multiple times

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related