Post reply

Sum case question

  • December 13, 2017 at 2:33 pm

    #409859

    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

    #1972038

    Maybe this article by Jeff Moden will help...

  • December 13, 2017 at 3:13 pm

    #1972047

    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

    #1972174


    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • December 19, 2017 at 1:32 pm

    #1972790

    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