CODEWITHSUNDEEP
sqlsql-server
Ömür Can Yurdugül
Ömür Can Yurdugül

Reputation: 107

Dynamic SQL Query With Dates

I have a SQL query and it's creating a PIVOT table.

Here is it. 

SELECT *
FROM (
  SELECT
   Name
  ,Year
  ,sum(Payment) as TotalPayment  
  FROM Orders
  group by Name,Date
 ) as Tablo
 PIVOT
 (
    SUM(TotalPayment)
    FOR Date IN ([2009-11],[2009-12],[2010-01])
 )
 AS p

It's getting monthly total payment and the guy who wrote this query adding year-month manually every month. I need to do it dynamically.

How can I get month-year dynamically from table using SQL query with loop or something like that ?

Thanks for your helps !

Upvotes: 1

Views: 801

Answers (1)

dani herrera
dani herrera

Reputation: 51655

I sugges to you about to use relative time and not absolute one. In this way, your query is all time up to date. Here I post a sample on SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table t ( d date, i int );
insert into t values
( '2016-01-01', 100 ),
-- ... some other data
( '2017-12-13', 100 );

I write the query step by step using CTE:

with 
cooked as          --change to relative months (from today)
( select datediff(MONTH,getdate(), d ) as dif, i
  from t
),
filtered as        --just take the last 12 months
( select dif as Date, i as TotalPayment
  from cooked
  where dif > -12
)
select *           --pivoting
from filtered
PIVOT
 (
    SUM(TotalPayment)
    FOR Date IN ([-11],[-10],[-9],[-8],
                 [-7],[-6],[-5],[-4],
                 [-3],[-2],[-1],[0])
 )
 AS p

Here the Results, where [0] means this moth and [-11] means 12 moths ago:

| -11 | -10 |  -9 |  -8 |  -7 |  -6 |  -5 |  -4 |  -3 |  -2 |  -1 |   0 |
|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| 500 | 500 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |

Upvotes: 2

Related Questions