TransWikia.com

How Can I Not Double Count Rows And Objects In My Query?

Stack Overflow Asked by Ziegler199 on November 22, 2021

I am looking to count a number of instances for a group of people that had a request per month however I only want to count this once rather than however many times the person requested something. Here is an example of my data table so this might make more sense

Client    Customer ID   Request ID   RequestStartDate   RequestEndDate
 1         A1              9991            03/04/19         07/03/19
 1         A1              9992            07/05/19         08/05/19
 1         A1              9993            08/09/19         10/01/19

There are around 30k results but I wrote a query to grab the instances per month when a request was asked, this is what I came up with:

Select Distinct
CLient,
       (SELECT COUNT(*) from #Testdata21234 where '2019-07-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.BRequestEndDate), 0) AND CLient = g.Client) as 'July19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-08-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.RequestEndDate), 0) AND CLient = g.Client) as 'August19',
    (SELECT COUNT(*) from #Testdata21234 where '2019-09-01' BETWEEN dateadd(month, datediff(MONTH, 0, #testdata21234.RequestStartDate), 0) and dateadd(month, datediff(MONTH, 0, #testdata21234.RequestEndDate), 0) AND CLient = g.Client) as 'September19'

FROM #testdata21234 g
group by client

My results end up like this if using the above example

July19    August19     September19
  2           2             1

I would like my output to get the following:

July19    August19     September19
  1           1             1

Essentially I want this to count once because although multiple requests, it is under the same Customer ID and I am not counting separate instances, just when the Customer actively pursued a request.

I hope this made sense and please, any help would be great.

Edit:

To further explain, lets try this example

Client   Customer ID   RequestID    RequestStartDate   RequestEndDate
1            A1          9991             03/03/19         07/03/19
1            A1          9992             07/05/19         08/05/19
1            A1          9993             08/09/19         10/01/19
1            A2          9994             07/02/19         07/10/19
1            A2          9995             07/15/19         08/06/19
1            A3          9996             02/01/19         07/01/19
1            A4          9997             08/05/19         08/16/19
1            A4          9998             08/30/19         09/01/19

For the above example, I need the outcome of the following counts by month

July19    August19    Septemeber19
4           3            2

I am counting based on clientID however need to filter based on a requestID. I hope this makes more sense, sorry for any confusion.

3 Answers

I think that hardcoding each month is not good SQL logic, you should use group by and format (see date format). So if, for example, you are trying to count the numbers of clients started each month, you should do something like this:

SELECT 
    FORMAT(g."RequestStartDate",'MMMM yy') month_year,
    COUNT(DISTINCT g."Customer ID") count_clients
FROM #testdata21234 g
GROUP BY 1

Now, there is still logic to add the g."RequestEndDate" and other things you want, but this is the direction I suggest..

Answered by Chananel P on November 22, 2021

You acces the same table multiple times, this can be simplified using conditional aggregation. Additionally you probably want to check if request start/end overlaps with a given month. Regarding your once per customer, this can be done using DISTNCT:

Select client,       
       count(distinct CASE WHEN RequestEndDate >= '2019-07-01'
                       AND RequestStartDate  < '2019-08-01'
                      then "Customer ID" end) AS July19, -- don't know if this matches your logic, once per customer?
       count(distinct CASE WHEN RequestEndDate >= '2019-08-01' -- overlap calculation
                       AND RequestStartDate  < '2019-09-01'
                      then "Customer ID" end) AS Aug19,
       count(distinct CASE WHEN RequestEndDate >= '2019-09-01'
                       AND RequestStartDate  < '2019-10-01'
                      then "Customer ID" end) AS Sep19
from #testdata21234
group by client;

Answered by dnoeth on November 22, 2021

You can use count(distinct) and radically simplify the date comparison logic. I'm pretty sure you want clients that overlap at all with the months, so:

Select client,       
       (select count(*)
        from #Testdata21234 g2
        where g2.RequestStartDate  < '2019-08-01' and
              g2.RequestEndDate >= '2019-07-01'
       ) July19,
       (select count(*)
        from #Testdata21234 g2
        where g2.RequestStartDate  < '2019-09-01' and
              g2.RequestEndDate >= '2019-08-01'
       ) Aug19,
       (select count(*)
        from #Testdata21234 g2
        where g2.RequestStartDate  < '2019-10-01' and
              g2.RequestEndDate >= '2019-09-01'
       ) Sep19
from (select distinct client #testdata21234) g;

Answered by Gordon Linoff on November 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP