CODEWITHSUNDEEP
sqlsql-servert-sql
greg121
greg121

Reputation: 964

How to loop SQL statement multiple times

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

Upvotes: 0

Views: 1169

Answers (2)

Tomalak
Tomalak

Reputation: 338208

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

Upvotes: 5

codingbadger
codingbadger

Reputation: 43984

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)

Upvotes: 2

Related Questions