时间戳

首页 > 解决方案 > MS Sql Server statement inside COUNT

sql - MS Sql Server statement inside COUNT

问题描述

It does work in BigQuery but i do not get what am I doing incorrectly in MS SQL Server;

CASE 
    WHEN COUNT( RSM.RECEIVED_AT BETWEEN DATEADD(day, -84,UDATE.SQLDate) AND UDATE.SQLDate ) >= 1 
    AND COUNT(SAL.DATE BETWEEN DATEADD(day, -7, UDATE.SQLDate) AND UDATE.SQLDate) >=2  
    AND COUNT(SAL.DATE BETWEEN DATEADD(day, -14, UDATE.SQLDate) AND DATEADD(day, -7, UDATE.SQLDate)) >= 2 
THEN 'True' 
ELSE 'False' END AS AVAILABLE

What is the issue??

标签： sqlsql-server

解决方案

I suspect what you are after is:

CASE
            WHEN COUNT(CASE WHEN RSM.RECEIVED_AT BETWEEN DATEADD(DAY, -84, UDATE.SQLDate) AND UDATE.SQLDate THEN 1 END) >= 1
             AND COUNT(CASE WHEN SAL.[DATE] BETWEEN DATEADD(DAY, -7, UDATE.SQLDate) AND UDATE.SQLDate THEN 1 END) >= 2
             AND COUNT(CASE WHEN SAL.[DATE] BETWEEN DATEADD(DAY, -14, UDATE.SQLDate) AND DATEADD(DAY, -7, UDATE.SQLDate) THEN 1 END) >= 2 THEN 'True'
            ELSE 'False'
       END AS AVAILABLE;

As I said in the comments: "You can't have a boolean expression within a COUNT (or any other aggregate function), it requires a scalar expression. "

推荐阅读