CODEWITHSUNDEEP
sqlsql-serverloops
Hassaan
Hassaan

Reputation: 41

Automating this SQL query

I have this query that I am running and I need to run this for 60 different dates. It is time consuming to enter a different date every time. Is there any way to make this run for some dates and results recorded against each date?

     DROP TABLE IF EXISTS #1
         declare @d1 datetime = '2020-01-20' 
         declare @d2 datetime = @d1 - 29
     select distinct [user] into #1
      from [Customerbase].[dbo].[Base]
      where [DATE] between @d2 and @d1;

select  sum(cast([bal] as decimal(16,4))) as sum , avg(cast([bal] as decimal(16,4))) as avg
      from #1
      inner join [reg].[dbo].[dailyfile]
      on #1.user= [reg].[dbo].[dailyfile].user
      where   [reg].[dbo].[dailyfile].[date] = @d1

Upvotes: 0

Views: 52

Answers (2)

Sandhya
Sandhya

Reputation: 674

Maybe something like this would work for you.

DECLARE @Dates AS TABLE (QueryDate DATE)
INSERT INTO @Dates VALUES ('2020-01-20')
INSERT INTO @Dates VALUES ('2019-12-15')
INSERT INTO @Dates VALUES ('2019-11-12')
-- insert addnal dates here

;WITH cteUserDates AS (
SELECT [user], d.QueryDate 
FROM [Base] b
INNER JOIN @Dates d ON b.[date] = d.QueryDate
)
select sum(cast([bal] as decimal(16,4))) as sum , avg(cast([bal] as decimal(16,4))) as avg
from cteUserDates d inner join
     [dailyfile] df
     on d.[user] = df.[user] and  d.QueryDate = df.[date]
group by d.[user], d.QueryDate

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would suggest something like this:

with t1 as (
      select distinct b.user, v.dte
      from [Customerbase].[dbo].[Base] b join
           (values (convert(date), date1), . . .   -- list dates here
           ) v.dte
      on b.date between dateadd(day -29, v.date) and v.dte
     )
select t1.dte, sum(cast([bal] as decimal(16, 4))) as sum,
       avg(cast([bal] as decimal(16, 4))) as avg
from t1 join
     [reg].[dbo].[dailyfile] df
     on t1.user = dt.user and
        df.[date] = t1.dte
group by t1.dte;

Upvotes: 1

Related Questions