CODEWITHSUNDEEP
sqlsql-server
Infin8Loop
Infin8Loop

Reputation: 301

How do I perform this kind of month-over-month comparison in SQL Server?

Say I have data in a table like this:

| DateTimePurchased    | Amount |
|----------------------|--------|
| 1/1/2017  3:23:15 PM | 657    |
| 1/1/2017  3:38:29 PM | 730    |

And I want to run a query that outputs like this:

| DayOfMonth | Feb 2017 | Mar 2017 |
|------------|----------|----------|
| 1          | 2344     | 4342     |
| 2          | 3435     | 4564     |
| 3          | 5675     | 6787     |

etc...

How would I write the query for SQL Server?

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Oh, that is what you want. You just do:

select day(DateTimePurchased) as dy,
       sum(case when DateTimePurchased >= '2017-02-01' and  DateTimePurchased < '2017-03-01'
                then amount
           end) as amount_201702,
       sum(case when DateTimePurchased >= '2017-03-01' and  DateTimePurchased < '2017-04-01'
                then amount
           end) as amount_201703
from t
group by day(DateTimePurchased)
order by dy;

Upvotes: 1

Related Questions