Dynamic SQL Query With Dates

Question

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 !

Use something along the lines of DATEADD(MONTH,-2,GETDATE()) — waka
– waka, Commented Dec 28, 2017 at 14:25
What about to use 'relative' month instead to absolute one. Then, you can take all times the last 12 months, for example: group by Name, datediff(month,Date,getdate() as r_month ) ... FOR r_month IN ([-1], [-2], ... ) Remember to limit the number of months on where clause. — dani herrera
– dani herrera, Commented Dec 28, 2017 at 14:29

dani herrera · Accepted Answer · 2017-12-28 14:47:29Z

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 |

Collectives™ on Stack Overflow

Dynamic SQL Query With Dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related