December 13, 2017 at 2:33 pm
Hi guys,
How would I write the below as a sum case when? for example, similar to this : Sum(case when t1.Sales date between '2017-01-01 00:00:00.000' and '2017-03-31 00:00:00.000' then 1 else 0 end) Quarter1,
Select
t1.Salesagent,
CASE
WHEN MONTH(t1.DateCompleted) BETWEEN 1 AND 3 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q1'
WHEN MONTH(t1.DateCompleted) BETWEEN 4 AND 6 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q2'
WHEN MONTH(t1.DateCompleted) BETWEEN 7 AND 9 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q3'
WHEN MONTH(t1.DateCompleted) BETWEEN 10 AND 12 THEN CONVERT(CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q4'
END AS Quarter,
COUNT(1.Salesid) AS Countofsales
From source t1
Group by
t1.Salesagent
CASE
WHEN MONTH(t1.DateCompleted) BETWEEN 1 AND 3 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q1'
WHEN MONTH(t1.DateCompleted) BETWEEN 4 AND 6 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q2'
WHEN MONTH(t1.DateCompleted) BETWEEN 7 AND 9 THEN CONVERT (CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q3'
WHEN MONTH(t1.DateCompleted) BETWEEN 10 AND 12 THEN CONVERT(CHAR (4), YEAR(t1.DateCompleted) - 0) + 'Q4'
END
December 13, 2017 at 2:43 pm
Maybe this article by Jeff Moden will help...
December 13, 2017 at 3:13 pm
And instead of using MONTH() try using DATEPART(QUARTER, <yourdatefield>)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 14, 2017 at 10:24 am
Select
t1.Salesagent,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS Quarter1_Sales_Count,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 4 AND 6 THEN 1 ELSE 0 END) AS Quarter2_Sales_Count,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 7 AND 9 THEN 1 ELSE 0 END) AS Quarter3_Sales_Count,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 10 AND 12 THEN 1 ELSE 0 END) AS Quarter4_Sales_Count,
COUNT(t1.Salesid) AS Year_Sales_Count
From source t1
Group by
t1.Salesagent
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 19, 2017 at 1:32 pm
Sorry for the late reply. Using the link supplied by pietlinden and your helpful examples, I was able to edit my code to get what I need. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply