SQL在日期范围之间 [英] SQL HAVING BETWEEN a date range
本文介绍了SQL在日期范围之间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试检索特定日期范围内特定于用户在另一个表中的最小日期和最大日期之间的记录数.
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.
这篇关于SQL在日期范围之间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文