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
Count(Qty) /30 --want to calculate the average last 30 days
Where delivery_dt >=start_dt
Group by product_name
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.