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

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'