I have a TSQL query that needs to run a query multiple times based on a date parameter. The date needs to be incremented to get all days in one month. The query needs to pull all orders that were purchased and in shipping during a certain time frame. As well as orders that are currently in the shipping process.
This is the current query I have. All I would need to do is run it multiple times and increment @DAY.
DECLARE @MONTH VARCHAR(3)
DECLARE @YEAR VARCHAR(5)
DECLARE @DAY VARCHAR(5)
DECLARE @DATE VARCHAR(25)
SET @MONTH = 1
SET @YEAR = 2024
SET @DAY = 1
--need to loop through and incriment @DAY by 1 every time and print the resualts. This should give us for 1 month.
SET @DATE = @MONTH +'/' + @DAY +'/'+@YEAR
SELECT
o.[ORDER TYPE],
COUNT(*) AS ' Order Count',
ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER(), 4) AS 'Percent' ,
@DATE AS [DATE]
FROM orders AS o
WHERE
--if the order was before this date
[Date Purchased] <= CAST(@DATE AS DATE)
--if the order was shipped after this date or not shipped yet
AND (o.[Date Shipped] >= CAST(@DATE AS DATE) OR o.[Date Shipped] IS NULL)
5
This smells like an XY Problem. There’s no need to run the same query many times here; instead run it once for all the dates you need. YOu can easily get all the dates you need using your calendar table (You do have one, right? If not, why not? Go create one!). Then you can just JOIN to your table, Orders and return the data. SSRS doesn’t support multiple result sets either, so you actually need to go down the single result set method.
I note that the query you provide would actually error, due to the fact that there’s no GROUP BY. I’ve therefore added both the calendar date and order type to it. This solution assumes that you want no rows if there are no application orders for that date of that type.
DECLARE @Year int = 2024,
@Month int = 1
SELECT O.[ORDER TYPE], --don't use names that require delimit identifing
COUNT(*) AS OrderCount, --Don't use single quotes for aliases, and don't use names that require delimit identifing
ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER(PARTITION BY C.CalendarDate), 4) AS [Percent],--Don't use single quotes for aliases, and don't use names that require delimit identifing (PERCENT is a reserved keyword)
C.CalendarDate AS Date
FROM tbl.Calendar C
JOIN dbo.orders O ON C.CalendarDate >= O.[Date Purchased] --don't use names that require delimit identifing
AND (C.CalendarDate <= O.[Date Shipped] OR O.[Date Shipped] IS NULL) --don't use names that require delimit identifing
WHERE C.CalendarYear = @Year
AND C.CalendarMonth = @Month
GROUP BY C.CalendarDate,
O.[ORDER TYPE]
ORDER BY C.CalendarDate,
O.[ORDER TYPE]; --don't use names that require delimit identifing
1