joe joe - 1 month ago 11
SQL Question

Daily Avg in SQL

I have a simple sql below. I want to calculate the daily average. But I will have to change the parameters and the number of days every day when I run my sql. What is a better way to eliminate these repeated steps?

Declare start_dt as datetime
Declare end-dt as datetime

Set start_dt ='10/01/16'
Set end_dt = '10/31/16

Select
Product_Name,
Count(Qty) /30 --want to calculate the average last 30 days
From temp
Where delivery_dt >=start_dt
And delivery_dt<end_dt
Group by product_name


As noticed above, I have to manually change the parameters for the date range and the numbers of days to get my average. Is there a way in sql server that I don't have to change the parameters and numbers of my average? And I would like my average results shown as
float
.

Answer

Maybe this can help you.

DECLARE @Start_dt DATETIME
DECLARE @End_dt   DATETIME

SET @Start_dt = GETDATE()
SET @End_dt = DATEADD(DAY, 30, @Start_dt);


SELECT  Product_Name,
        Count(Qty)/DATEDIFF(DAY,@Start_dt,@End_dt) AS [Daily Average] --want to calculate the average last 30 days
FROM temp 
WHERE (delivery_dt >= @Start_dt) AND (delivery_dt < @End_dt)
GROUP BY Product_Name 

However, you've mentioned that you often use this kind of sql query so I would recommend that you create stored procedure for this one and just call it a day. For creating procedures you can take a look at microsoft documentation.