CODEWITHSUNDEEP
sqlsql-server
Tina
Tina

Reputation: 21

select query multiple times, changing dates

I need to run a simple select query in SQL.

Problem is I need 12 outputs basis different months. Is there a way to execute something like a macro where I write the query once and it runs 12 times on its own by just changing the monthend dates?

Thanks

Upvotes: 2

Views: 506

Answers (2)

Unreason
Unreason

Reputation: 12704

Try to get around the concept of relational algebra.

Translating algorithmic approaches, such as a loop, will only work so far. Relational algebra traditionally had its limits (so you will find procedural support in all RDBMSes), but together with CTE and recursive queries there's little that you can not do.

Your problem can be solved by

  • procedural SQL (which has loops, see example)
  • joining with another table which would have entries for these 12 instances (such table can be created for temporary use or can be made part of the system)
  • recursive SQL (as in Lieven's answer)

I would say that conceptually recursive query is hardest to understand and maintain, so read plenty of examples.

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

You could use following construct to get 12 records for each month and cross apply the results with your original query.

;WITH q AS (
  SELECT  Column1 = GetDate()
  UNION ALL
  SELECT  DATEADD(mm, 1, q.Column1)
  FROM    q
  WHERE   q.Column1 < DATEADD(mm, 11, GetDate())
)
SELECT  *
FROM    q

Upvotes: 3

Related Questions