日期范围之间的 SQL

2022-01-23 00:00:00 sql between subquery sql-server having

我正在尝试检索特定日期范围内的记录计数，该日期范围特定于另一个表中的用户最小日期和最大日期.

I am trying to retrieve the count of records between a certain date range that is specific to the users min date and max date in another table.

这是我目前所拥有的，但它不包括我所知道的至少 13 条记录.你能判断我的逻辑是否有错误吗?

This is what I have so far, however it is excluding at least 13 records that I know of. Can you tell if there is an error in my logic?

提前感谢您的任何意见！

Thanks in advance for any input you have! 

SELECT   rtam.dbo.wfm_process_instance.user_id AS user_id,
         MIN(rtam.dbo.wfm_process_instance.LOCAL_USER_START_TIME) AS Min_Date,
         MAX(rtam.dbo.wfm_process_instance.LOCAL_USER_START_TIME) AS Max_Date,
         0 AS IVR_Calls,
         COUNT(*) AS Total_Calls
FROM     rtam.dbo.WFM_PROCESS_INSTANCE
         LEFT OUTER JOIN
         rtam.dbo.WFM_PROCESS_type
         ON rtam.dbo.wfm_process_instance.PROCESS_TYPE_INDX = rtam.dbo.wfm_process_type.INDX
WHERE    rtam.dbo.wfm_process_type.DISPLAY_NAME = 'DTV Inbound2'
         AND EXISTS (SELECT   rtam.dbo.gnr_Tbl_72_type.CTRL_USER_ID,
                              CONVERT (VARCHAR (10), MIN(rtam.dbo.gnr_tbl_72_type.LOCAL_COL_113), 101) AS min_date,
                              CONVERT (VARCHAR (10), MAX(rtam.dbo.gnr_tbl_72_type.local_col_113), 101) AS max_date
                     FROM     rtam.dbo.GNR_TBL_72_TYPE
                     WHERE    rtam.dbo.GNR_TBL_72_TYPE.CTRL_USER_ID = rtam.dbo.wfm_process_instance.USER_ID
                     GROUP BY rtam.dbo.GNR_TBL_72_TYPE.CTRL_USER_ID
                     HAVING   rtam.dbo.wfm_process_instance.LOCAL_USER_START_TIME BETWEEN CONVERT (VARCHAR (10), MIN(rtam.dbo.gnr_tbl_72_type.LOCAL_COL_113), 101) AND CONVERT (VARCHAR (10), MAX(rtam.dbo.gnr_tbl_72_type.LOCAL_COL_113), 101))
GROUP BY rtam.dbo.wfm_process_instance.USER_ID
ORDER BY rtam.dbo.wfm_process_instance.USER_ID;

推荐答案

试试:

HAVING rtam.dbo.wfm_process_instance.LOCAL_USER_START_TIME 
  >= CONVERT(DATE, MIN(rtam.dbo.gnr_tbl_72_type.LOCAL_COL_113)) 
AND rtam.dbo.wfm_process_instance.LOCAL_USER_START_TIME 
  < DATEADD(DAY, 1, CONVERT(DATE, MAX(rtam.dbo.gnr_tbl_72_type.LOCAL_COL_113))

您还可以考虑使用别名，这样您就不必在整个代码中重复冗长且容易出错的引用，例如 rtam.dbo.wfm_process_instance.

You might also think about using aliases so that you don't have to repeat lengthy and error-prone references like rtam.dbo.wfm_process_instance all over your code.

相关文章