Stack Overflow Asked on December 13, 2021
I have two tables [Charges] and [Defects] and want to produce [Desired Query Output] where the output counts the occurrances of defect when [Charges].ChargeDate is between (and including) [Defects].OpenDate and [Defects].CloseDate. For [Defects] table, a close date of NULL means it has not closed yet. Seems simple enough, but I haven’t found a good example of how to do this. Can you help??
I’m using SQL Server version 12.
[Charges]
Order Charge ChargeDate
1 1.2 07/10/2020
1 0.6 07/15/2020
6 0.002 07/20/2020
8 0.13 07/01/2020
8 1.1 06/18/2020
8 0.3 06/19/2020
10 2.3 06/24/2020
[Defects]
Order DefectID OpenDate CloseDate
1 25 06/01/2020 NULL
1 27 07/09/2020 07/12/2020
1 30 05/01/2020 07/20/2020
8 45 06/19/2020 06/19/2020
8 47 06/12/2020 07/05/2020
8 48 06/19/2020 NULL
10 49 06/24/2020 NULL
[Desired Query Output]
Order Charge ChargeDate DefectCnt
1 1.2 07/10/2020 3
1 0.6 07/15/2020 2
6 0.002 07/20/2020 0
8 0.13 07/01/2020 2
8 1.1 06/18/2020 1
8 0.3 06/19/2020 3
10 2.3 06/24/2020 1
You can use a correlated subquery or a lateral join:
select
c.*,
(
select count(*)
from defects d
where
d.order = c.order
and c.ChargeDate >= d.OpenDate
and (d.CloseDate is null or c.ChargeDate <= d.CloseDate)
) as DefectCnt
from charges c
Answered by GMB on December 13, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP