SQL CASE statement WHEN quarter = x then show results between 2 quarters-CodePudding

So I think a case statement is the right way to do this but I am open to other solutions to get the same result.

Problem: We have a customer that wants to know all new client for their Fiscal year depending on the quarter. Their FY starts at quarter 4.

So if it is quarter 4 then everyone is a new client, quarter 1 everyone between quarter 1 and 3 is a new client (if they contact in quarter 4 they are not to be counted), quarter 2 everyone between quarter 2 and 3 is a new client (if they contact in quarter 4 or 1 they are not to be counted), and quarter 3 only people that contacted us in quarter 3 are new clients (if they contact in quarter 4, 1, or 2 they are not to be counted).

We only need the counts of the clients.

FYI: I'm using SQL Server.

Here is the relevant code below. I'm aware the case statement isn't correct syntax (error message Msg 156, Level 15, State 1, Line 66 Incorrect syntax near the keyword 'BETWEEN'.) but this is the basic logic that I need just need help making it work (the case statement is really what has me stumped):

/* Join Unencrypted new calls(still have to filter by date, Quarter starting in October all clients will be considered new) */
    LEFT JOIN
        (SELECT CallReportNum
        FROM [iCarolData].[dbo].[cUnencryted]
        WHERE PhoneNumberFull != '' AND PhoneNumberFull NOT LIKE '0%' AND PhoneNumberFull NOT LIKE '1%' AND PhoneNumberFull NOT LIKE '999%'
        HAVING COUNT(PhoneNumberFull) = 1) unencryptNew
ON tRep.CallReportNum = unencryptNew.CallReportNum
/* Join ClientRecordNonText New Clients */
    INNER JOIN
    SELECT
                YEAR(DateOfCall), DATEPART(q, dateOfCall),
                /* comparing current quarter to quarter 4 */
                COUNT(CASE WHEN DATEPART(q, DateOfCall) = 4 THEN BETWEEN DATEPART(q,DateOfCall) >= 1 AND DATEPART(q,DATEOFCALL) <= 4
                     WHEN DATEPART(q, DateOfCall) = 1 THEN BETWEEN DATEPART(q,DateOfCall) >= 1 AND DATEPART(q,DATEOFCALL) <= 3
                     WHEN DATEPART(q, DateOfCall) = 2 THEN BETWEEN DATEPART(q,DateOfCall) >= 2 AND DATEPART(q,DATEOFCALL) <= 3
                     WHEN DATEPART(q, DateOfCall) = 3 THEN DATEPART(q, DateOfCall) = 3 
                END) AS 'DateTest'
            FROM cClientRecordNonText
            WHERE [Contact Information - Is this a Test Call?] = 'No'
                
            GROUP BY YEAR(DateOfCall), DATEPART(q, dateOfCall)
ON unencryptNew.CallReportNum = CRecNTNew.CallReportNum

What I've tried so far is different combinations of DATEPART, DATEADD(q,DateOfCall), and DATEDIFF(q,DateOfCall) and I can't figure out the logic to get what I need. Any help or guidance on this problem would be appreciated.

CodePudding user response：

This is just about the syntax of the case statement. The between is part of a logical statement. Even if the syntax was correct, it would be counted if either true or false. A trick is to sum values and return 0 or 1 for those to not count or count. Also, I think you mean to include the current quarter for part of this and then test the quarter of the call. This is a WAG.

SUM(CASE
    WHEN DATEPART(q,GETDATE()) = 4 AND YEAR(DateOfCall) = YEAR(GETDATE()) AND DATEPART(q,DateOfCall) = 4 THEN 1 -- Now Q4 - call in same year Q4
    WHEN DATEPART(q,GETDATE()) = 4 AND YEAR(DateOfCall) = YEAR(GETDATE())   1 AND DATEPART(q,DateOfCall) BETWEEN 1 AND 3 THEN 1 -- Now Q4 - call in next year Q1,Q2,Q3
    WHEN DATEPART(q,GETDATE()) = 1 AND YEAR(DateOfCall) = YEAR(GETDATE()) AND DATEPART(q,DateOfCall) BETWEEN 1 AND 3 THEN 1 -- Now Q1 - call in same year Q1,Q2,Q3
    WHEN DATEPART(q,GETDATE()) = 2 AND YEAR(DateOfCall) = YEAR(GETDATE()) AND DATEPART(q,DateOfCall) BETWEEN 2 AND 3 THEN 1 -- Now Q2 - call in same year Q2,Q3
    WHEN DATEPART(q,GETDATE()) = 3 AND YEAR(DateOfCall) = YEAR(GETDATE()) AND DATEPART(q,DateOfCall) = 3 THEN 1 -- Now Q3 - call in same year Q3
    ELSE 0
    END) AS 'DateTest'