Anyone know a prettier way of doing this ?

Question

1.00/5 (1 vote)

See more:

SQL

select owner_id,
sum(CASE WHEN MONTH(close_date) = 1 THEN 1 ELSE 0 END) AS JanLeads,
sum(CASE WHEN MONTH(close_date) = 1 THEN amtfinanced ELSE 0 END) AS JanAmt,
sum(CASE WHEN MONTH(close_date) = 2 THEN 1 ELSE 0 END) AS FebLeads,
sum(CASE WHEN MONTH(close_date) = 2 THEN amtfinanced ELSE 0 END) AS FebAmt,
sum(CASE WHEN MONTH(close_date) = 3 THEN 1 ELSE 0 END) AS MarLeads,
sum(CASE WHEN MONTH(close_date) = 3 THEN amtfinanced ELSE 0 END) AS MarAmt,
sum(CASE WHEN MONTH(close_date) = 4 THEN 1 ELSE 0 END) AS AprLeads,
sum(CASE WHEN MONTH(close_date) = 4 THEN amtfinanced ELSE 0 END) AS AprAmt,
sum(CASE WHEN MONTH(close_date) = 5 THEN 1 ELSE 0 END) AS MayLeads,
sum(CASE WHEN MONTH(close_date) = 5 THEN amtfinanced ELSE 0 END) AS MayAmt,
sum(CASE WHEN MONTH(close_date) = 6 THEN 1 ELSE 0 END) AS JunLeads,
sum(CASE WHEN MONTH(close_date) = 6 THEN amtfinanced ELSE 0 END) AS JunAmt,
sum(CASE WHEN MONTH(close_date) = 7 THEN 1 ELSE 0 END) AS JulLeads,
sum(CASE WHEN MONTH(close_date) = 7 THEN amtfinanced ELSE 0 END) AS JulAmt,
sum(CASE WHEN MONTH(close_date) = 8 THEN 1 ELSE 0 END) AS AugLeads,
sum(CASE WHEN MONTH(close_date) = 8 THEN amtfinanced ELSE 0 END) AS AugAmt,
sum(CASE WHEN MONTH(close_date) = 9 THEN 1 ELSE 0 END) AS SepLeads,
sum(CASE WHEN MONTH(close_date) = 9 THEN amtfinanced ELSE 0 END) AS SepAmt,
sum(CASE WHEN MONTH(close_date) = 10 THEN 1 ELSE 0 END) AS OctLeads,
sum(CASE WHEN MONTH(close_date) = 10 THEN amtfinanced ELSE 0 END) AS OctAmt,
sum(CASE WHEN MONTH(close_date) = 11 THEN 1 ELSE 0 END) AS NovLeads,
sum(CASE WHEN MONTH(close_date) = 11 THEN amtfinanced ELSE 0 END) AS NovAmt,
sum(CASE WHEN MONTH(close_date) = 12 THEN 1 ELSE 0 END) AS DecLeads,
sum(CASE WHEN MONTH(close_date) = 12 THEN amtfinanced ELSE 0 END) AS DecAmt,
sum(amtfinanced) as yearlyamt,
count(owner_id) as yearlyleads
from opportunity o
inner join sumfinanced sf
on o.opp_id = sf.opp_id
group by owner_id
order by owner_id

Posted 1-Jan-15 3:16am

pkfox

Add a Solution

2 solutions

Solution 2

If SQL Server (you didn't specify)...

Consider using a Common Table Expression:

SQL

WITH cte AS
(
  SELECT OwnerId
  , MONTH(close_date) close_month
  , COUNT(*) leads
  , SUM(amtfinanced) amtfinanced
  FROM ...
  GROUP BY OwnerId
  , MONTH(close_date)
)
SELECT ...
FROM cte 
...

Posted 1-Jan-15 5:11am

PIEBALDconsult

Comments

Peter Leow 1-Jan-15 23:05pm

+5, and Happy New Year!

pkfox 2-Jan-15 2:15am

Hi and thanks, what do I put in the second select ?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-01-01T03:28:00

Solution 1

At the first look, below query should give you result in rows:

SQL

SELECT OwnerId, MONTH(close_date) AS MonthId, COUNT(*) AS Amt
FROM ...
GROUP BY OwnerId, MONTH(close_date)

To be able to convert it to columns view, use pivot[^].
Simple Way To Use Pivot In SQL Query[^]

Posted 1-Jan-15 3:28am

Maciej Los

Updated 1-Jan-15 3:31am

v2

Comments

Peter Leow 1-Jan-15 23:04pm

+5, and Happy New Year!

Maciej Los 2-Jan-15 1:52am

Thank you, Peter!
Happy New Year for you too!

pkfox 2-Jan-15 2:05am

Very useful link thank you

Maciej Los 2-Jan-15 2:16am

You're very welcome ;)