时间戳

首页 > 解决方案 > 通过使用两个表中的两个不同日期进行 SQL 组

sql - 通过使用两个表中的两个不同日期进行 SQL 组

问题描述

我有两张桌子正在处理。

select daydate 
from Temp_Dates

results:

**daydate**
2019-01-01 
2019-01-02 
2019-01-03 
2019-01-04 
2019-01-05 
2019-01-06 
2019-01-07 
2019-01-08 
2019-01-09 
2019-01-10 



select Coverage_Effective_Date, Coverage_Expiration_Date, Certificate_Number 
from Elig_dates

results:

*Coverage_Effective_Date     Coverage_Expiration_Date    Certificate_Number*        
2018-12-31                    2019-01-31                   1LEL17-115487         
2019-01-01                    2019-01-31                   1LEL17-115488        
2019-01-01                    2019-01-31                   1LEL17-115494             
2019-01-02                    2019-01-31                   1LEL17-115497         
2019-01-03                    2019-01-31                   1LEL17-115500         
2019-01-05                    2019-01-31                   18LEL18-121770       
2019-01-05                    2019-01-31                   18LELL18-109689       
2019-01-09                    2019-01-31                   ATR16-160410          
2019-01-10                    2019-01-31                   ATR16-160410          
2019-01-10                    2019-01-31                   18LCL18-117965

每次DayDate介于Coverage_Effective_Date和Coverage_Expiration_Date之间时，我想查看Certificate_Number的计数按DayDate分组

到目前为止，这是我的代码，

select DayDate, count(E.certificate_number) as Total
from  Elig_dates E

where dayDate in 
    ( 
select DayDate
from Temp_dates 
where DayDate between E.Coverage_Effective_Date and E.Coverage_Expiration_Date
    ) 

group by  DayDate

我要找的是这个

**daydate**    **Total**
2019-01-01        3
2019-01-02        4
2019-01-03        5
2019-01-04        5
2019-01-05        7
2019-01-06        7 
2019-01-07        7
2019-01-08        7
2019-01-09        8
2019-01-10        10

非常感谢您的帮助！

标签： sqlgroup-bycountsubquery

解决方案

根据您的情况加入表格，然后按日期分组并汇总：

select t.daydate, count(*) Total
from Temp_Dates t left join Elig_dates e
on t.daydate between Coverage_Effective_Date and Coverage_Expiration_Date
group by t.daydate

请参阅演示。
结果：

> daydate                 | Total
> :---------------------- | ----:
> 2019-01-01 00:00:00.000 |     3
> 2019-01-02 00:00:00.000 |     4
> 2019-01-03 00:00:00.000 |     5
> 2019-01-04 00:00:00.000 |     5
> 2019-01-05 00:00:00.000 |     7
> 2019-01-06 00:00:00.000 |     7
> 2019-01-07 00:00:00.000 |     7
> 2019-01-08 00:00:00.000 |     7
> 2019-01-09 00:00:00.000 |     8
> 2019-01-10 00:00:00.000 |    10

推荐阅读