SQL Server Combine 2 SQL statements to get one answer of 'Yes' OR 1 , which ever is easiest

bzzcjhmw 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(101)

I currently have these 2 statements in my query. How would I combine these to get one answer of 'Yes' (like they had one or the other and if they did then give me a 'Yes OR a 1, I need to be able to count all of the Yes's)

AND (ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107')
 OR ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107'))

I only tried what I currently have. I wasn't sure if a CASE statement is needed or not?

@startdate as DATE,
@enddate as DATE;
SET @startdate ='2023-02-01'
SET @enddate = '2023-02-02'
 
 SELECT 
 --CLARITY_LOC.LOC_NAME, 
 --CLARITY_DEP.REV_LOC_ID, 
 --CLARITY_LOC.LOC_ID, 
 --CLARITY_DEP.DEPARTMENT_NAME, 
 --ORDER_METRICS.PAT_LOC_ID, 
 --CLARITY_DEP.DEPARTMENT_ID, 
 --ZC_DEP_SPECIALTY.NAME, 
 --CLARITY_DEP.SPECIALTY_DEP_C, 
 --ZC_DEP_SPECIALTY.DEP_SPECIALTY_C, 
 --ORDER_METRICS.ORDERING_PROV_ID, 
  CLARITY_SER.PROV_NAME, 
  CLARITY_SER.PROV_ID,
 --ZC_NOTE_SER.NAME, 
 --CLARITY_SER.PROVIDER_TYPE_C, 
 --ZC_NOTE_SER.SERVICE_TYPE_C, 
 --ZC_ORDER_TYPE.NAME, 
 --ORDER_METRICS.ORDER_TYPE_C, 
 --ZC_ORDER_TYPE.ORDER_TYPE_C, 
 --ORDER_SIGNED_MED.VERBAL_MODE_C, 
 --ORDER_SIGNED_PROC.VERBAL_MODE_C, 
 ZC_VERB_ORD_MODE_med.NAME as VERBAL_MODE_MED,
 --ZC_VERB_ORD_MODE_med.VERBAL_MODE_C, 
 ZC_VERB_ORD_MODE_proc.NAME as VERBAL_MODE_PROC,
 --ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C, 
 ORDER_METRICS.ORDER_DTTM, 
 --ORDER_SIGNED_MED.LINE, 
 --ORDER_SIGNED_PROC.LINE, 
 --ORDER_SIGNED_PROC.SIGNED_TYPE_C, 
 ORDER_METRICS.ORDER_ID, 
 --ORDER_SIGNED_MED.SIGNED_TYPE_C, 
 ORDER_METRICS.DISPLAY_NAME 
 --ORDER_METRICS.ORDER_DESC
 --ZC_LICENSE_DISPLAY.NAME
 
 FROM ORDER_METRICS ORDER_METRICS 
 LEFT OUTER JOIN ORDER_SIGNED_MED ORDER_SIGNED_MED ON ORDER_METRICS.ORDER_ID=ORDER_SIGNED_MED.ORDER_MED_ID 
 LEFT OUTER JOIN ORDER_SIGNED_PROC ORDER_SIGNED_PROC ON ORDER_METRICS.ORDER_ID=ORDER_SIGNED_PROC.ORDER_PROC_ID
 LEFT OUTER JOIN ZC_ORDER_TYPE ZC_ORDER_TYPE ON ORDER_METRICS.ORDER_TYPE_C=ZC_ORDER_TYPE.ORDER_TYPE_C
 LEFT OUTER JOIN CLARITY_DEP CLARITY_DEP ON ORDER_METRICS.PAT_LOC_ID=CLARITY_DEP.DEPARTMENT_ID
 LEFT OUTER JOIN CLARITY_SER CLARITY_SER ON ORDER_METRICS.ORDERING_PROV_ID=CLARITY_SER.PROV_ID
 LEFT OUTER JOIN ZC_VERB_ORD_MODE ZC_VERB_ORD_MODE_proc ON ORDER_SIGNED_PROC.VERBAL_MODE_C=ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C
 LEFT OUTER JOIN ZC_VERB_ORD_MODE ZC_VERB_ORD_MODE_med ON ORDER_SIGNED_MED.VERBAL_MODE_C=ZC_VERB_ORD_MODE_med.VERBAL_MODE_C 
 LEFT OUTER JOIN CLARITY_LOC CLARITY_LOC ON CLARITY_DEP.REV_LOC_ID=CLARITY_LOC.LOC_ID 
 LEFT OUTER JOIN ZC_DEP_SPECIALTY ZC_DEP_SPECIALTY ON CLARITY_DEP.SPECIALTY_DEP_C=ZC_DEP_SPECIALTY.DEP_SPECIALTY_C
 LEFT OUTER JOIN ZC_NOTE_SER ZC_NOTE_SER ON CLARITY_SER.PROVIDER_TYPE_C=ZC_NOTE_SER.SERVICE_TYPE_C 
 LEFT OUTER JOIN CLARITY_SER_2 CLARITY_SER_2 ON CLARITY_SER.PROV_ID=CLARITY_SER_2.PROV_ID 
 LEFT OUTER JOIN ZC_LICENSE_DISPLAY ZC_LICENSE_DISPLAY ON CLARITY_SER_2.CUR_CRED_C=ZC_LICENSE_DISPLAY.LICENSE_DISPLAY_C
 
 WHERE  ORDER_METRICS.ORDER_DTTM>= @startdate AND ORDER_METRICS.ORDER_DTTM<= @enddate 
 AND ((ORDER_SIGNED_MED.LINE IS  NULL  AND ORDER_SIGNED_PROC.LINE IS  NULL ) 
 OR (ORDER_SIGNED_PROC.LINE IS  NULL  AND ORDER_SIGNED_MED.LINE=1) 
 OR (ORDER_SIGNED_MED.LINE IS  NULL  AND ORDER_SIGNED_PROC.LINE=1))
 AND (ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107')
 OR  ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107'))
 AND  CLARITY_SER.PROV_ID='1862' 
 --AND CLARITY_SER.PROV_ID='1348'
 --AND (CLARITY_SER.PROV_ID='1184' 
 --OR CLARITY_SER.PROV_ID='1348' 
 --OR CLARITY_SER.PROV_ID='1862' 
 --OR CLARITY_SER.PROV_ID='3432' 
 --OR CLARITY_SER.PROV_ID='3823')

 order by CLARITY_SER.PROV_NAME

sql-server

来源：https://stackoverflow.com/questions/75827641/combine-2-sql-statements-to-get-one-answer-of-yes-or-1-which-ever-is-easiest

1条答案

按热度按时间

q43xntqr1#

If you want to remove the filter in the where clause and count the "yes" (or 1) as part of your select statement:

@startdate as DATE,
@enddate as DATE;
SET @startdate ='2023-02-01'
SET @enddate = '2023-02-02'

SELECT
   CASE 
      WHEN
         ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107')
         OR ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107')
      THEN 1
      ELSE 0
   END included_in_sets,
CLARITY_SER.PROV_NAME, 
CLARITY_SER.PROV_ID,
...
FROM ORDER_METRICS ORDER_METRICS 
LEFT OUTER JOIN ...
WHERE ...

If you use 1 and 0 you can also just simply sum() the values of the rows to get the count().

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Combine 2 SQL statements to get one answer of 'Yes' OR 1 , which ever is easiest

1条答案

相关问题

热门标签

最新问答